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:
>

Dynamic SQL vs OSQL

Question for the experts here:

Is there any advantage of running an SQL statement through osql with the database information over using dynamic sql?

Example:

DECLARE @.DB Varchar(50)
DECLARE @.SQL Varchar(4000)
SET @.DB = '<nvr_changing_server>.<my_dynamic_db_name>'
SET @.SQL = 'SELECT admissiontype_id AS atype, admissiontype AS atype_desc, start_date, end_date
INTO tlkAdmitType
FROM <nvr_changing_server>.<nvr_changing_DB>.dbo.tlkAdmissionTypes'

exec master..xp_cmdShell 'osql -U sa -P sapwd -S nvr_changing_server -d my_dynamic_db_name -Q @.SQL...'

vs. something like:

DECLARE @.DB Varchar(50)
DECLARE @.SQL Varchar(4000)
SET @.DB = '<nvr_changing_server>.<my_dynamic_db_name>'
SET @.SQL = 'SELECT admissiontype_id AS atype, admissiontype AS atype_desc, start_date, end_date
INTO ' + @.DB +'.dbo.tlkAdmitType
FROM <nvr_changing_server>.<nvr_changing_DB>.dbo.tlkAdmissionTypes'

EXEC(@.SQL)

The purpose of all this is...I need to pass a parameter for the DB that I will be inserting into...here we create a new db with a specific name based on quarterly data. We collect, crunch, validate data and ship it. Then when it's old we archive it then eventually delete it.

I have written a script that makes this quarterly build less painful. In fact I won't have to do it! :)...our Sr. Data Analysts will do it now. In order for this beautiful thing (*in my mind anyway*) to work they need to set parameters for which data to pull and where to put it. The DB is scripted into existance and the data is moved into it. So therefore they need to enter the Qtr,Yr and dbname. I have done DSQL before on smaller scripts and I am just curious if the expert pool here can shed some light on this approach. The script will most likely be run in a DTS SQL Task.

Thanks in advance...RI would go for the second option. It will not require you to open up xp_cmdshell to all users (bad security hole). Also, I think the error handling is better with the second option. I am not sure how to get an error back from option 1.