WITH Statement Problem

General
Highlighted
Enthusiast

WITH Statement Problem

Hello,

The following SQL works fine in Oracle, but in Teradata it causes a 3807 error (WITH table does not exist) when referenced later in the query. Does anybody know how to modify SQL to run in Teradata? Thanks.

 

WITH PREVIOUS_MONTH_DATA AS
(SELECT
IC.CASEID IC_CASEID
, IC.CASEREFERENCE IC_CASEREFERENCE
, AC.APPLICATIONCASEID
, IC.STATUSCODE IC_STATUSCODE
, AC.APPLICATIONDATE
, AC.METHODOFAPPLICATIONcode
,(SELECT
MAX(IA.ENTEREDBY)
FROM
MNsureViews.INTAKEAPPLICATIONv IA
WHERE
IA.REFERENCEid = AC.APPLICATIONREFERENCE
) ENTEREDBY
, OOL.ORGOBJECTcode
, OOL.ORGOBJECTREFERENCE
, OOL.USERNAMEcode
, PDC.RECEIVEDDATE
FROM
MNsureViews.APPLICATIONCASEv AC
INNER JOIN MNsureViews.CASERELATIONSHIPv CR ON CR.CASEID = AC.APPLICATIONCASEID AND CR.TYPECODE = 'CT19'
INNER JOIN MNsureViews.CASEHEADERv IC ON IC.CASEID = CR.RELATEDCASEID AND IC.CASETYPECODE = 'CT5'
INNER JOIN MNsureViews.ORGOBJECTLINKv OOL ON OOL.ORGOBJECTLINKID = IC.OWNERORGOBJECTLINKID
INNER JOIN MNsureViews.CASEHEADERv PDC ON PDC.INTEGRATEDCASEID=IC.CASEID AND PDC.STATUSCODE<>'CS3'
AND TO_CHAR((SELECT
MIN(CCD.DETERMINATIONDATETIME)
FROM
MNsureViews.CREOLECASEDETERMINATIONv CCD
WHERE
CCD.CASEID = PDC.CASEID
AND CCD.ASSESSMENTSTATUS IS NOT NULL
), 'MMYYYY') = TO_CHAR(ADD_MONTHS(current_date, - 1), 'MMYYYY') AND
AC.APPLICATIONDATE <= IC.RECEIVEDDATE
AND NOT EXISTS
(SELECT
1
FROM
MNsureViews.CASEHEADERv PDC
WHERE
PDC.INTEGRATEDCASEID = IC.CASEID
AND PDC.CASETYPECODE = 'CT2'
AND (PDC.STATUSCODE = 'CS3'
OR PDC.STATUSCODE <> 'CS1')
)
)


Accepted Solutions
Teradata Employee

Re: WITH Statement Problem

I am guessing that you have multiple common table expressions preceding the main query, and that a later CTE references this one.

Teradata's order of parsing WITH clauses is backwards, so you need to reverse the order of the CTEs.

 

In other words, for other databases (including Oracle), if B references A then A is listed first:

WITH A AS (some query), B AS (SELECT ... FROM A...) ...

But for Teradata, the order needs to be:

WITH B AS (SELECT ... FROM A...), A AS (some query)...

1 ACCEPTED SOLUTION
8 REPLIES
Teradata Employee

Re: WITH Statement Problem

I am guessing that you have multiple common table expressions preceding the main query, and that a later CTE references this one.

Teradata's order of parsing WITH clauses is backwards, so you need to reverse the order of the CTEs.

 

In other words, for other databases (including Oracle), if B references A then A is listed first:

WITH A AS (some query), B AS (SELECT ... FROM A...) ...

But for Teradata, the order needs to be:

WITH B AS (SELECT ... FROM A...), A AS (some query)...

Senior Apprentice

Re: WITH Statement Problem

And this creative interpretation of standards is finally fixed in TD16.

Then you can mix both, wrong and correct order :-)

Enthusiast

Re: WITH Statement Problem

Thanks for the tip. I've tried moving the WITH clause to different areas, but still no luck. Below is the entire query. The main WITH clause is in lines 1-46. Where to you think it should be moved for this query to run successfully? Thanks again.

 

