Databases
StackOverflow2013
Postsdbo
POT-SQL Query Join on different column depending on case
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POT-SQL Query Join on different column depending on case
    primarykey
    data
    text
    <p>I have a legacy system that has a sales table and a customer table, CMS and CUST respectively. I need to query for the shipped to address based on different criteria. The customer table treats each address as its own customer. So if I have a billing address, then a shipping address, those will both be different CUSTNUM's. The CMS table has columns CUSTNUM and SHIPNUM. If the sales order uses the billing address as the shipping address, SHIPNUM = 0. If those 2 address are different, SHIPNUM = a different customer number than CUSTNUM. I'm trying to write a query that joins CUST to CMS based on the case of SHIPNUM being > 0 or not. My original query just used CUSTNUM, and ignored the SHIPNUM. My new query is syntactically correct and executes, but the row count returned is 2860 vs 3590 for the old query. The old join statement is just the commented out line :ON CMS.CUSTNUM = CUST.CUSTNUM.</p> <pre><code>from KGI_LOTNOS as LOT INNER JOIN CMS ON LOT.ORDERNO = CMS.ORDERNO JOIN CUST ON CUST.CUSTNUM = CASE WHEN CMS.SHIPNUM &gt; 0 THEN CMS.SHIPNUM Else CMS.CUSTNUM END -- ON CMS.CUSTNUM = CUST.CUSTNUM INNER JOIN COUNTRY as C ON CUST.COUNTRY = C.COUNTRY </code></pre> <p>Here is an example from the CMS table;</p> <pre><code>CUSTNUM SHIPNUM ORDERNO 41863 77394 828509 &lt;--Different billing and shipping address 43242 69291 776888 &lt;--Different billing and shipping address 2356 0 765022 &lt;--Same billing and shipping address </code></pre> <p>Any thoughts on how to make this work?</p> <p>PS Here is the original query in its entirety.</p> <pre><code>select CUST.CUSTNUM as Customer, CMS.CUSTNUM, CMS.SHIPNUM, CUST.CTYPE, CMS.ORDERNO, CMS.ODR_DATE, LTRIM(RTRIM(CUST.FIRSTNAME)) as First, LTRIM(RTRIM(CUST.LASTNAME)) as Last, LTRIM(RTRIM(CUST.COMPANY)) as Company, LTRIM(RTRIM(CUST.PHONE)) as Phone, LTRIM(RTRIM(CUST.EMAIL)) as Email, LTRIM(RTRIM(CUST.ADDR)) as ADDR1, LTRIM(RTRIM(CUST.ADDR2)) as ADDR2, LTRIM(RTRIM(CUST.ADDR3)) as ADDR3, LTRIM(RTRIM(CUST.CITY)) as City, LTRIM(RTRIM(CUST.State)) as State, LTRIM(RTRIM(CUST.ZIPCODE)) as Zip, LTRIM(RTRIM(C.NAME)) as Country, LOT.ITEMNO, LOT.LOTNO, COUNT(LOT.ITEMNO) as Quantity from KGI_LOTNOS as LOT INNER JOIN CMS ON LOT.ORDERNO = CMS.ORDERNO LEFT JOIN CUST ON CMS.CUSTNUM = CUST.CUSTNUM INNER JOIN COUNTRY as C ON CUST.COUNTRY = C.COUNTRY where ( CUST.CTYPE IN ('P','W','Z') ) AND ( LOT.LOTNO IN ('1000001','20001','300001') ) GROUP BY CMS.ORDERNO, CUST.CUSTNUM, CMS.CUSTNUM, CMS.SHIPNUM, CUST.CTYPE, CUST.FIRSTNAME, CMS.ODR_DATE, CUST.LASTNAME, CUST.COMPANY, CUST.PHONE, CUST.EMAIL, CUST.ADDR, CUST.ADDR2, CUST.ADDR3, LOT.ITEMNO, CUST.CITY, CUST.STATE, CUST.ZIPCODE, C.NAME, LOT.LOTNO ORDER BY Customer, CMS.ORDERNO, LOT.ITEMNO, LOT.LOTNO </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMLindsay
    1. USMLindsay
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COThinking out of the box as I'm still not sure what's wrong with your query, but do you have any NULL's in SHIPNUM or negative numbers?
      singulars
      1. POT-SQL Query Join on different column depending on case
      1. USshadowjfaith
    2. COHard to see from what you've posted but I'd union two joins, one for each if I needed something like that.
      singulars
      1. POT-SQL Query Join on different column depending on case
      1. USTony Hopkinson
    3. COI did check for Null's, there weren't any.
      singulars
      1. POT-SQL Query Join on different column depending on case
      1. USMLindsay
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload