how to use Open Query within sub query in sql server

Hi,
i am using sql server 2005 and using sub query within an Open Query method to pull some data from other databases but getting an error like this "Incorrect syntax near the keyword 'select'."
i suspect the error has to do where i put the prenthesis in between the open query and the sub query.  here is my code.  Appreciate any kind of help.
select
         parent_id,
         parent_desc,
         contract_id,
         pg.group_desc as Tier_Desc,
         ca.actdate as mdf_date,
         ca.stat,
         volume


from

select * into Dashboard.dbo.test_temp
from openquery(MyDb,'

(SELECT  l.PROV_id,
         l.prov_name,
         tp.parent_id,
         tp.parent_desc,
         lob.lob_id,
         lob.lob_desc,
         l.provider_category_desc,

         max(s.provider_key) provider_key,
         max(s.s_contract_key) s_contract_key,

SUM(Case when S.month_key in (2010307, 2010308, 2010309, 2010410) then S.VOLUME_PAID else 0 end) AS volume

FROM    " bunch of tables here but not icluded in here"

AND      S.MONTH_KEY >= 2010307 and S.MONTH_KEY <= 2011206
GROUP BY 1,2,3,4,5,6) f left outer join

(select lca.provider_key, lca.s_contract_key, lca.price_group_key, lca.activation_date, lca.status
from  osr_price_act_content lca
where lca.provider_key <> 0
group by lca.provider_key, lca.s_contract_key, lca.price_group_key, lca.activation_date, lca.status) ca
                                       on f.provider_key   = ca.provider_key
                                       and f.s_contract_key      = ca.s_contract_key     left outer join
         lookup_price_group pg         on pg.price_group_key    = ca.price_group_key ')

Open in new window

karinos57Asked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
so either copy the openquery data into your table and then use the table within this query

or just reference the openquery...
select
         parent_id,
         parent_desc,
         contract_id,
         pg.group_desc as Tier_Desc,
         ca.actdate as mdf_date,
         ca.stat,
         volume


from 

--select * into Dashboard.dbo.test_temp from
 openquery(MyDb,'

(SELECT  l.PROV_id,
         l.prov_name,
         tp.parent_id,
         tp.parent_desc,
         lob.lob_id,
         lob.lob_desc,
         l.provider_category_desc,

         max(s.provider_key) provider_key,
         max(s.s_contract_key) s_contract_key,

SUM(Case when S.month_key in (2010307, 2010308, 2010309, 2010410) then S.VOLUME_PAID else 0 end) AS volume

FROM    " bunch of tables here but not icluded in here"

AND      S.MONTH_KEY >= 2010307 and S.MONTH_KEY <= 2011206
GROUP BY 1,2,3,4,5,6) f left outer join

(select lca.provider_key, lca.s_contract_key, lca.price_group_key, lca.activation_date, lca.status
from  osr_price_act_content lca
where lca.provider_key <> 0
group by lca.provider_key, lca.s_contract_key, lca.price_group_key, lca.activation_date, lca.status) ca
                                       on f.provider_key   = ca.provider_key
                                       and f.s_contract_key      = ca.s_contract_key     left outer join
         lookup_price_group pg         on pg.price_group_key    = ca.price_group_key ') as X

Open in new window

0
 
karinos57Author Commented:
thanks for your response but not sure what you have said or did in the query.  can you explain little bit?  thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.