WITH PREVIOUS_MONTH_DATA AS
(SELECT
IC.CASEID IC_CASEID
, IC.CASEREFERENCE IC_CASEREFERENCE
, AC.APPLICATIONCASEID
, IC.STATUSCODE IC_STATUSCODE
, AC.APPLICATIONDATE
, AC.METHODOFAPPLICATIONcode
,(SELECT
MAX(IA.ENTEREDBY)
FROM
MNsureViews.INTAKEAPPLICATIONv IA
WHERE
IA.REFERENCEid = AC.APPLICATIONREFERENCE
) ENTEREDBY
, OOL.ORGOBJECTcode
, OOL.ORGOBJECTREFERENCE
, OOL.USERNAMEcode
, PDC.RECEIVEDDATE
FROM
MNsureViews.APPLICATIONCASEv AC
INNER JOIN MNsureViews.CASERELATIONSHIPv CR ON CR.CASEID = AC.APPLICATIONCASEID AND CR.TYPECODE = 'CT19'
INNER JOIN MNsureViews.CASEHEADERv IC ON IC.CASEID = CR.RELATEDCASEID AND IC.CASETYPECODE = 'CT5'
INNER JOIN MNsureViews.ORGOBJECTLINKv OOL ON OOL.ORGOBJECTLINKID = IC.OWNERORGOBJECTLINKID
INNER JOIN MNsureViews.CASEHEADERv PDC ON PDC.INTEGRATEDCASEID=IC.CASEID AND PDC.STATUSCODE<>'CS3'
AND TO_CHAR((SELECT
MIN(CCD.DETERMINATIONDATETIME)
FROM
MNsureViews.CREOLECASEDETERMINATIONv CCD
WHERE
CCD.CASEID = PDC.CASEID
AND CCD.ASSESSMENTSTATUS IS NOT NULL
), 'MMYYYY') = TO_CHAR(ADD_MONTHS(current_date, - 1), 'MMYYYY') AND
AC.APPLICATIONDATE <= IC.RECEIVEDDATE
AND NOT EXISTS
(SELECT
1
FROM
MNsureViews.CASEHEADERv PDC
WHERE
PDC.INTEGRATEDCASEID = IC.CASEID
AND PDC.CASETYPECODE = 'CT2'
AND (PDC.STATUSCODE = 'CS3'
OR PDC.STATUSCODE <> 'CS1')
)
)
, CHECK_IC_RENEWAL_DATES AS
(SELECT
TO_CHAR(MIN(REN_DATES.RENEWAL_DATES), 'MM/DD/YYYY') EARLIEST_RENEWAL_DATE
, REN_DATES.CASEID IC_CASEID
FROM
(SELECT
(SELECT
MIN(PCCH.STATUSDATETIME)
FROM
MNsureViews.PDMRUNCASECONTROLSTATEHISTORYv PCCH
WHERE
PCCH.PDMRUNCASECONTROLID = PCC.PDMRUNCASECONTROLID
AND PCCH.STATUS = 'RCCS26000'
) RENEWAL_DATES
, PCC.CASEID
FROM
MNsureViews.PDMRUNCASECONTROLv PCC
WHERE
PCC.CASEID IN
(SELECT
IC_CASEID
FROM
PREVIOUS_MONTH_DATA
)
) REN_DATES
GROUP BY
REN_DATES.CASEID
)
, IC_PDC_INFORMATION AS
(SELECT
PMD.IC_CASEREFERENCE
, PDC.CASEREFERENCE PDC_CASEREFERENCE
,(SELECT
TO_CHAR(MIN(CStat.STARTDATETIME), 'MM/DD/YYYY HH24:MI:SS')
FROM
MNsureViews.CASESTATUSv CStat --CS is reserved word
WHERE
CStat.CASEID = PDC.CASEID
AND STATUSCODE = 'CS5'
) CASECREATIONDATE
,(SELECT
MIN(CCD.DETERMINATIONDATETIME)
FROM
MNsureViews.CREOLECASEDETERMINATIONv CCD
WHERE
CCD.CASEID = PDC.CASEID
AND CCD.ASSESSMENTSTATUS IS NOT NULL
) PDCACTIVATIONDATE
,(SELECT
MAX(FULLNAME)
FROM
MNsureViews.USERSv US
WHERE
US.STATUSCODE = 'RST1'
AND US.USERNAME =
(SELECT
MAX(CREATEDBYUSER)
FROM
MNsureViews.CREOLECASEDETERMINATIONv CCD
WHERE
CCD.CASEID = PDC.CASEID
AND CCD.ASSESSMENTSTATUS IS NOT NULL
AND NOT EXISTS
(SELECT
1
FROM
MNsureViews.CREOLECASEDETERMINATIONv IN_CCD
WHERE
IN_CCD.CASEID = CCD.CASEID
AND IN_CCD.ASSESSMENTSTATUS IS NOT NULL
AND IN_CCD.DETERMINATIONDATETIME < CCD.DETERMINATIONDATETIME
)
)
) PDCACTIVATOR
, TO_CHAR(PMD.APPLICATIONDATE, 'MM/DD/YYYY') APPLICATIONDATE
FROM
PREVIOUS_MONTH_DATA PMD
INNER JOIN MNsureViews.CASEHEADERv PDC ON PDC.INTEGRATEDCASEID = PMD.IC_CASEID AND PDC.CASETYPECODE = 'CT2'
AND NOT EXISTS
(SELECT
1
FROM
MNsureViews.CASEHEADERv IN_PDC
WHERE
IN_PDC.INTEGRATEDCASEID = PDC.INTEGRATEDCASEID
AND IN_PDC.CASETYPECODE = PDC.CASETYPECODE
AND IN_PDC.STATUSCODE = PDC.STATUSCODE
AND IN_PDC.RECEIVEDDATE < PDC.RECEIVEDDATE
)
)
SELECT DISTINCT
PMD.IC_CASEREFERENCE "Integrated Case Number"
, IPI.PDC_CASEREFERENCE "PDC Reference Number"
, IPI.CASECREATIONDATE "Case Creation Date"
, TO_CHAR(IPI.PDCACTIVATIONDATE,'MM/DD/YYYY HH24:MI:SS') "PDC Activation Date"
, IPI.PDCACTIVATOR "PDC Activator"
,(SELECT
MAX(DESCRIPTION)
FROM
MNsureViews.CODETABLEITEMv
WHERE
CODE = PMD.METHODOFAPPLICATION
AND TABLENAME = 'MethodOfApplication'
) "Application Type"
, CASE
WHEN PMD.METHODOFAPPLICATION = 'MOA27001'
THEN
(SELECT
MAX(US.FULLNAME)
FROM
MNsureViews.USERSv US
WHERE
US.USERNAME = PMD.ENTEREDBY
)
ELSE 'SYSTEM'
END "Application Entered By"
,(SELECT
MAX(CTI.DESCRIPTION)
FROM
MNsureViews.CODETABLEITEMv CTI
, MNsureViews.EVIDENCEDESCRIPTORv ED
, MNsureViews.DYNAMICEVIDENCEDATAATTRIBUTEv DE
WHERE
ED.EVIDENCETYPE = 'DET0001029'
AND ED.STATUSCODE = 'EDS1'
AND DE.EVIDENCEID = ED.RELATEDID
AND DE.NAME LIKE 'serviceAgencyName'
AND CTI.CODE = DE.attributeVALUE
AND CTI.TABLENAME = 'MNXServiceAgenciesMA'
AND NOT EXISTS
(SELECT
1
FROM
MNsureViews.EVIDENCEDESCRIPTORv
WHERE
EVIDENCEDESCRIPTOR.CASEID = ED.CASEID
AND EVIDENCEDESCRIPTOR.EVIDENCETYPE = ED.EVIDENCETYPE
AND EVIDENCEDESCRIPTOR.STATUSCODE = ED.STATUSCODE
AND EVIDENCEDESCRIPTOR.PARTICIPANTID = ED.PARTICIPANTID
AND EVIDENCEDESCRIPTOR.LASTWRITTEN > ED.LASTWRITTEN
)
AND ED.CASEID = PMD.IC_CASEID
) "Service Agency"
, CASE PMD.ORGOBJECTcode
WHEN 'RL9'
THEN
(SELECT
MAX(USERSv.FULLNAME)
FROM
MNsureViews.USERSv USERS
WHERE
USERSv.USERNAME = PMD.USERNAME
AND USERS.STATUSCODE = 'RST1'
)
WHEN 'RL10'
THEN
(SELECT
MAX(OU.NAME)
FROM
MNsureViews.ORGANIzATIONUNITv OU
WHERE
OU.ORGANIzATIONUNITID = PMD.ORGOBJECTREFERENCE
AND OU.STATUSCODE = 'OUSC1'
)
WHEN 'RL21'
THEN
(SELECT
MAX(POS.NAME)
FROM
MNsureViews.jobPOSITIONv POS
WHERE
POS.POSITIONID = PMD.ORGOBJECTREFERENCE
AND POS.RECORDSTATUS = 'RST1'
)
WHEN 'RL23'
THEN
(SELECT
MAX(WQ.NAME)
FROM
MNsureViews.WORKQUEUEv WQ
WHERE
WQ.WORKQUEUEID = PMD.ORGOBJECTREFERENCE
)
ELSE 'unknown'
END AS "Case Owner"
FROM
PREVIOUS_MONTH_DATA PMD
INNER JOIN IC_PDC_INFORMATION IPI ON IPI.IC_CASEREFERENCE = PMD.IC_CASEREFERENCE
WHERE TO_CHAR(IPI.PDCACTIVATIONDATE, 'MMYYYY') = TO_CHAR(ADD_MONTHS(current_date, - 1), 'MMYYYY')
AND NOT EXISTS (SELECT 1 FROM IC_PDC_INFORMATION IN_IPI WHERE IN_IPI.IC_CASEREFERENCE=IPI.IC_CASEREFERENCE AND IN_IPI.PDCACTIVATIONDATE>IPI.PDCACTIVATIONDATE)
ORDER BY
4

Enthusiast

Re: WITH Statement Problem

Thank you for the tip.  I have tried moving the WITH clause around but have still not had success. Below is the entire query.

The WITH clause in question is in lines 1 - 46. Where do you think it should be moved for the query to run successfully? Thanks again.

 

WITH PREVIOUS_MONTH_DATA AS
(SELECT
IC.CASEID IC_CASEID
, IC.CASEREFERENCE IC_CASEREFERENCE
, AC.APPLICATIONCASEID
, IC.STATUSCODE IC_STATUSCODE
, AC.APPLICATIONDATE
, AC.METHODOFAPPLICATIONcode
,(SELECT
MAX(IA.ENTEREDBY)
FROM
MNsureViews.INTAKEAPPLICATIONv IA
WHERE
IA.REFERENCEid = AC.APPLICATIONREFERENCE
) ENTEREDBY
, OOL.ORGOBJECTcode
, OOL.ORGOBJECTREFERENCE
, OOL.USERNAMEcode
, PDC.RECEIVEDDATE
FROM
MNsureViews.APPLICATIONCASEv AC
INNER JOIN MNsureViews.CASERELATIONSHIPv CR ON CR.CASEID = AC.APPLICATIONCASEID AND CR.TYPECODE = 'CT19'
INNER JOIN MNsureViews.CASEHEADERv IC ON IC.CASEID = CR.RELATEDCASEID AND IC.CASETYPECODE = 'CT5'
INNER JOIN MNsureViews.ORGOBJECTLINKv OOL ON OOL.ORGOBJECTLINKID = IC.OWNERORGOBJECTLINKID
INNER JOIN MNsureViews.CASEHEADERv PDC ON PDC.INTEGRATEDCASEID=IC.CASEID AND PDC.STATUSCODE<>'CS3'
AND TO_CHAR((SELECT
MIN(CCD.DETERMINATIONDATETIME)
FROM
MNsureViews.CREOLECASEDETERMINATIONv CCD
WHERE
CCD.CASEID = PDC.CASEID
AND CCD.ASSESSMENTSTATUS IS NOT NULL
), 'MMYYYY') = TO_CHAR(ADD_MONTHS(current_date, - 1), 'MMYYYY') AND
AC.APPLICATIONDATE <= IC.RECEIVEDDATE
AND NOT EXISTS
(SELECT
1
FROM
MNsureViews.CASEHEADERv PDC
WHERE
PDC.INTEGRATEDCASEID = IC.CASEID
AND PDC.CASETYPECODE = 'CT2'
AND (PDC.STATUSCODE = 'CS3'
OR PDC.STATUSCODE <> 'CS1')
)
)
, CHECK_IC_RENEWAL_DATES AS
(SELECT
TO_CHAR(MIN(REN_DATES.RENEWAL_DATES), 'MM/DD/YYYY') EARLIEST_RENEWAL_DATE
, REN_DATES.CASEID IC_CASEID
FROM
(SELECT
(SELECT
MIN(PCCH.STATUSDATETIME)
FROM
MNsureViews.PDMRUNCASECONTROLSTATEHISTORYv PCCH
WHERE
PCCH.PDMRUNCASECONTROLID = PCC.PDMRUNCASECONTROLID
AND PCCH.STATUS = 'RCCS26000'
) RENEWAL_DATES
, PCC.CASEID
FROM
MNsureViews.PDMRUNCASECONTROLv PCC
WHERE
PCC.CASEID IN
(SELECT
IC_CASEID
FROM
PREVIOUS_MONTH_DATA
)
) REN_DATES
GROUP BY
REN_DATES.CASEID
)
, IC_PDC_INFORMATION AS
(SELECT
PMD.IC_CASEREFERENCE
, PDC.CASEREFERENCE PDC_CASEREFERENCE
,(SELECT
TO_CHAR(MIN(CStat.STARTDATETIME), 'MM/DD/YYYY HH24:MI:SS')
FROM
MNsureViews.CASESTATUSv CStat --CS is reserved word
WHERE
CStat.CASEID = PDC.CASEID
AND STATUSCODE = 'CS5'
) CASECREATIONDATE
,(SELECT
MIN(CCD.DETERMINATIONDATETIME)
FROM
MNsureViews.CREOLECASEDETERMINATIONv CCD
WHERE
CCD.CASEID = PDC.CASEID
AND CCD.ASSESSMENTSTATUS IS NOT NULL
) PDCACTIVATIONDATE
,(SELECT
MAX(FULLNAME)
FROM
MNsureViews.USERSv US
WHERE
US.STATUSCODE = 'RST1'
AND US.USERNAME =
(SELECT
MAX(CREATEDBYUSER)
FROM
MNsureViews.CREOLECASEDETERMINATIONv CCD
WHERE
CCD.CASEID = PDC.CASEID
AND CCD.ASSESSMENTSTATUS IS NOT NULL
AND NOT EXISTS
(SELECT
1
FROM
MNsureViews.CREOLECASEDETERMINATIONv IN_CCD
WHERE
IN_CCD.CASEID = CCD.CASEID
AND IN_CCD.ASSESSMENTSTATUS IS NOT NULL
AND IN_CCD.DETERMINATIONDATETIME < CCD.DETERMINATIONDATETIME
)
)
) PDCACTIVATOR
, TO_CHAR(PMD.APPLICATIONDATE, 'MM/DD/YYYY') APPLICATIONDATE
FROM
PREVIOUS_MONTH_DATA PMD
INNER JOIN MNsureViews.CASEHEADERv PDC ON PDC.INTEGRATEDCASEID = PMD.IC_CASEID AND PDC.CASETYPECODE = 'CT2'
AND NOT EXISTS
(SELECT
1
FROM
MNsureViews.CASEHEADERv IN_PDC
WHERE
IN_PDC.INTEGRATEDCASEID = PDC.INTEGRATEDCASEID
AND IN_PDC.CASETYPECODE = PDC.CASETYPECODE
AND IN_PDC.STATUSCODE = PDC.STATUSCODE
AND IN_PDC.RECEIVEDDATE < PDC.RECEIVEDDATE
)
)
SELECT DISTINCT
PMD.IC_CASEREFERENCE "Integrated Case Number"
, IPI.PDC_CASEREFERENCE "PDC Reference Number"
, IPI.CASECREATIONDATE "Case Creation Date"
, TO_CHAR(IPI.PDCACTIVATIONDATE,'MM/DD/YYYY HH24:MI:SS') "PDC Activation Date"
, IPI.PDCACTIVATOR "PDC Activator"
,(SELECT
MAX(DESCRIPTION)
FROM
MNsureViews.CODETABLEITEMv
WHERE
CODE = PMD.METHODOFAPPLICATION
AND TABLENAME = 'MethodOfApplication'
) "Application Type"
, CASE
WHEN PMD.METHODOFAPPLICATION = 'MOA27001'
THEN
(SELECT
MAX(US.FULLNAME)
FROM
MNsureViews.USERSv US
WHERE
US.USERNAME = PMD.ENTEREDBY
)
ELSE 'SYSTEM'
END "Application Entered By"
,(SELECT
MAX(CTI.DESCRIPTION)
FROM
MNsureViews.CODETABLEITEMv CTI
, MNsureViews.EVIDENCEDESCRIPTORv ED
, MNsureViews.DYNAMICEVIDENCEDATAATTRIBUTEv DE
WHERE
ED.EVIDENCETYPE = 'DET0001029'
AND ED.STATUSCODE = 'EDS1'
AND DE.EVIDENCEID = ED.RELATEDID
AND DE.NAME LIKE 'serviceAgencyName'
AND CTI.CODE = DE.attributeVALUE
AND CTI.TABLENAME = 'MNXServiceAgenciesMA'
AND NOT EXISTS
(SELECT
1
FROM
MNsureViews.EVIDENCEDESCRIPTORv
WHERE
EVIDENCEDESCRIPTOR.CASEID = ED.CASEID
AND EVIDENCEDESCRIPTOR.EVIDENCETYPE = ED.EVIDENCETYPE
AND EVIDENCEDESCRIPTOR.STATUSCODE = ED.STATUSCODE
AND EVIDENCEDESCRIPTOR.PARTICIPANTID = ED.PARTICIPANTID
AND EVIDENCEDESCRIPTOR.LASTWRITTEN > ED.LASTWRITTEN
)
AND ED.CASEID = PMD.IC_CASEID
) "Service Agency"
, CASE PMD.ORGOBJECTcode
WHEN 'RL9'
THEN
(SELECT
MAX(USERSv.FULLNAME)
FROM
MNsureViews.USERSv USERS
WHERE
USERSv.USERNAME = PMD.USERNAME
AND USERS.STATUSCODE = 'RST1'
)
WHEN 'RL10'
THEN
(SELECT
MAX(OU.NAME)
FROM
MNsureViews.ORGANIzATIONUNITv OU
WHERE
OU.ORGANIzATIONUNITID = PMD.ORGOBJECTREFERENCE
AND OU.STATUSCODE = 'OUSC1'
)
WHEN 'RL21'
THEN
(SELECT
MAX(POS.NAME)
FROM
MNsureViews.jobPOSITIONv POS
WHERE
POS.POSITIONID = PMD.ORGOBJECTREFERENCE
AND POS.RECORDSTATUS = 'RST1'
)
WHEN 'RL23'
THEN
(SELECT
MAX(WQ.NAME)
FROM
MNsureViews.WORKQUEUEv WQ
WHERE
WQ.WORKQUEUEID = PMD.ORGOBJECTREFERENCE
)
ELSE 'unknown'
END AS "Case Owner"
FROM
PREVIOUS_MONTH_DATA PMD
INNER JOIN IC_PDC_INFORMATION IPI ON IPI.IC_CASEREFERENCE = PMD.IC_CASEREFERENCE
WHERE TO_CHAR(IPI.PDCACTIVATIONDATE, 'MMYYYY') = TO_CHAR(ADD_MONTHS(current_date, - 1), 'MMYYYY')
AND NOT EXISTS (SELECT 1 FROM IC_PDC_INFORMATION IN_IPI WHERE IN_IPI.IC_CASEREFERENCE=IPI.IC_CASEREFERENCE AND IN_IPI.PDCACTIVATIONDATE>IPI.PDCACTIVATIONDATE)
ORDER BY
4

 

Enthusiast

Re: WITH Statement Problem

Thank you for the tip.  I have tried moving the WITH clause around but have still not had success. Below is the rest of the query. Where do you think the WITH clause it should be moved for the query to run successfully? Thanks again.

 

, CHECK_IC_RENEWAL_DATES AS
(SELECT
TO_CHAR(MIN(REN_DATES.RENEWAL_DATES), 'MM/DD/YYYY') EARLIEST_RENEWAL_DATE
, REN_DATES.CASEID IC_CASEID
FROM
(SELECT
(SELECT
MIN(PCCH.STATUSDATETIME)
FROM
MNsureViews.PDMRUNCASECONTROLSTATEHISTORYv PCCH
WHERE
PCCH.PDMRUNCASECONTROLID = PCC.PDMRUNCASECONTROLID
AND PCCH.STATUS = 'RCCS26000'
) RENEWAL_DATES
, PCC.CASEID
FROM
MNsureViews.PDMRUNCASECONTROLv PCC
WHERE
PCC.CASEID IN
(SELECT
IC_CASEID
FROM
PREVIOUS_MONTH_DATA
)
) REN_DATES
GROUP BY
REN_DATES.CASEID
)
, IC_PDC_INFORMATION AS
(SELECT
PMD.IC_CASEREFERENCE
, PDC.CASEREFERENCE PDC_CASEREFERENCE
,(SELECT
TO_CHAR(MIN(CStat.STARTDATETIME), 'MM/DD/YYYY HH24:MI:SS')
FROM
MNsureViews.CASESTATUSv CStat --CS is reserved word
WHERE
CStat.CASEID = PDC.CASEID
AND STATUSCODE = 'CS5'
) CASECREATIONDATE
,(SELECT
MIN(CCD.DETERMINATIONDATETIME)
FROM
MNsureViews.CREOLECASEDETERMINATIONv CCD
WHERE
CCD.CASEID = PDC.CASEID
AND CCD.ASSESSMENTSTATUS IS NOT NULL
) PDCACTIVATIONDATE
,(SELECT
MAX(FULLNAME)
FROM
MNsureViews.USERSv US
WHERE
US.STATUSCODE = 'RST1'
AND US.USERNAME =
(SELECT
MAX(CREATEDBYUSER)
FROM
MNsureViews.CREOLECASEDETERMINATIONv CCD
WHERE
CCD.CASEID = PDC.CASEID
AND CCD.ASSESSMENTSTATUS IS NOT NULL
AND NOT EXISTS
(SELECT
1
FROM
MNsureViews.CREOLECASEDETERMINATIONv IN_CCD
WHERE
IN_CCD.CASEID = CCD.CASEID
AND IN_CCD.ASSESSMENTSTATUS IS NOT NULL
AND IN_CCD.DETERMINATIONDATETIME < CCD.DETERMINATIONDATETIME
)
)
) PDCACTIVATOR
, TO_CHAR(PMD.APPLICATIONDATE, 'MM/DD/YYYY') APPLICATIONDATE
FROM
PREVIOUS_MONTH_DATA PMD
INNER JOIN MNsureViews.CASEHEADERv PDC ON PDC.INTEGRATEDCASEID = PMD.IC_CASEID AND PDC.CASETYPECODE = 'CT2'
AND NOT EXISTS
(SELECT
1
FROM
MNsureViews.CASEHEADERv IN_PDC
WHERE
IN_PDC.INTEGRATEDCASEID = PDC.INTEGRATEDCASEID
AND IN_PDC.CASETYPECODE = PDC.CASETYPECODE
AND IN_PDC.STATUSCODE = PDC.STATUSCODE
AND IN_PDC.RECEIVEDDATE < PDC.RECEIVEDDATE
)
)
SELECT DISTINCT
PMD.IC_CASEREFERENCE "Integrated Case Number"
, IPI.PDC_CASEREFERENCE "PDC Reference Number"
, IPI.CASECREATIONDATE "Case Creation Date"
, TO_CHAR(IPI.PDCACTIVATIONDATE,'MM/DD/YYYY HH24:MI:SS') "PDC Activation Date"
, IPI.PDCACTIVATOR "PDC Activator"
,(SELECT
MAX(DESCRIPTION)
FROM
MNsureViews.CODETABLEITEMv
WHERE
CODE = PMD.METHODOFAPPLICATION
AND TABLENAME = 'MethodOfApplication'
) "Application Type"
, CASE
WHEN PMD.METHODOFAPPLICATION = 'MOA27001'
THEN
(SELECT
MAX(US.FULLNAME)
FROM
MNsureViews.USERSv US
WHERE
US.USERNAME = PMD.ENTEREDBY
)
ELSE 'SYSTEM'
END "Application Entered By"
,(SELECT
MAX(CTI.DESCRIPTION)
FROM
MNsureViews.CODETABLEITEMv CTI
, MNsureViews.EVIDENCEDESCRIPTORv ED
, MNsureViews.DYNAMICEVIDENCEDATAATTRIBUTEv DE
WHERE
ED.EVIDENCETYPE = 'DET0001029'
AND ED.STATUSCODE = 'EDS1'
AND DE.EVIDENCEID = ED.RELATEDID
AND DE.NAME LIKE 'serviceAgencyName'
AND CTI.CODE = DE.attributeVALUE
AND CTI.TABLENAME = 'MNXServiceAgenciesMA'
AND NOT EXISTS
(SELECT
1
FROM
MNsureViews.EVIDENCEDESCRIPTORv
WHERE
EVIDENCEDESCRIPTOR.CASEID = ED.CASEID
AND EVIDENCEDESCRIPTOR.EVIDENCETYPE = ED.EVIDENCETYPE
AND EVIDENCEDESCRIPTOR.STATUSCODE = ED.STATUSCODE
AND EVIDENCEDESCRIPTOR.PARTICIPANTID = ED.PARTICIPANTID
AND EVIDENCEDESCRIPTOR.LASTWRITTEN > ED.LASTWRITTEN
)
AND ED.CASEID = PMD.IC_CASEID
) "Service Agency"
, CASE PMD.ORGOBJECTcode
WHEN 'RL9'
THEN
(SELECT
MAX(USERSv.FULLNAME)
FROM
MNsureViews.USERSv USERS
WHERE
USERSv.USERNAME = PMD.USERNAME
AND USERS.STATUSCODE = 'RST1'
)
WHEN 'RL10'
THEN
(SELECT
MAX(OU.NAME)
FROM
MNsureViews.ORGANIzATIONUNITv OU
WHERE
OU.ORGANIzATIONUNITID = PMD.ORGOBJECTREFERENCE
AND OU.STATUSCODE = 'OUSC1'
)
WHEN 'RL21'
THEN
(SELECT
MAX(POS.NAME)
FROM
MNsureViews.jobPOSITIONv POS
WHERE
POS.POSITIONID = PMD.ORGOBJECTREFERENCE
AND POS.RECORDSTATUS = 'RST1'
)
WHEN 'RL23'
THEN
(SELECT
MAX(WQ.NAME)
FROM
MNsureViews.WORKQUEUEv WQ
WHERE
WQ.WORKQUEUEID = PMD.ORGOBJECTREFERENCE
)
ELSE 'unknown'
END AS "Case Owner"
FROM
PREVIOUS_MONTH_DATA PMD
INNER JOIN IC_PDC_INFORMATION IPI ON IPI.IC_CASEREFERENCE = PMD.IC_CASEREFERENCE
WHERE TO_CHAR(IPI.PDCACTIVATIONDATE, 'MMYYYY') = TO_CHAR(ADD_MONTHS(current_date, - 1), 'MMYYYY')
AND NOT EXISTS (SELECT 1 FROM IC_PDC_INFORMATION IN_IPI WHERE IN_IPI.IC_CASEREFERENCE=IPI.IC_CASEREFERENCE AND IN_IPI.PDCACTIVATIONDATE>IPI.PDCACTIVATIONDATE)
ORDER BY 4

 

