Solved

Query using openquery in a subquery

Posted on 2014-07-30
6
283 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
Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

 
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

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…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

691 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