Easy Query Syntax Question

Good morning, experts.
Got a syntactical snag I need a fresh pair of eyes on. I've looked at this
query too long and I know that the fix is probably staring me in the face bu
t
I can't see the "forest for the trees", as it were.
SELECT * FROM
((SELECT XH.CDS_04
, XH.CDS_07
, XH.CDS_09
, CH.CaseHdr_ID
FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
ON XH.XActHdr_ID = CH.XActHdr_ID
WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 30) OrgNtc
INNER JOIN
(SELECT XH.CDS_04
, XH.CDS_07
, XH.CDS_09
, CH.CaseHdr_ID
FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
ON XH.XActHdr_ID = CH.XActHdr_ID
WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 15) DsmNtc
ON OrgNtc.CDS_04 = DsmNtc.CDS_04
AND OrgNtc.CDS_07 = DsmNtc.CDS_07
AND OrgNtc.CDS_09 = DsmNtc.CDS_09) CS
INNER JOIN
(SELECT CaseHdr_ID, NM1_ID
FROM EBN_NM1 WHERE NM1_01 IN ('XB', 'JT')) NM
ON CS.CaseHdr_ID = NM.CaseHdr_ID
INNER JOIN
(SELECT REF_02, NM1_ID
FROM EBN_REF WHERE REF_01 = 'SY') RF
ON NM.NM1_ID = RF.NM1_ID
I get a "Invalid syntax near CS, NM and RF" error when I try to execute the
whole thing. I'm betting I just need to throw some open/close parens in
there somewhere but not sure where. If anybody could help me tweak this
thing so I can get it to parse I'd be most grateful.Try,
SELECT * FROM
(
select *
from
(
SELECT XH.CDS_04
, XH.CDS_07
, XH.CDS_09
, CH.CaseHdr_ID
FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
ON XH.XActHdr_ID = CH.XActHdr_ID
WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 30\
) OrgNtc
INNER JOIN
(
SELECT XH.CDS_04
, XH.CDS_07
, XH.CDS_09
, CH.CaseHdr_ID
FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
ON XH.XActHdr_ID = CH.XActHdr_ID
WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 15
) DsmNtc
ON OrgNtc.CDS_04 = DsmNtc.CDS_04
AND OrgNtc.CDS_07 = DsmNtc.CDS_07
AND OrgNtc.CDS_09 = DsmNtc.CDS_09
) as CS
INNER JOIN
(
SELECT CaseHdr_ID, NM1_ID
FROM EBN_NM1 WHERE NM1_01 IN ('XB', 'JT')
) as NM
ON CS.CaseHdr_ID = NM.CaseHdr_ID
INNER JOIN
(
SELECT REF_02, NM1_ID
FROM EBN_REF WHERE REF_01 = 'SY'
) as RF
ON NM.NM1_ID = RF.NM1_ID
AMB
"Bob St. Aubyn" wrote:

> Good morning, experts.
> Got a syntactical snag I need a fresh pair of eyes on. I've looked at thi
s
> query too long and I know that the fix is probably staring me in the face
but
> I can't see the "forest for the trees", as it were.
> SELECT * FROM
> ((SELECT XH.CDS_04
> , XH.CDS_07
> , XH.CDS_09
> , CH.CaseHdr_ID
> FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
> ON XH.XActHdr_ID = CH.XActHdr_ID
> WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 30) OrgNtc
> INNER JOIN
> (SELECT XH.CDS_04
> , XH.CDS_07
> , XH.CDS_09
> , CH.CaseHdr_ID
> FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
> ON XH.XActHdr_ID = CH.XActHdr_ID
> WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 15) DsmNtc
> ON OrgNtc.CDS_04 = DsmNtc.CDS_04
> AND OrgNtc.CDS_07 = DsmNtc.CDS_07
> AND OrgNtc.CDS_09 = DsmNtc.CDS_09) CS
> INNER JOIN
> (SELECT CaseHdr_ID, NM1_ID
> FROM EBN_NM1 WHERE NM1_01 IN ('XB', 'JT')) NM
> ON CS.CaseHdr_ID = NM.CaseHdr_ID
> INNER JOIN
> (SELECT REF_02, NM1_ID
> FROM EBN_REF WHERE REF_01 = 'SY') RF
> ON NM.NM1_ID = RF.NM1_ID
> I get a "Invalid syntax near CS, NM and RF" error when I try to execute th
e
> whole thing. I'm betting I just need to throw some open/close parens in
> there somewhere but not sure where. If anybody could help me tweak this
> thing so I can get it to parse I'd be most grateful.|||Thanks Alejandro. Worked and made sense.
I had to make one slight modification to get it to parse: Changed the new
"select * from" (in lower case) to "SELECT OrgNtc.CDS_04, OrgNtc.CaseHdr_ID
FROM ..." because both of those columns are selected in each of the
subqueries combined to make CS. Just had to explicitly specify which one I
wanted to return.
"Alejandro Mesa" wrote:
> Try,
> SELECT * FROM
> (
> select *
> from
> (
> SELECT XH.CDS_04
> , XH.CDS_07
> , XH.CDS_09
> , CH.CaseHdr_ID
> FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
> ON XH.XActHdr_ID = CH.XActHdr_ID
> WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 30\
> ) OrgNtc
> INNER JOIN
> (
> SELECT XH.CDS_04
> , XH.CDS_07
> , XH.CDS_09
> , CH.CaseHdr_ID
> FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
> ON XH.XActHdr_ID = CH.XActHdr_ID
> WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 15
> ) DsmNtc
> ON OrgNtc.CDS_04 = DsmNtc.CDS_04
> AND OrgNtc.CDS_07 = DsmNtc.CDS_07
> AND OrgNtc.CDS_09 = DsmNtc.CDS_09
> ) as CS
> INNER JOIN
> (
> SELECT CaseHdr_ID, NM1_ID
> FROM EBN_NM1 WHERE NM1_01 IN ('XB', 'JT')
> ) as NM
> ON CS.CaseHdr_ID = NM.CaseHdr_ID
> INNER JOIN
> (
> SELECT REF_02, NM1_ID
> FROM EBN_REF WHERE REF_01 = 'SY'
> ) as RF
> ON NM.NM1_ID = RF.NM1_ID
>
> AMB
> "Bob St. Aubyn" wrote:
>