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