February 10, 2006 at 12:19 pm
Hello Friends,
I've a complex query to be returned with a particular result set. I'm joining 3 tables from 2 different databases from the same server (SQL 7).
Based on the join of 2 tables from a single database, I need to get the value from the second database table. I'm able to get through a case statement, but I'm missing the related data for some rows. The related data for only 3 columns is as follows:
BillOfLadingNum OrderKey OrderNumber
NULL 0000132649 0000132649
NULL 0000132650 0000132650
NULL 0000132651 0000132651
NULL 0000132653 0000132653
NULL 0000132654 0000132654
NULL 0000132655 0000132655
NULL 0000132656 0000132656
NULL 0000132657 0000132657
NULL 0000132658 0000132658
NULL 0000132659 0000132659
NULL 0000132660 0000132660
NULL 0000132661 0000132661
NULL 0000132662 0000132662
NULL 0000132760 0000132760
04140510000000001 0000132649 0000132649
04140510000000002 0000132650 0000132650
04140510000000003 0000132651 0000132651
04140510000000005 0000132653 0000132653
04140510000000006 0000132654 0000132654
04140510000000007 0000132655 0000132655
04140510000000008 0000132656 0000132656
04140510000000009 0000132657 0000132657
04140510000000010 0000132658 0000132658
04140510000000011 0000132659 0000132659
04140510000000012 0000132660 0000132660
04140510000000013 0000132661 0000132661
04140510000000014 0000132662 0000132662
The query I'm using is as follows:
SELECT DISTINCT 'BILLOFLADINGNUM' = CASE WHEN WH3.ORDERDETAIL.ORDERKEY IS NOT NULL THEN TRACKING.DBO.ARISTO.BILLOFLADINGNUM END,
WH3.ORDERDETAIL.ORDERKEY,
TRACKING.DBO.ARISTO.EXEORDERNUMBER
FROM TRACKING.DBO.ARISTO INNER JOIN WH3.ORDERDETAIL ON WH3.ORDERDETAIL.ORDERKEY = TRACKING.DBO.ARISTO.EXEORDERNUMBER
INNER JOIN WH3.ORDERS ON WH3.ORDERS.ORDERKEY = WH3.ORDERDETAIL.ORDERKEY
If you can see from the data the Oderkey and OrderNumber are the same and I need to get the relevant BillOfLadingNum replaced for the Null values in that column (highlighted for easy understanding).
Any help would be appreciated.
Thanks in advance.
Lucky
February 10, 2006 at 1:22 pm
Do you have any more details about the table layouts?
--Jeff
February 13, 2006 at 6:54 am
Hello,
as I understand it, you have table Orders (order header, 1 for each order) and OrderDetail (order lines, can be several per order), and table Aristo, which stores info about shipments. Now, it can probably happen that one order is shipped in several parcels (or that only part of the order is shipped and the rest waiting till later date... e.g. goods not available atm).
However, since you are joining the table Aristo on the common OrderKey (ID of order header, as I see it), not on a separate order detail (orderline) and all the joins are INNER, it shouldn't make any difference whether you join to orderline or header ID. It seems that there are rows in table Aristo which have BillOfLadingNum NULL. To be honest, I'm not sure whether this is correct... we have something similar in our DB, but we are joining the shipment codes table (equivalent of your Aristo) to individual orderlines - not to ID of the whole order. Everything depends on your processes, and I don't know anything about that.
Try : SELECT * FROM TRACKING.DBO.ARISTO WHERE BillOfLadingNum IS NULL
(if necessary, limit the output with some restriction in WHERE or by TOP 100 or whatever) ... if you find any such rows, you have to know what to do with them. Is this on purpose, temporary status, or is it error that should be corrected? Or a simple "WHERE BillOfLadingNum IS NOT NULL" will be all you need? That's up to you to decide.
If this didn't help, please post more information about the tables and the data they contain.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply