August 26, 2008 at 11:49 am
Here is the Oracle query that works fine in Oracle but when I put it in SSIS SQL Server import and export wizard, it gave me an error
ORA-00904:invalid identifier(Microsoft OLE DB Provider for Oracle). I could not figure out what was wrong with the query.
I connected to Oracle 9i using Microsoft OLE DB Provider for Oracle.
Thanks
SELECT
to_char(sysdate, 'YYYYMMDD') TransactionDate
,0 TransactionTime
,eid.person_code BadgeNum
,to_char(sysdate, 'YYYYMMDD') DueDate
,to_char(eid.birth_date, 'YYYYMMDD') BirthDate
,NULL Comments
,NULL DriverLicenceCode
,NULL DriverLicenseExp
,eid.DRIVERS_LICENSE DriverLicenseNumber
,NULL EmergencyContact
,NULL EMergencyContactRel
,NULL EmergencyPhone
,eid.person_code EmployeeCode
,NULL EmployeePositionAbbr
,decode(dps.Position_Code,'RTS-BUS-OPER','OPER','RTS-BUS-OPER-PT','OPER-PT','RTS-BO-TRAINEE','TRAINEE') EmployeeTypeAbbr
,eid.First_Name FirstName
,replace(eid.Last_Name,',','') LastName
,DECODE(eid.gender, '01','M','02','F',' ') Gender
,TO_CHAR(eem.hire_date,'YYYYMMDD') HireDate
,NULL MaritalStatus
,nvl(eid.Middle_Name,' ') MiddleName
,NULL NickName
,NULL Seniority
,nvl(TO_CHAR(eem.seniority_date,'YYYYMMDD'),'0') SeniorityDate
,' ' SSN
,NULL StartDate
,decode(p2k_smglx('X_STATUS_TYPE',des.Status_Type),'Terminated','Term',p2k_smglx('X_STATUS_TYPE',des.Status_Type)) EmployeeStatusAbbr
,NULL StepProgressionDate
,nvl(TO_CHAR(eem.Termination_Date,'YYYYMMDD'),'0') TerminationDate
,NULL Title
,NULL VacationGroupAbbr
,NULL VacationSeniority
,' ' VacationSeniorityDate
,'1' DivisionAbbr
,easd.Wage_Rate PayRate
,NULL Violations
,replace(eps.Address_Line_1,',','') UserString1
,replace(eps.Address_Line_2,',','') UserString2
,eps.Locality UserString3
,'NY' UserString4
,nvl(eps.Zip_Postal, ' ') UserString5
,NULL UserString6
,nvl(eps.alt_phone_number,' ') UserString7
,NULL UserString8
,NULL UserString9
,NULL UserString10
,NULL UserString11
,NULL UserString12
,NULL UserString13
,NULL UserString14
,NULL UserString15
,NULL UserString16
,NULL UserString17
,NULL UserString18
,NULL UserString19
,NULL UserString20
,nvl(to_char(to_date(p2k_smgetudf('P2K_HR_EMPLOYMENTS','FT_DATE',eem.id)),'YYYYMMDD'),'0') UserLong1
,nvl(to_char(to_date(p2k_smgetudf('P2K_HR_EMPLOYMENTS','PT_DATE',eem.id)),'YYYYMMDD'),'0') UserLong2
,nvl(to_char(to_date(p2k_smgetudf('P2K_HR_PERSONALS','NEXT DIABETIC CTL DT',eps.id)),'YYYYMMDD'),'0') UserLong3
,nvl(to_char(to_date(p2k_smgetudf('P2K_HR_PERSONALS','NEXT ANNUAL OBS DT',eps.id)),'YYYYMMDD'),'0') UserLong4
,nvl(to_char(to_date(p2k_smgetudf('P2K_HR_PERSONALS','NEXT PHYSICAL DT',eps.id)),'YYYYMMDD'),'0') UserLong5
,nvl(to_char(to_date(p2k_smgetudf('P2K_HR_PERSONALS','NEXT WRITTEN DT',eps.id)),'YYYYMMDD'),'0') UserLong6
,nvl(to_char(to_date(p2k_smgetudf('P2K_HR_PERSONALS','NEXT ROAD TEST DT',eps.id)),'YYYYMMDD'),'0') UserLong7
,'0' UserShort1
,'0' UserShort2
,NULL VTTDate
,nvl(to_char(to_date(p2k_smgetudf('P2K_HR_PERSONALS','NEXT ANNUAL REVIEW DT',eps.id)),'YYYYMMDD'),'0') Medical
,NULL awardEligibilityDate
,NULL SafeDrivingStartDate
,NULL LastAwardDate
,NULL numberofawards
,NULL RedCircleJobClass
,decode(dps.Position_Code,'RTS-BUS-OPER','OPER','RTS-BUS-OPER-PT','OPER-PT') JobClassAbbr
,'NY' DriverLicenseState
FROM p2k_hr_identities eid
INNER JOIN p2k_hr_personals eps on eid.id = eps.eid_id and sysdate between eps.effective and eps.expiry
INNER JOIN p2k_hr_employments eem on eid.id = eem.eid_id
INNER JOIN p2k_cm_entities den on den.id = eem.den_id
INNER JOIN p2k_hr_assignments eas on eem.id = eas.eem_id
INNER JOIN p2k_hr_assignment_details easd on eas.id = easd.eas_id and sysdate between easd.effective and easd.expiry
INNER JOIN p2k_cm_positions dps on easd.dps_id = dps.id
INNER JOIN p2k_cm_employment_statuses des on easd.des_id = des.id
August 26, 2008 at 12:48 pm
I don't know what is wrong, but maybe you could narrow it down by commenting out groups of columns until you get a set that works. My guess would be that it is trying to pre-parse some of the functions and getting hung up on ones it does not recognize. If that's the case, then maybe swapping NVL and ISNULL, TO_CHAR and CONVERT and DECODE and CASE would fix it.
Good luck,
Chad
August 26, 2008 at 12:59 pm
I am agreeing with the other post but back when I worked with Oracle I recall the "nvl" not working through ODBC or OLEDB. I think what I did was build staging tables on Oracle so I could use all of the Oracle features and a simple select to SQL to prevent errors when I forgot which SQL to use.
I also believe with 10 and 11 you can execute an Oracle stored procedure to return a table result set which would allow you to not have to deal with not fully supported SQL commands.
August 26, 2008 at 1:12 pm
Or maybe a view on the Oracle end?
Chad
August 28, 2008 at 11:46 am
You guys are right. Inside the SQL, it used views and functions that I did not know. I did not write the SQL myself.
Now I fixed the SQL but the data transformation still gives me a big problem. It said DT_I4 and DT_I2 is not supported.
I hated SSIS.:angry:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy