Solved

Query using openquery in a subquery

Posted on 2014-07-30
6
273 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
[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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

740 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