SQL used in a Drived Table

[Former Member]

I created a derived table in Universe Designer using the SQL Expression shown below. But when I try to check the syntax, I get "DBD, ORA-00907: missing right paranthesis" message. I appreciate your assistance.

SELECT

(CASE WHEN (PD.PROC_OBJECT.OBJ_TYPE) = 'RQM' or (PD.PROC_OBJECT.OBJ_TYPE)='REQ' THEN PD.PROC_OBJECT.DOC_NMBR END) AS "REQ NUMBR",

(CASE WHEN (PD.PROC_OBJECT.OBJ_TYPE) = 'RQM' or (PD.PROC_OBJECT.OBJ_TYPE)='REQ' THEN PD.PROC_OBJECT.APLD_DT END) AS "Creation DATE",

(CASE WHEN (PD.PROC_OBJECT.OBJ_TYPE) = 'REQ' or (PD.PROC_OBJECT.OBJ_TYPE)='RQM' Then ( PD_MTB_AssignedSpecialist.LAST_NAME || ', ' || PD_MTB_AssignedSpecialist.FIRST_NAME ) END) As "Assigned Specialist",

(CASE WHEN (PD.PROC_OBJECT.OBJ_TYPE) = 'RQM' or (PD.PROC_OBJECT.OBJ_TYPE)='REQ' THEN PD.FUND_BY_OBJID.FUND_AMT END) As"REQ Amount",

(CASE WHEN (PD.PROC_OBJECT.OBJ_TYPE) = 'RFQ' or (PD.PROC_OBJECT.OBJ_TYPE)='RFM' or (PD.PROC_OBJECT.OBJ_TYPE)='S2M'

or (PD.PROC_OBJECT.OBJ_TYPE)='S42' or (PD.PROC_OBJECT.OBJ_TYPE)='S49' or (PD.PROC_OBJECT.OBJ_TYPE)='S9M'

or (PD.PROC_OBJECT.OBJ_TYPE)='SLM' or (PD.PROC_OBJECT.OBJ_TYPE)='SOL' or (PD.PROC_OBJECT.OBJ_TYPE)='S52'

or (PD.PROC_OBJECT.OBJ_TYPE)='S5M'THEN PD.PROC_OBJECT.DOC_NMBR END) AS "Solicitation NMBR",

(CASE WHEN (PD.PROC_OBJECT.OBJ_TYPE) = 'RFQ' or (PD.PROC_OBJECT.OBJ_TYPE)='RFM' or (PD.PROC_OBJECT.OBJ_TYPE)='S2M' or (PD.PROC_OBJECT.OBJ_TYPE)='S42' or (PD.PROC_OBJECT.OBJ_TYPE)='S49' or (PD.PROC_OBJECT.OBJ_TYPE)='S9M' or (PD.PROC_OBJECT.OBJ_TYPE)='SLM' or (PD.PROC_OBJECT.OBJ_TYPE)='SOL' or (PD.PROC_OBJECT.OBJ_TYPE)='S52' or (PD.PROC_OBJECT.OBJ_TYPE)='S5M'THEN PD.PROC_OBJECT.APLD_DT END) AS "Issue Date",

(CASE WHEN (PD.PROC_OBJECT.OBJ_TYPE) = '26M' or (PD.PROC_OBJECT.OBJ_TYPE)='B55' or (PD.PROC_OBJECT.OBJ_TYPE)='B5M' or (PD.PROC_OBJECT.OBJ_TYPE)='BPA' or (PD.PROC_OBJECT.OBJ_TYPE)='BPC' or (PD.PROC_OBJECT.OBJ_TYPE)='DEL' or (PD.PROC_OBJECT.OBJ_TYPE)='DLM' or (PD.PROC_OBJECT.OBJ_TYPE)='F26' or (PD.PROC_OBJECT.OBJ_TYPE)='PAM' or (PD.PROC_OBJECT.OBJ_TYPE)='PCM' or (PD.PROC_OBJECT.OBJ_TYPE)='TKM' or (PD.PROC_OBJECT.OBJ_TYPE)='TSK' or (PD.PROC_OBJECT.OBJ_TYPE)='C49' or (PD.PROC_OBJECT.OBJ_TYPE)='C9M' or (PD.PROC_OBJECT.OBJ_TYPE)='P49' or (PD.PROC_OBJECT.OBJ_TYPE)='P9M' or (PD.PROC_OBJECT.OBJ_TYPE)='C42' or (PD.PROC_OBJECT.OBJ_TYPE)='C2M' or (PD.PROC_OBJECT.OBJ_TYPE)='C52' or (PD.PROC_OBJECT.OBJ_TYPE)='C5M' or (PD.PROC_OBJECT.OBJ_TYPE)='C33' or (PD.PROC_OBJECT.OBJ_TYPE)='C3M'THEN PD.PROC_OBJECT.DOC_NMBR END) As "Award NMBR",