Enthusiast

Re: WITH Statement Problem

Thank you for the tip.  I have tried moving the WITH clause around but have still not had success. Below is the entire query.

The WITH clause in question is in lines 1 - 46. Where do you think it should be moved for the query to run successfully? Thanks again.

 

WITH PREVIOUS_MONTH_DATA AS
(SELECT
IC.CASEID IC_CASEID
, IC.CASEREFERENCE IC_CASEREFERENCE
, AC.APPLICATIONCASEID
, IC.STATUSCODE IC_STATUSCODE
, AC.APPLICATIONDATE
, AC.METHODOFAPPLICATIONcode
,(SELECT
MAX(IA.ENTEREDBY)
FROM
MNsureViews.INTAKEAPPLICATIONv IA
WHERE
IA.REFERENCEid = AC.APPLICATIONREFERENCE
) ENTEREDBY
, OOL.ORGOBJECTcode
, OOL.ORGOBJECTREFERENCE
, OOL.USERNAMEcode
, PDC.RECEIVEDDATE
FROM
MNsureViews.APPLICATIONCASEv AC
INNER JOIN MNsureViews.CASERELATIONSHIPv CR ON CR.CASEID = AC.APPLICATIONCASEID AND CR.TYPECODE = 'CT19'
INNER JOIN MNsureViews.CASEHEADERv IC ON IC.CASEID = CR.RELATEDCASEID AND IC.CASETYPECODE = 'CT5'
INNER JOIN MNsureViews.ORGOBJECTLINKv OOL ON OOL.ORGOBJECTLINKID = IC.OWNERORGOBJECTLINKID
INNER JOIN MNsureViews.CASEHEADERv PDC ON PDC.INTEGRATEDCASEID=IC.CASEID AND PDC.STATUSCODE<>'CS3'
AND TO_CHAR((SELECT
MIN(CCD.DETERMINATIONDATETIME)
FROM
MNsureViews.CREOLECASEDETERMINATIONv CCD
WHERE
CCD.CASEID = PDC.CASEID
AND CCD.ASSESSMENTSTATUS IS NOT NULL
), 'MMYYYY') = TO_CHAR(ADD_MONTHS(current_date, - 1), 'MMYYYY') AND
AC.APPLICATIONDATE <= IC.RECEIVEDDATE
AND NOT EXISTS
(SELECT
1
FROM
MNsureViews.CASEHEADERv PDC
WHERE
PDC.INTEGRATEDCASEID = IC.CASEID
AND PDC.CASETYPECODE = 'CT2'
AND (PDC.STATUSCODE = 'CS3'
OR PDC.STATUSCODE <> 'CS1')
)
)
, CHECK_IC_RENEWAL_DATES AS
(SELECT
TO_CHAR(MIN(REN_DATES.RENEWAL_DATES), 'MM/DD/YYYY') EARLIEST_RENEWAL_DATE
, REN_DATES.CASEID IC_CASEID
FROM
(SELECT
(SELECT
MIN(PCCH.STATUSDATETIME)
FROM
MNsureViews.PDMRUNCASECONTROLSTATEHISTORYv PCCH
WHERE
PCCH.PDMRUNCASECONTROLID = PCC.PDMRUNCASECONTROLID
AND PCCH.STATUS = 'RCCS26000'
) RENEWAL_DATES
, PCC.CASEID
FROM
MNsureViews.PDMRUNCASECONTROLv PCC
WHERE
PCC.CASEID IN
(SELECT
IC_CASEID
FROM
PREVIOUS_MONTH_DATA
)
) REN_DATES
GROUP BY
REN_DATES.CASEID
)
, IC_PDC_INFORMATION AS
(SELECT
PMD.IC_CASEREFERENCE
, PDC.CASEREFERENCE PDC_CASEREFERENCE
,(SELECT
TO_CHAR(MIN(CStat.STARTDATETIME), 'MM/DD/YYYY HH24:MI:SS')
FROM
MNsureViews.CASESTATUSv CStat --CS is reserved word
WHERE
CStat.CASEID = PDC.CASEID
AND STATUSCODE = 'CS5'
) CASECREATIONDATE
,(SELECT
MIN(CCD.DETERMINATIONDATETIME)
FROM
MNsureViews.CREOLECASEDETERMINATIONv CCD
WHERE
CCD.CASEID = PDC.CASEID
AND CCD.ASSESSMENTSTATUS IS NOT NULL
) PDCACTIVATIONDATE
,(SELECT
MAX(FULLNAME)
FROM
MNsureViews.USERSv US
WHERE
US.STATUSCODE = 'RST1'
AND US.USERNAME =
(SELECT
MAX(CREATEDBYUSER)
FROM
MNsureViews.CREOLECASEDETERMINATIONv CCD
WHERE
CCD.CASEID = PDC.CASEID
AND CCD.ASSESSMENTSTATUS IS NOT NULL
AND NOT EXISTS
(SELECT
1
FROM
MNsureViews.CREOLECASEDETERMINATIONv IN_CCD
WHERE
IN_CCD.CASEID = CCD.CASEID
AND IN_CCD.ASSESSMENTSTATUS IS NOT NULL
AND IN_CCD.DETERMINATIONDATETIME < CCD.DETERMINATIONDATETIME
)
)
) PDCACTIVATOR
, TO_CHAR(PMD.APPLICATIONDATE, 'MM/DD/YYYY') APPLICATIONDATE
FROM
PREVIOUS_MONTH_DATA PMD
INNER JOIN MNsureViews.CASEHEADERv PDC ON PDC.INTEGRATEDCASEID = PMD.IC_CASEID AND PDC.CASETYPECODE = 'CT2'
AND NOT EXISTS
(SELECT
1
FROM
MNsureViews.CASEHEADERv IN_PDC
WHERE
IN_PDC.INTEGRATEDCASEID = PDC.INTEGRATEDCASEID
AND IN_PDC.CASETYPECODE = PDC.CASETYPECODE
AND IN_PDC.STATUSCODE = PDC.STATUSCODE
AND IN_PDC.RECEIVEDDATE < PDC.RECEIVEDDATE
)
)
SELECT DISTINCT
PMD.IC_CASEREFERENCE "Integrated Case Number"
, IPI.PDC_CASEREFERENCE "PDC Reference Number"
, IPI.CASECREATIONDATE "Case Creation Date"
, TO_CHAR(IPI.PDCACTIVATIONDATE,'MM/DD/YYYY HH24:MI:SS') "PDC Activation Date"
, IPI.PDCACTIVATOR "PDC Activator"
,(SELECT
MAX(DESCRIPTION)
FROM
MNsureViews.CODETABLEITEMv
WHERE
CODE = PMD.METHODOFAPPLICATION
AND TABLENAME = 'MethodOfApplication'
) "Application Type"
, CASE
WHEN PMD.METHODOFAPPLICATION = 'MOA27001'
THEN
(SELECT
MAX(US.FULLNAME)
FROM
MNsureViews.USERSv US
WHERE
US.USERNAME = PMD.ENTEREDBY
)
ELSE 'SYSTEM'
END "Application Entered By"
,(SELECT
MAX(CTI.DESCRIPTION)
FROM
MNsureViews.CODETABLEITEMv CTI
, MNsureViews.EVIDENCEDESCRIPTORv ED
, MNsureViews.DYNAMICEVIDENCEDATAATTRIBUTEv DE
WHERE
ED.EVIDENCETYPE = 'DET0001029'
AND ED.STATUSCODE = 'EDS1'
AND DE.EVIDENCEID = ED.RELATEDID
AND DE.NAME LIKE 'serviceAgencyName'
AND CTI.CODE = DE.attributeVALUE
AND CTI.TABLENAME = 'MNXServiceAgenciesMA'
AND NOT EXISTS
(SELECT
1
FROM
MNsureViews.EVIDENCEDESCRIPTORv
WHERE
EVIDENCEDESCRIPTOR.CASEID = ED.CASEID
AND EVIDENCEDESCRIPTOR.EVIDENCETYPE = ED.EVIDENCETYPE
AND EVIDENCEDESCRIPTOR.STATUSCODE = ED.STATUSCODE
AND EVIDENCEDESCRIPTOR.PARTICIPANTID = ED.PARTICIPANTID
AND EVIDENCEDESCRIPTOR.LASTWRITTEN > ED.LASTWRITTEN
)
AND ED.CASEID = PMD.IC_CASEID
) "Service Agency"
, CASE PMD.ORGOBJECTcode
WHEN 'RL9'
THEN
(SELECT
MAX(USERSv.FULLNAME)
FROM
MNsureViews.USERSv USERS
WHERE
USERSv.USERNAME = PMD.USERNAME
AND USERS.STATUSCODE = 'RST1'
)
WHEN 'RL10'
THEN
(SELECT
MAX(OU.NAME)
FROM
MNsureViews.ORGANIzATIONUNITv OU
WHERE
OU.ORGANIzATIONUNITID = PMD.ORGOBJECTREFERENCE
AND OU.STATUSCODE = 'OUSC1'
)
WHEN 'RL21'
THEN
(SELECT
MAX(POS.NAME)
FROM
MNsureViews.jobPOSITIONv POS
WHERE
POS.POSITIONID = PMD.ORGOBJECTREFERENCE
AND POS.RECORDSTATUS = 'RST1'
)
WHEN 'RL23'
THEN
(SELECT
MAX(WQ.NAME)
FROM
MNsureViews.WORKQUEUEv WQ
WHERE
WQ.WORKQUEUEID = PMD.ORGOBJECTREFERENCE
)
ELSE 'unknown'
END AS "Case Owner"
FROM
PREVIOUS_MONTH_DATA PMD
INNER JOIN IC_PDC_INFORMATION IPI ON IPI.IC_CASEREFERENCE = PMD.IC_CASEREFERENCE
WHERE TO_CHAR(IPI.PDCACTIVATIONDATE, 'MMYYYY') = TO_CHAR(ADD_MONTHS(current_date, - 1), 'MMYYYY')
AND NOT EXISTS (SELECT 1 FROM IC_PDC_INFORMATION IN_IPI WHERE IN_IPI.IC_CASEREFERENCE=IPI.IC_CASEREFERENCE AND IN_IPI.PDCACTIVATIONDATE>IPI.PDCACTIVATIONDATE)
ORDER BY
4

 

