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
Solved

Query using openquery in a subquery

Posted on 2014-07-30
6
267 Views
Last Modified: 2014-08-05
I have written the following query to obtain certain rows from a table tblShipping. I am pulling two columns (ReceivedDate, ReceivedBoxes) from a different table "tblInven" based on a subquery,


select * from (select a.ShipON dtShip,a.sender,a.product,a.name,a.units,a.uom,a.type,a.[ORDER],
ISNULL((select convert(varchar(20),b.dtrec,101) from tblInven b where a.origid = b.sid and a.lot = b.lot
and a.[order] = b.[order] and a.product = b.product and a.boxnum=b.boxnum),'Not Received') ReceivedDate,
(a.Boxes) ConfirmedBoxes,(a.Shipping)Shipped,
ISNULL((select b.dtRec from tblInven b where a.origid = b.sid and a.lot = b.lot and a.[order] = b.[order]
and a.product = b.product and a.boxnum=b.boxnum),0) ReceivedBoxes,
(a.COST)FOB,a.MOVED,a.origid
from tblshipping a
where convert(varchar(10),ShipOn,101) < (getDate()-2)) q
order by dtship

Now I need to replace tblInven with a table in a different database that I can access using openquery. It has the same columns as tblInven. This is how I would access the new table

Select * from OpenQuery(ServerCDX, 'SELECT * from newINVEN order by dtec')

How can I include the newINVEN table in my query?
0
Comment
Question by:Angel02
  • 3
  • 2
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40231053
simply like this:
select * 
from (select a.ShipON dtShip,a.sender,a.product,a.name,a.units,a.uom,a.type,a.[ORDER]
, ISNULL( (select convert(varchar(20),b.dtrec,101) from ( Select * from OpenQuery(ServerCDX, 'SELECT * from newINVEN ') ) b where a.origid = b.sid and a.lot = b.lot
and a.[order] = b.[order] and a.product = b.product and a.boxnum=b.boxnum),'Not Received') ReceivedDate
, (a.Boxes) ConfirmedBoxes,(a.Shipping)Shipped
, ISNULL((select b.dtRec from ( Select * from OpenQuery(ServerCDX, 'SELECT * from newINVEN ') ) b where a.origid = b.sid and a.lot = b.lot and a.[order] = b.[order]
and a.product = b.product and a.boxnum=b.boxnum),0) ReceivedBoxes
, (a.COST)FOB
,a.MOVED,a.origid
from tblshipping a
where convert(varchar(10),ShipOn,101) < (getDate()-2)) q
order by dtship

Open in new window


but I wonder why you don't do a plain LEFT JOIN with this table...
0
 
LVL 1

Assisted Solution

by:kjulius
kjulius earned 200 total points
ID: 40232309
I sincerely hope that this isn't production code because that is one ugly query! It is common knowledge that if at all possible you should avoid using subselects -- and you have two of those in your main select. A simple rewrite to use left outer joins would make it much quicker and use less resources.

select a.ShipON dtShip,
      a.sender,
      a.product,
      a.name,
      a.units,
      a.uom,
      a.type,
      a.[ORDER],
      ISNULL(convert(varchar(20),b.dtrec,101),'Not Received') ReceivedDate,
      a.Boxes ConfirmedBoxes,
      a.Shipping Shipped,
       ISNULL(b.dtRec,0) ReceivedBoxes,
      a.COST FOB,
      a.MOVED,
      a.origid
from tblshipping a
left outer join tblInven b
            on a.origid = b.sid
            and a.lot = b.lot
            and a.[order] = b.[order]
            and a.product = b.product
            and a.boxnum=b.boxnum
where convert(varchar(10),a.ShipOn,101) < (getDate()-2)
order by dtship

(or something like that)

As for how to use a table from a different server, I suggest you create a synonym that points to the table on the remote server. Or simply point directly to the server in your query by including the servername, i.e.  use the full name format  remoteservername.database.schema.table, for example server2.mydb.dbo.tblNEWInven (though I wouldn't recommend it in general).

IMHO it doesn't really pay off to use OpenQuery in this case. OpenQuery is an excellent choice if you can send a complex query to the remote server to be executed there and use the returned dataset from that query. However this is not what you have here. You simply want to use a single remote table in your query.

So I would advise to create a synonym pointing at the remote table and use the synonym name instead of the original table name or use the long full name.

To use Synonyms is a much more elegant solution. It allows you to move the table to a different server or database at a later stage without having to change the queries that use the remote table. You can simply delete the synonym and create a new one with the same name that points to a different location.
0
 

Author Comment

by:Angel02
ID: 40233435
Well you are right. That is an ugly query. I am trying to but can't remember how I ended up on this! This is fortunately just a one-time query to generate a report. The other table in a different database that I am talking about is a simple DBF file. I think I'll go with openquery for now.

So I modified the query like this.

select * from (select a.ShipON dtShip,a.sender,a.product,a.name,a.units,a.uom,a.type,a.[ORDER],
ISNULL((CASE WHEN convert(varchar(20),b.dtrec,101) IS NULL THEN c.dtrec ELSE convert(varchar(20),b.dtrec,101) END),'Not Received') ReceivedDate,
(a.Boxes) ConfirmedBoxes,(a.Shipping)Shipped,
ISNULL((CASE WHEN (b.RecBoxes) IS NULL THEN c.RecBoxes ELSE b.RecBoxes END),0) ReceivedBoxes,
(a.COST)FOB,a.MOVED,a.origid
from tblshipping a
LEFT JOIN tblINVEN b
on a.origid = b.sid and a.lot = b.lot
and a.[order] = b.[order] and a.product = b.product and a.boxnum=b.boxnum
LEFT JOIN OPENQUERY(SERVERCDX, 'SELECT * FROM NewINVEN') AS c
on a.origid = c.sid and a.lot = c.lot
and a.[order] = c.[order] and a.product = c.product and a.boxnum=c.boxnum
where convert(varchar(10),ShipOn,101) < (getDate()-2)) q
order by dtship

This looks like works good. I am getting the received boxes from NewInven if i don't find then in tblINVEN.

Now the question I have is if I have any boxes in tblINVEN or newINVEN that are not there in tblShipping, will they be displayed in the result? If not, what should I be doing?
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 40233486
if they are not in shipping at all, but you want them in the report, you want to reorder the tables and the joins...

like you first build up the full "catalog" with a full outer join, and LEFT JOIN that to the actual shippings...

around this structure:
from (  SELECT  isnull( b.sid, c.sid ) sid,  ...
             FROM tblINVEN b
        FULL OUTER JOIN OPENQUERY(SERVERCDX, 'SELECT * FROM NewINVEN') AS c
            ON b.sid = c.sid and c.lot = b.lot  ....
  )
LEFT JOIN  tblshipping a
   on ...

Open in new window

0
 

Author Comment

by:Angel02
ID: 40233511
There are also boxes in tblshipping that are not there in tblinven or newinven and I want those also in the report.
So I FULL JOIN  to tblshipping too?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40233518
yes
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

828 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