(CASE WHEN (PD.PROC_OBJECT.OBJ_TYPE) = '26M' or (PD.PROC_OBJECT.OBJ_TYPE)='B55' or (PD.PROC_OBJECT.OBJ_TYPE)='B5M' or (PD.PROC_OBJECT.OBJ_TYPE)='BPA' or (PD.PROC_OBJECT.OBJ_TYPE)='BPC' or (PD.PROC_OBJECT.OBJ_TYPE)='DEL' or (PD.PROC_OBJECT.OBJ_TYPE)='DLM' or (PD.PROC_OBJECT.OBJ_TYPE)='F26' or (PD.PROC_OBJECT.OBJ_TYPE)='PAM' or (PD.PROC_OBJECT.OBJ_TYPE)='PCM' or (PD.PROC_OBJECT.OBJ_TYPE)='TKM' or (PD.PROC_OBJECT.OBJ_TYPE)='TSK' or (PD.PROC_OBJECT.OBJ_TYPE)='C49' or (PD.PROC_OBJECT.OBJ_TYPE)='C9M' or (PD.PROC_OBJECT.OBJ_TYPE)='P49' or (PD.PROC_OBJECT.OBJ_TYPE)='P9M' or (PD.PROC_OBJECT.OBJ_TYPE)='C42' or (PD.PROC_OBJECT.OBJ_TYPE)='C2M' or (PD.PROC_OBJECT.OBJ_TYPE)='C52' or (PD.PROC_OBJECT.OBJ_TYPE)='C5M' or (PD.PROC_OBJECT.OBJ_TYPE)='C33' or (PD.PROC_OBJECT.OBJ_TYPE)='C3M'THEN PD.PROC_OBJECT.APLD_DT END) AS "Award Date",

(CASE WHEN (PD.PROC_OBJECT.OBJ_TYPE) = '26M' or (PD.PROC_OBJECT.OBJ_TYPE)='B55' or (PD.PROC_OBJECT.OBJ_TYPE)='B5M' or (PD.PROC_OBJECT.OBJ_TYPE)='BPA' or (PD.PROC_OBJECT.OBJ_TYPE)='BPC' or (PD.PROC_OBJECT.OBJ_TYPE)='DEL' or (PD.PROC_OBJECT.OBJ_TYPE)='DLM' or (PD.PROC_OBJECT.OBJ_TYPE)='F26' or (PD.PROC_OBJECT.OBJ_TYPE)='PAM' or (PD.PROC_OBJECT.OBJ_TYPE)='PCM' or (PD.PROC_OBJECT.OBJ_TYPE)='TKM' or (PD.PROC_OBJECT.OBJ_TYPE)='TSK' or (PD.PROC_OBJECT.OBJ_TYPE)='C49' or (PD.PROC_OBJECT.OBJ_TYPE)='C9M' or (PD.PROC_OBJECT.OBJ_TYPE)='P49' or (PD.PROC_OBJECT.OBJ_TYPE)='P9M' or (PD.PROC_OBJECT.OBJ_TYPE)='C42' or (PD.PROC_OBJECT.OBJ_TYPE)='C2M' or (PD.PROC_OBJECT.OBJ_TYPE)='C52' or (PD.PROC_OBJECT.OBJ_TYPE)='C5M' or (PD.PROC_OBJECT.OBJ_TYPE)='C33' or (PD.PROC_OBJECT.OBJ_TYPE)='C3M' THEN ( PD.MTB_USR.LAST_NAME || ', ' || PD.MTB_USR.FIRST_NAME ) END) As "Awarding CO",

