WITH clause not working.

pts.
Tags:
SQL
This is the first time I'm using the WITH clause, and am getting "missing expression". I am rewriting a MUCH larger query, so much of the code below that seems extraneous is needed for the pieces that aren't in there yet. Can anyone tell me what needs to be changed to make this work? WITH test AS ( select ps_job.emplid as testempl, case when PS_JOB.PAYGROUP = 'ABC' then (case when (ROUND((PS_AL_YTD_CNTL_HRS.AL_PROCESS_EFFDT - PS_EMPLOYMENT.SERVICE_DT)/365,2)) < 5 then 1.44 when (ROUND((PS_AL_YTD_CNTL_HRS.AL_PROCESS_EFFDT - PS_EMPLOYMENT.SERVICE_DT)/365,2)) between 5 and 9.99 then 2.16 when (ROUND((PS_AL_YTD_CNTL_HRS.AL_PROCESS_EFFDT - PS_EMPLOYMENT.SERVICE_DT)/365,2)) between 10 and 24.99 then 2.88 when (ROUND((PS_AL_YTD_CNTL_HRS.AL_PROCESS_EFFDT - PS_EMPLOYMENT.SERVICE_DT)/365,2)) >= 25 then 3.61 end) when ps_Employment.Flag_3 = 'G' then 4.62 when ps_Employment.Flag_3 = 'H' then (case when (ROUND((PS_AL_YTD_CNTL_HRS.AL_PROCESS_EFFDT- PS_EMPLOYMENT.SERVICE_DT)/365,2)) < 10 then 3.08 when (ROUND((PS_AL_YTD_CNTL_HRS.AL_PROCESS_EFFDT- PS_EMPLOYMENT.SERVICE_DT)/365,2)) >= 10 then 4.62 end) when PS_JOB.PAYGROUP = 'XYZ' and substr(ps_Employment.Flag_3, 1, 1) = ' ' then ( case when (ROUND((PS_AL_YTD_CNTL_HRS.AL_PROCESS_EFFDT - PS_EMPLOYMENT.SERVICE_DT)/365,2)) < 1 then .31 when (ROUND((PS_AL_YTD_CNTL_HRS.AL_PROCESS_EFFDT - PS_EMPLOYMENT.SERVICE_DT)/365,2)) between 1 and 2.99 then 2.77 when (ROUND((PS_AL_YTD_CNTL_HRS.AL_PROCESS_EFFDT - PS_EMPLOYMENT.SERVICE_DT)/365,2)) between 3 and 9.99 then 3.08 when (ROUND((PS_AL_YTD_CNTL_HRS.AL_PROCESS_EFFDT - PS_EMPLOYMENT.SERVICE_DT)/365,2)) >= 10 then 4.62 end) end as AccRt, from PS_EMPLOYMENT, PS_AL_YTD_CNTL_HRS, PS_JOB Where ((JOB.EFFSEQ= ( SELECT MAX(INNERALIAS.EFFSEQ) FROM PS_JOB INNERALIAS WHERE INNERALIAS.EMPLID = JOB.EMPLID AND INNERALIAS.EFFDT = JOB.EFFDT)) AND (JOB.EFFDT = ( SELECT MAX(INNERALIAS.EFFDT) FROM PS_JOB INNERALIAS WHERE INNERALIAS.EMPLID = JOB.EMPLID AND INNERALIAS.EFFDT <= SYSDATE))) AND ((JOB.EMPLID = EMP.EMPLID ) AND (JOB.FILE_NBR = CNTL_HRS.FILE_NBR(+) ) AND (JOB.PAYGROUP = CNTL_HRS.PAYGROUP(+) ) group by ps_job.emplid ) SELECT SUBSTR(JOB.HOME_DEPARTMENT, 1, 3) || SUBSTR(JOB.HOME_DEPARTMENT, 5, 2) as "Dept", JOB.FILE_NBR as "FileNo", JOB.PAYGROUP as "PayGrp", JOB.AL_EMPL_STATUS as "Status", PER.NAME as "Name", PER.STATE as "State", CNTL_HRS.AL_PROCESS_EFFDT as ProcessDt, EMP.SERVICE_DT as Svc_Dt, round((CNTL_HRS.AL_PROCESS_EFFDT- EMP.SERVICE_DT)/365,0) as YOS, to_number(to_char(EMP.SERVICE_DT, 'MM')) as Svc_Mo, UsrDt.USER2_DATE as Roll_Dt, Emp.Flag_3, AccRt FROM ps_job job, ps_personal_data per, ps_employment emp, ps_al_ytd_cntl_hrs cntl_hrs, ps_al_ytd_accum accum, ps_hruser_data UsrDt, ps_personal_data supvEM, test WHERE ((JOB.EFFSEQ= ( SELECT MAX(INNERALIAS.EFFSEQ) FROM PS_JOB INNERALIAS WHERE INNERALIAS.EMPLID = JOB.EMPLID AND INNERALIAS.EFFDT = JOB.EFFDT)) AND (JOB.EFFDT = ( SELECT MAX(INNERALIAS.EFFDT) FROM PS_JOB INNERALIAS WHERE INNERALIAS.EMPLID = JOB.EMPLID AND INNERALIAS.EFFDT <= SYSDATE))) AND ((CNTL_HRS.CNTL_HRS_NBR IN( 1, 2, 3, 4)) AND (JOB.AL_EMPL_STATUS IN( 'A', 'L')) AND (JOB.PAYGROUP IN( 'XYZ', 'ABC')) AND (JOB.FILE_NBR > '000099')) AND ( (JOB.EMPLID = (select testempl from test)) AND (JOB.EMPLID = EMP.EMPLID ) AND (JOB.EMPLID = PER.EMPLID ) AND (JOB.EMPLID = UsrDt.EMPLID(+) ) AND (JOB.FILE_NBR = CNTL_HRS.FILE_NBR(+) ) AND (JOB.PAYGROUP = CNTL_HRS.PAYGROUP(+) ) AND (JOB.FILE_NBR = ACCUM.FILE_NBR(+) ) AND (JOB.PAYGROUP = ACCUM.PAYGROUP(+) ) AND (EMP.SUPERVISOR_ID = SupvEM.EMPLID(+) ) ) ORDER BY SUBSTR(job.HOME_DEPARTMENT, 1, 3) || SUBSTR(job.HOME_DEPARTMENT, 5, 2) , per.NAME

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi, I would start by carefully formatting this statement: it is very hard to follow and one bracket in the wrong place could cause the error.
Also the line “(JOB.EMPLID = (select testempl from test)) AND” will cause an ORA-01427 use “job.emplid=test.testempl AND”
Also make sure the “test” table select statement works on it’s own before using it within the “with test as (” clause.
Regard, Peter Wilkinson.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Source
    Thanks for your input - I admit I hadn't tested the subquery as a stand-alone yet, at the time I posted the question. It's working better now than it was before. However, I'm now getting ORA-01427 Single-row subquery returns more than one row. Am I missing something more here? I thought the WITH clause could return more than one row (in my case, I am looking for one result per person) - I haven't seen reference to it being a single-row subquery in the documentation I have. If the WITH clause is really limited to only one row, any idea of another approach so I don't have to repeat this code a gazillion times in this query? I have several pieces of code that are re-used multiple times in the original query, which is what is making it unwieldy, and I was hoping to reduce each of them to individual functions.
    0 pointsBadges:
    report
  • Source
    This is what happens when you're trying to do too many things at once - I found my error. Thanks for your input!
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following