Solved

how to use Open Query within sub query in sql server

Posted on 2011-03-10
2
1,604 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:karinos57
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 35097950
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
 

Author Comment

by:karinos57
ID: 35098209
thanks for your response but not sure what you have said or did in the query.  can you explain little bit?  thanks
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Parse this column 6 35
Faster way to get row count from a view than select Count(*) from ViewObject? 2 37
SQL Query Syntax Assistance 2 35
SQL Syntax 6 41
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question