asked on

I am getting the following error

When I run this query I am getting the following error: ORA-00950:

select jobplan.description as jpdesc,
pm.assetnum, pm.frequency, pm.nextdate, pm.location, pm.crewid, pm.frequnit, pm.lead,
pm.description as pm_description,asset.description as asset_description,jobplan.jpduration,
pmsequence.jpnum as pmseq_jpnum, pm.pmnum, pm.siteid,PMSEQUENCE.INTERVAL, PM.PMCOUNTER ,
PM.JPSEQINUSE, pm.STATUS,

CASE WHEN PM.JPSEQINUSE='1' THEN 'YES'
WHEN PM.JPSEQINUSE='0' THEN 'NO'
ELSE ' ' END as "JPSEQ",

CASE WHEN PM.JPSEQINUSE = '1' THEN
(((PMSEQUENCE.INTERVAL*(Abs(1-PM.PMCOUNTER/PMSEQUENCE.INTERVAL ))) * (pm.FREQUENCY *
(CASE
WHEN pm.frequnit = 'DAYS' THEN '1'
WHEN pm.frequnit='WEEKS' THEN '7.0192307697'
ELSE '30.41666667' END)))+pm.NEXTDATE) as "JPnextdate"

ELSE (((pm.FREQUENCY * (CASE WHEN pm.frequnit = 'DAYS' THEN '1'
WHEN pm.frequnit='WEEKS' THEN '7.0192307697'
ELSE '30.41666667'
END))) + pm.NEXTDATE) as "JPnextdate"
END

FROM MAXIMO.PMSEQUENCE, MAXIMO.JOBPLAN, MAXIMO.PM, MAXIMO.ASSET

WHERE JOBPLAN.JPNUM = PMSEQUENCE.JPNUM
AND PM.PMNUM = PMSEQUENCE.PMNUM
AND PM.ASSETNUM = ASSET.ASSETNUM
and pm.siteid = :mrosite
AND PM.ASSETNUM IN ('12700','12300')
order by pm.assetnum, PM_DESCRIPTION, INTERVAL

Sean Stuber

cna you provide "create table" scripts?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

You need to remove the aliases from the inner case statement to the outer case statement.
CASE WHEN PM.JPSEQINUSE = '1' THEN
(((PMSEQUENCE.INTERVAL*(Abs(1-PM.PMCOUNTER/PMSEQUENCE.INTERVAL ))) * (pm.FREQUENCY *
(CASE
WHEN pm.frequnit = 'DAYS' THEN '1'
WHEN pm.frequnit='WEEKS' THEN '7.0192307697'
ELSE '30.41666667' END)))+pm.NEXTDATE)

ELSE (((pm.FREQUENCY * (CASE WHEN pm.frequnit = 'DAYS' THEN '1'
WHEN pm.frequnit='WEEKS' THEN '7.0192307697'
ELSE '30.41666667'
END))) + pm.NEXTDATE)
END as "JPnextdate"

awking00

Sorry, I didn't see sdstuber's post.

ewgf2002

ASKER

Thank you sdstuber. I figured it out after I received your response. Thanks again.