(CASE WHEN (PD.PROC_OBJECT.OBJ_TYPE) = '26M' or (PD.PROC_OBJECT.OBJ_TYPE)='B55' or (PD.PROC_OBJECT.OBJ_TYPE)='B5M' or (PD.PROC_OBJECT.OBJ_TYPE)='BPA' or (PD.PROC_OBJECT.OBJ_TYPE)='BPC' or (PD.PROC_OBJECT.OBJ_TYPE)='DEL' or (PD.PROC_OBJECT.OBJ_TYPE)='DLM' or (PD.PROC_OBJECT.OBJ_TYPE)='F26' or (PD.PROC_OBJECT.OBJ_TYPE)='PAM' or (PD.PROC_OBJECT.OBJ_TYPE)='PCM' or (PD.PROC_OBJECT.OBJ_TYPE)='TKM' or (PD.PROC_OBJECT.OBJ_TYPE)='TSK' or (PD.PROC_OBJECT.OBJ_TYPE)='C49' or (PD.PROC_OBJECT.OBJ_TYPE)='C9M' or (PD.PROC_OBJECT.OBJ_TYPE)='P49' or (PD.PROC_OBJECT.OBJ_TYPE)='P9M' or (PD.PROC_OBJECT.OBJ_TYPE)='C42' or (PD.PROC_OBJECT.OBJ_TYPE)='C2M' or (PD.PROC_OBJECT.OBJ_TYPE)='C52' or (PD.PROC_OBJECT.OBJ_TYPE)='C5M' or (PD.PROC_OBJECT.OBJ_TYPE)='C33' or (PD.PROC_OBJECT.OBJ_TYPE)='C3M'THEN PD.FUND_BY_OBJID.FUND_AMT END) AS "Award Amount",

PD.ORGANIZATION.ORG_CODE AS "Vendor Code",

PD.ORGANIZATION.ORG_NM AS "Vendor Name"

FROM

PD.FUND_BY_OBJID,PD.MTB_USR,PD.ORGANIZATION,PD.DSK_WKLD_HD,PD.PROC_OBJECT

WHERE

PD.XREF_PROC_GRP.OBJ_ID=PD.FUND_BY_OBJID.OBJ_ID

LEFT OUTER JOIN PD.AWARD ON (PD.AWARD.OBJ_ID=PD.XREF_PROC_GRP.OBJ_ID)

LEFT OUTER JOIN PD.MTB_USR ON (PD.AWARD.CONTRACT_OFFCR_USR_ID=PD.MTB_USR.USR_ID)

LEFT OUTER JOIN PD.ORGANIZATION ON (PD.AWARD.VEND_ORG_ID=PD.ORGANIZATION.ORG_ID)

LEFT OUTER JOIN PD.DSK_WKLD_HDR ON (PD.XREF_PROC_GRP.OBJ_ID=PD.DSK_WKLD_HDR.OBJ_ID)

LEFT OUTER JOIN PD.MTB_USR PD_MTB_AssignedSpecialist ON (PD.DSK_WKLD_HDR.ASSIGNED_USR_ID=PD_MTB_AssignedSpecialist.USR_ID)

LEFT OUTER JOIN PD.PROC_OBJECT ON (PD.PROC_OBJECT.OBJ_ID=PD.XREF_PROC_GRP.OBJ_ID)

[Former Member · Accepted solution]

The back end database is Oracle. I do not have access to the SQL server so I cannot fire the sql statement in the database directly. I used the IN operator and still got the same error message. Should I use the Left outer joins in the WHERE section. Please see my new SQL in the derived table below:

SELECT

CASE WHEN PD.PROC_OBJECT.OBJ_TYPE IN ('RQM','REQ') THEN PD.PROC_OBJECT.DOC_NMBR END AS "REQ NUMBR",

CASE WHEN PD.PROC_OBJECT.OBJ_TYPE IN ('RQM','REQ') THEN PD.PROC_OBJECT.APLD_DT END AS "Creation DATE",

CASE WHEN PD.PROC_OBJECT.OBJ_TYPE IN ('RQM','REQ') THEN ( PD_MTB_AssignedSpecialist.LAST_NAME || ', ' || PD_MTB_AssignedSpecialist.FIRST_NAME ) END As "Assigned Specialist",

CASE WHEN PD.PROC_OBJECT.OBJ_TYPE IN ('RQM','REQ') THEN PD.FUND_BY_OBJID.FUND_AMT END As "REQ Amount",

CASE WHEN PD.PROC_OBJECT.OBJ_TYPE IN ('RFQ','RFM','S2M','S42','S49','S9M','SLM','SOL','S52','S5M') THEN PD.PROC_OBJECT.DOC_NMBR END AS "Solicitation NMBR",

CASE WHEN PD.PROC_OBJECT.OBJ_TYPE IN ('RFQ','RFM','S2M','S42','S49','S9M','SLM','SOL','S52', 'S5M') THEN PD.PROC_OBJECT.APLD_DT END AS "Issue Date",

CASE WHEN PD.PROC_OBJECT.OBJ_TYPE IN ('26M','B55','B5M','BPA','BPC','DEL','DLM','F26', 'PAM','PCM','TKM','TSK','C49','C9M','P49','P9M','C42','C2M','C52','C5M','C33','C3M') THEN PD.PROC_OBJECT.DOC_NMBR END As "Award NMBR",

