?
Solved

Query using openquery in a subquery

Posted on 2014-07-30
6
Medium Priority
?
295 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 800 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1200 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.
Suggested Courses

800 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