Solved

Query using openquery in a subquery

Posted on 2014-07-30
6
255 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 142

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

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 142

Expert Comment

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

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server - SQL field is defined as text 3 27
Need to update TableA to TableB 6 34
TSQL DateADD update Question 4 28
Sql Join Problem 2 27
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.‚Äč
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now