CASE WHEN PD.PROC_OBJECT.OBJ_TYPE IN ('26M','B55','B5M','BPA','BPC','DEL','DLM','F26', 'PAM','PCM','TKM','TSK','C49','C9M','P49','P9M','C42','C2M','C52','C5M','C33','C3M') THEN PD.PROC_OBJECT.APLD_DT END AS "Award Date",

CASE WHEN PD.PROC_OBJECT.OBJ_TYPE IN ('26M','B55','B5M','BPA','BPC','DEL','DLM','F26', 'PAM','PCM','TKM','TSK','C49','C9M','P49','P9M','C42','C2M','C52','C5M','C33','C3M') THEN ( PD.MTB_USR.LAST_NAME || ', ' || PD.MTB_USR.FIRST_NAME ) END As "Awarding CO",

CASE WHEN PD.PROC_OBJECT.OBJ_TYPE IN ('26M','B55','B5M','BPA','BPC','DEL','DLM','F26', 'PAM','PCM','TKM','TSK','C49','C9M','P49','P9M','C42','C2M','C52','C5M','C33','C3M') THEN PD.FUND_BY_OBJID.FUND_AMT END AS "Award Amount",

PD.ORGANIZATION.ORG_CODE AS "Vendor Code",

PD.ORGANIZATION.ORG_NM AS "Vendor Name"

FROM

PD.FUND_BY_OBJID, PD.MTB_USR, PD.ORGANIZATION, PD.DSK_WKLD_HD,PD.PROC_OBJECT, PD.XREF_PROC_GRP, PD.AWARD

WHERE

PD.XREF_PROC_GRP.OBJ_ID=PD.FUND_BY_OBJID.OBJ_ID

LEFT OUTER JOIN PD.AWARD ON (PD.AWARD.OBJ_ID=PD.XREF_PROC_GRP.OBJ_ID)

LEFT OUTER JOIN PD.MTB_USR ON (PD.AWARD.CONTRACT_OFFCR_USR_ID=PD.MTB_USR.USR_ID)

LEFT OUTER JOIN PD.ORGANIZATION ON (PD.AWARD.VEND_ORG_ID=PD.ORGANIZATION.ORG_ID)

LEFT OUTER JOIN PD.DSK_WKLD_HDR ON (PD.XREF_PROC_GRP.OBJ_ID=PD.DSK_WKLD_HDR.OBJ_ID)

LEFT OUTER JOIN PD.MTB_USR PD_MTB_AssignedSpecialist ON (PD.DSK_WKLD_HDR.ASSIGNED_USR_ID=PD_MTB_AssignedSpecialist.USR_ID)

LEFT OUTER JOIN PD.PROC_OBJECT ON (PD.PROC_OBJECT.OBJ_ID=PD.XREF_PROC_GRP.OBJ_ID)

[Former Member · Accepted solution]

Maybe if you try the join like this instead of in a where clause:

FROM PD.FUND_BY_OBJID LEFT OUTER JOIN PD.XREF_PROC_GRP ON (PD.XREF_PROC_GRP.OBJ_ID=PD.FUND_BY_OBJID.OBJ_ID) LEFT OUTER JOIN PD.AWARD ON (PD.AWARD.OBJ_ID=PD.XREF_PROC_GRP.OBJ_ID) LEFT OUTER JOIN PD.MTB_USR ON (PD.AWARD.CONTRACT_OFFCR_USR_ID=PD.MTB_USR.USR_ID) LEFT OUTER JOIN PD.ORGANIZATION ON (PD.AWARD.VEND_ORG_ID=PD.ORGANIZATION.ORG_ID) LEFT OUTER JOIN PD.DSK_WKLD_HDR ON (PD.XREF_PROC_GRP.OBJ_ID=PD.DSK_WKLD_HDR.OBJ_ID) LEFT OUTER JOIN PD.MTB_USR PD_MTB_AssignedSpecialist ON (PD.DSK_WKLD_HDR.ASSIGNED_USR_ID=PD_MTB_AssignedSpecialist.USR_ID) LEFT OUTER JOIN PD.PROC_OBJECT ON (PD.PROC_OBJECT.OBJ_ID=PD.XREF_PROC_GRP.OBJ_ID)

[arijit_das]

Hi,

What is the backend database? Does the sql statement give any error when it is fired in the database directly?

Also, why you are not using IN operator instead of so many ORs?

Regards