Openquery syntax - multiple tables

[eshie003]

Hi, In the query below, I want to insert the results of the query into my SQL SVR, table [#TEST]. The linked server (URAFP4 is Oracle). When I run the query below, I am getting an error on the last line of the query" Incorrect syntax near ')'. What am I missing?


SELECT * INTO [RDM].[dbo].[#TEST] FROM
(SELECT * FROM OPENQUERY(URAFP4,'SELECT DISTINCT cmc.clcl_id,
cmc2.cdml_seq_no,cmc.clcl_cur_sts,cmc.clcl_recd_dt,
cmc.clcl_paid_dt, cmc2.ipcd_id , cmc2.rcrc_id,
cmc2.cdml_from_dt,cmc2.cdml_chg_amt,cmc2.cdml_paid_amt,
cmc2.cdml_allow,cmc.prpr_id,cmc1.prpr_name,
cmc1.mctn_id ,cmc2.cdml_sepy_acct_cat,
cmc3.excd_id,
cmc3.excd_short_text
FROM faubh.cmc_clcl_claim cmc INNER JOIN faubh.cmc_prpr_prov cmc1
ON cmc1.prpr_id = cmc.prpr_id
LEFT JOIN faubh.cmc_cdml_cl_line cmc2
ON cmc2.clcl_id = cmc.clcl_id
LEFT JOIN faubh.cmc_excd_expl_cd cmc3
ON cmc3.excd_id = cmc2.cdml_disall_excd
WHERE cmc.grgr_ck = 6693
AND trunc(cmc2.cdml_from_dt) >= 2011-07-01
AND TRUNC(cmc2.cdml_from_dt) <= 2011-08-01
AND cmc2.cdml_cur_sts = 02
AND cmc2.cdml_paid_amt <>0
AND cmc1.mctn_id=850390576'))

 

[PWise]

perhaps missing quote
AND cmc1.mctn_id='850390576'))

 

[eshie003]

I tried that previously and when I modify last line to:
AND cmc1.mctn_id='850390576'))

I get 2 errors:

Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '850390576'.
Msg 105, Level 15, State 1, Line 21
Unclosed quotation mark after the character string '))

 

[drlex]

I think that you just need a table reference after the query.

SELECT * INTO [RDM].[dbo].[#TEST] FROM
(SELECT * FROM OPENQUERY(URAFP4,'SELECT DISTINCT  cmc.clcl_id,
 [...]      AND cmc1.mctn_id=850390576')) [red][b]T1[/b][/red]

soi là, soi carré