Enthusiast

Re: WITH Statement Problem

Thank you for the tip.  I have tried moving the WITH clause around but have still not had success. Below is the entire query.

The WITH clause in question is in lines 1 - 46. Where do you think it should be moved for the query to run successfully? Thanks again.

 

WITH PREVIOUS_MONTH_DATA AS
(SELECT
IC.CASEID IC_CASEID
, IC.CASEREFERENCE IC_CASEREFERENCE
, AC.APPLICATIONCASEID
, IC.STATUSCODE IC_STATUSCODE
, AC.APPLICATIONDATE
, AC.METHODOFAPPLICATIONcode
,(SELECT
MAX(IA.ENTEREDBY)
FROM
MNsureViews.INTAKEAPPLICATIONv IA
WHERE
IA.REFERENCEid = AC.APPLICATIONREFERENCE
) ENTEREDBY
, OOL.ORGOBJECTcode
, OOL.ORGOBJECTREFERENCE
, OOL.USERNAMEcode
, PDC.RECEIVEDDATE
FROM
MNsureViews.APPLICATIONCASEv AC
INNER JOIN MNsureViews.CASERELATIONSHIPv CR ON CR.CASEID = AC.APPLICATIONCASEID AND CR.TYPECODE = 'CT19'
INNER JOIN MNsureViews.CASEHEADERv IC ON IC.CASEID = CR.RELATEDCASEID AND IC.CASETYPECODE = 'CT5'
INNER JOIN MNsureViews.ORGOBJECTLINKv OOL ON OOL.ORGOBJECTLINKID = IC.OWNERORGOBJECTLINKID
INNER JOIN MNsureViews.CASEHEADERv PDC ON PDC.INTEGRATEDCASEID=IC.CASEID AND PDC.STATUSCODE<>'CS3'
AND TO_CHAR((SELECT
MIN(CCD.DETERMINATIONDATETIME)
FROM
MNsureViews.CREOLECASEDETERMINATIONv CCD
WHERE
CCD.CASEID = PDC.CASEID
AND CCD.ASSESSMENTSTATUS IS NOT NULL
), 'MMYYYY') = TO_CHAR(ADD_MONTHS(current_date, - 1), 'MMYYYY') AND
AC.APPLICATIONDATE <= IC.RECEIVEDDATE
AND NOT EXISTS
(SELECT
1
FROM
MNsureViews.CASEHEADERv PDC
WHERE
PDC.INTEGRATEDCASEID = IC.CASEID
AND PDC.CASETYPECODE = 'CT2'
AND (PDC.STATUSCODE = 'CS3'
OR PDC.STATUSCODE <> 'CS1')
)
)
, CHECK_IC_RENEWAL_DATES AS
(SELECT
TO_CHAR(MIN(REN_DATES.RENEWAL_DATES), 'MM/DD/YYYY') EARLIEST_RENEWAL_DATE
, REN_DATES.CASEID IC_CASEID
FROM
(SELECT
(SELECT
MIN(PCCH.STATUSDATETIME)
FROM
MNsureViews.PDMRUNCASECONTROLSTATEHISTORYv PCCH
WHERE
PCCH.PDMRUNCASECONTROLID = PCC.PDMRUNCASECONTROLID
AND PCCH.STATUS = 'RCCS26000'
) RENEWAL_DATES
, PCC.CASEID
FROM
MNsureViews.PDMRUNCASECONTROLv PCC
WHERE
PCC.CASEID IN
(SELECT
IC_CASEID
FROM
PREVIOUS_MONTH_DATA
)
) REN_DATES
GROUP BY
REN_DATES.CASEID
)
, IC_PDC_INFORMATION AS
(SELECT
PMD.IC_CASEREFERENCE
, PDC.CASEREFERENCE PDC_CASEREFERENCE
,(SELECT
TO_CHAR(MIN(CStat.STARTDATETIME), 'MM/DD/YYYY HH24:MI:SS')
FROM
MNsureViews.CASESTATUSv CStat --CS is reserved word
WHERE
CStat.CASEID = PDC.CASEID
AND STATUSCODE = 'CS5'
) CASECREATIONDATE
,(SELECT
MIN(CCD.DETERMINATIONDATETIME)
FROM
MNsureViews.CREOLECASEDETERMINATIONv CCD
WHERE
CCD.CASEID = PDC.CASEID
AND CCD.ASSESSMENTSTATUS IS NOT NULL
) PDCACTIVATIONDATE
,(SELECT
MAX(FULLNAME)
FROM
MNsureViews.USERSv US
WHERE
US.STATUSCODE = 'RST1'
AND US.USERNAME =
(SELECT
MAX(CREATEDBYUSER)
FROM
MNsureViews.CREOLECASEDETERMINATIONv CCD
WHERE
CCD.CASEID = PDC.CASEID
AND CCD.ASSESSMENTSTATUS IS NOT NULL
AND NOT EXISTS
(SELECT
1
FROM
MNsureViews.CREOLECASEDETERMINATIONv IN_CCD
WHERE
IN_CCD.CASEID = CCD.CASEID
AND IN_CCD.ASSESSMENTSTATUS IS NOT NULL
AND IN_CCD.DETERMINATIONDATETIME < CCD.DETERMINATIONDATETIME
)
)
) PDCACTIVATOR
, TO_CHAR(PMD.APPLICATIONDATE, 'MM/DD/YYYY') APPLICATIONDATE
FROM
PREVIOUS_MONTH_DATA PMD
INNER JOIN MNsureViews.CASEHEADERv PDC ON PDC.INTEGRATEDCASEID = PMD.IC_CASEID AND PDC.CASETYPECODE = 'CT2'
AND NOT EXISTS
(SELECT
1
FROM
MNsureViews.CASEHEADERv IN_PDC
WHERE
IN_PDC.INTEGRATEDCASEID = PDC.INTEGRATEDCASEID
AND IN_PDC.CASETYPECODE = PDC.CASETYPECODE
AND IN_PDC.STATUSCODE = PDC.STATUSCODE
AND IN_PDC.RECEIVEDDATE < PDC.RECEIVEDDATE
)
)
SELECT DISTINCT
PMD.IC_CASEREFERENCE "Integrated Case Number"
, IPI.PDC_CASEREFERENCE "PDC Reference Number"
, IPI.CASECREATIONDATE "Case Creation Date"
, TO_CHAR(IPI.PDCACTIVATIONDATE,'MM/DD/YYYY HH24:MI:SS') "PDC Activation Date"
, IPI.PDCACTIVATOR "PDC Activator"
,(SELECT
MAX(DESCRIPTION)
FROM
MNsureViews.CODETABLEITEMv
WHERE
CODE = PMD.METHODOFAPPLICATION
AND TABLENAME = 'MethodOfApplication'
) "Application Type"
, CASE
WHEN PMD.METHODOFAPPLICATION = 'MOA27001'
THEN
(SELECT
MAX(US.FULLNAME)
FROM
MNsureViews.USERSv US
WHERE
US.USERNAME = PMD.ENTEREDBY
)
ELSE 'SYSTEM'
END "Application Entered By"
,(SELECT
MAX(CTI.DESCRIPTION)
FROM
MNsureViews.CODETABLEITEMv CTI
, MNsureViews.EVIDENCEDESCRIPTORv ED
, MNsureViews.DYNAMICEVIDENCEDATAATTRIBUTEv DE
WHERE
ED.EVIDENCETYPE = 'DET0001029'
AND ED.STATUSCODE = 'EDS1'
AND DE.EVIDENCEID = ED.RELATEDID
AND DE.NAME LIKE 'serviceAgencyName'
AND CTI.CODE = DE.attributeVALUE
AND CTI.TABLENAME = 'MNXServiceAgenciesMA'
AND NOT EXISTS
(SELECT
1
FROM
MNsureViews.EVIDENCEDESCRIPTORv
WHERE
EVIDENCEDESCRIPTOR.CASEID = ED.CASEID
AND EVIDENCEDESCRIPTOR.EVIDENCETYPE = ED.EVIDENCETYPE
AND EVIDENCEDESCRIPTOR.STATUSCODE = ED.STATUSCODE
AND EVIDENCEDESCRIPTOR.PARTICIPANTID = ED.PARTICIPANTID
AND EVIDENCEDESCRIPTOR.LASTWRITTEN > ED.LASTWRITTEN
)
AND ED.CASEID = PMD.IC_CASEID
) "Service Agency"
, CASE PMD.ORGOBJECTcode
WHEN 'RL9'
THEN
(SELECT
MAX(USERSv.FULLNAME)
FROM
MNsureViews.USERSv USERS
WHERE
USERSv.USERNAME = PMD.USERNAME
AND USERS.STATUSCODE = 'RST1'
)
WHEN 'RL10'
THEN
(SELECT
MAX(OU.NAME)
FROM
MNsureViews.ORGANIzATIONUNITv OU
WHERE
OU.ORGANIzATIONUNITID = PMD.ORGOBJECTREFERENCE
AND OU.STATUSCODE = 'OUSC1'
)
WHEN 'RL21'
THEN
(SELECT
MAX(POS.NAME)
FROM
MNsureViews.jobPOSITIONv POS
WHERE
POS.POSITIONID = PMD.ORGOBJECTREFERENCE
AND POS.RECORDSTATUS = 'RST1'
)
WHEN 'RL23'
THEN
(SELECT
MAX(WQ.NAME)
FROM
MNsureViews.WORKQUEUEv WQ
WHERE
WQ.WORKQUEUEID = PMD.ORGOBJECTREFERENCE
)
ELSE 'unknown'
END AS "Case Owner"
FROM
PREVIOUS_MONTH_DATA PMD
INNER JOIN IC_PDC_INFORMATION IPI ON IPI.IC_CASEREFERENCE = PMD.IC_CASEREFERENCE
WHERE TO_CHAR(IPI.PDCACTIVATIONDATE, 'MMYYYY') = TO_CHAR(ADD_MONTHS(current_date, - 1), 'MMYYYY')
AND NOT EXISTS (SELECT 1 FROM IC_PDC_INFORMATION IN_IPI WHERE IN_IPI.IC_CASEREFERENCE=IPI.IC_CASEREFERENCE AND IN_IPI.PDCACTIVATIONDATE>IPI.PDCACTIVATIONDATE)
ORDER BY
4

 

Enthusiast

Re: WITH Statement Problem

Thank you, Fred.

You were correct - I changed the order of the 3 WITH derived tables from 1,2,3 in oracle to 3,2,1 in Teradata, and it worked!