0

I'm getting the following message and I can't find what's wrong. I'm needing help reviewing my code.

ERROR MSG:

OLE DB provider "OraOLEDB.Oracle" for linked server "S" returned message "ORA-00936: missing expression". Msg 7321, Level 16, State 2, Line 1

SELECT *
FROM OPENQUERY(S, 

'select 
d.Pay_Period_ID,
d.Start_Date,
d.End-Date,
l.LABORLEV2NM "Wrk Labor Acct",
l.LABORLEV2dsc "Wrk Cost Center",
l.LABORLEV2NM "Home Acct",
a.personnum ID,
A.LASTNM,
CASE WHEN INSTR(A.FIRSTNM,'''') > 0 THEN TRIM(SUBSTR(A.FIRSTNM,1,INSTR(A.FIRSTNM,''''))) ELSE A.FIRSTNM END firstnm, 
a.PERSONFULLNAME "EmployeeName", 
l.LABORLEV3NM "Job Desc",
bu.EXTERNAL_KEY "BU",
k.fte "FTE",
''Productive'' "Type", 
p.name "PayCode",
to_char(last_day(add_months( w.adjAPPLYDTM,-1))+1, ''MM/DD/YYYY'') "Month",
sum(w.DURATIONSECSQTY/60/60) "Hours",
d.hourly_rate "Fixed Hrly",  
'''' "Dollars",
CASE WHEN SUBSTR(l.LABORLEV2NM,1,3) = ''350'' THEN ''HMC'' 
WHEN SUBSTR(l.LABORLEV2NM,1,3) = ''351'' THEN ''HHH''
WHEN SUBSTR(l.LABORLEV2NM,1,3) = ''352'' THEN ''KAU''
END AS "Facility - Worked",
SUBSTR(l.LABORLEV2NM,4,4) "Worked Dept GL",
'''' "EMT",
CONCAT(''FY'',to_char(add_months(w.adjAPPLYDTM, 8 ), ''YY'' )) "FY"
--to_char(add_months(w.adjAPPLYDTM, 8 ), ''YY'' ) fiscal_year


from a, w, p,  l,  c, bu,  k,  d, b

where
w.paycodeid = p.paycodeid
and w.laboracctid = l.laboracctid
and c.Job_ID = b.Job_ID
and c.position_number = b.postion_number
and c.WFC_EMPLOYEE_ID = w.Employeeid
and b.payroll_id = d.payroll_id
and b.pay
and c.bu_id = bu.bu_id
and c.job_id = k.job_id
and d.llid = l.laboracctid
and a.personnum not like ''999%''
and a.personnum not like ''987%''
and w.employeeid = a.personid
and a.homelaborlevelnm1 in (''350'', ''351'', ''352'') 
and w.ADJAPPLYDTM >= TO_DATE(''08/01/2017'', ''MM/DD/YYYY'') 
and w.ADJAPPLYDTM < TO_DATE(''09/01/2017'', ''MM/DD/YYYY'') 
and p.name like ''CT%Earned%''
') AS CT_Table
cmpmd2
  • 165
  • 1
  • 2
  • 16
  • I do see 2 commas in your from clause near 'c' – sniperd Oct 10 '17 at 19:18
  • Thanks. I added the 'bu' table – cmpmd2 Oct 10 '17 at 19:24
  • Try making a _really_ simple SELECT * FROM table statement and expand until you get the error. I see your linked server 'S' at the top, this would be a good way to make sure your linked server is actually setup correctly and the problem isn't in the query itself. – sniperd Oct 10 '17 at 19:27
  • The openquery statment works. However when I added, the d.hourly_rate field, with and b.payroll_id = d.payroll_id it doens't work. – cmpmd2 Oct 10 '17 at 22:43
  • sounds like b.payroll_id = d.payroll_id those columns are missing from table b and table d – sniperd Oct 11 '17 at 17:44
  • @sniperd would you mind expaining missing columns is? Do you mean in my select statement? – cmpmd2 Oct 11 '17 at 18:12

1 Answers1

0

It works now. Needed the double quotes around the name of the column "Month" vs. 'Month'.

cmpmd2
  • 165
  • 1
  • 2
  • 16