SQL CASE returning wrong values

user3394606

I wrote the following query but the CASE statements are returning incorrect values. When soaddr has a value instead of returning the value from soaddr it will return the incorrect value from arcust. But if I change the else statment to pull the value from soaddr then it will return a NULL value. What am I doing wrong?

SELECT DISTINCT a.custno, b.company,
    CASE WHEN c.address1 = NULL THEN b.address1
    ELSE b.address1
    END as address,
    CASE WHEN c.city = NULL THEN b.city
    ELSE b.city
    END as city,
    CASE WHEN c.addrstate = NULL THEN b.addrstate
    ELSE b.addrstate
    END as addrstate,
    CASE WHEN c.zip = NULL THEN b.zip
    ELSE b.zip
    END as zip,
    invno, descrip, qtyshp, price, extprice, b.tax, invdte 
FROM artran a
LEFT JOIN arcust b ON a.custno = b.custno
LEFT JOIN soaddr c ON a.custno = c.custno
Matt

The reason it is returning the wrong value is that your case statement is setup to always return the arcust value because all parts of the THEN & ELSE reference the b table alias which points to arcust. Between that and comparing the null incorrectly as @ServerSentinel appropriately points out you are not getting your desired results. Modify your query as follows to point to the c table alias and compare the null as IS NULL

SELECT DISTINCT a.custno, b.company,
    CASE WHEN c.address1 IS NULL THEN b.address1
    ELSE c.address1
    END as address,
    CASE WHEN c.city IS NULL THEN b.city
    ELSE c.city
    END as city,
    CASE WHEN c.addrstate IS NULL THEN b.addrstate
    ELSE c.addrstate
    END as addrstate,
    CASE WHEN c.zip IS NULL THEN b.zip
    ELSE c.zip
    END as zip,
    invno, descrip, qtyshp, price, extprice, b.tax, invdte 
FROM artran a
LEFT JOIN arcust b ON a.custno = b.custno
LEFT JOIN soaddr c ON a.custno = c.custno

Next learning COALESCE() is a huge help to you here because it basically writes the case statement for you and returns the first non null value. So you could simply write:

SELECT DISTINCT a.custno, b.company,
    COALESCE(c.address1,b.address1) as address,
    COALESCE(c.city,b.city) as city,
    COALESCE(c.addrstate,b.addrstate) as addrstate,
    COALESCE(c.zip,b.zip) as zip,
    invno, descrip, qtyshp, price, extprice, b.tax, invdte 
FROM artran a
LEFT JOIN arcust b ON a.custno = b.custno
LEFT JOIN soaddr c ON a.custno = c.custno

Which will give you the soaddr column if it is not null and if it is then you will get the arcust address.

However because address data should probably be kept together meaning you should select and entire address from 1 table instead of potentially merging you should stick with your case statement but always test 1 field to determine if there is a soaddr that field should be the unique id for that table if one exists if not use another column such as Address1.

SELECT DISTINCT a.custno, b.company,
    CASE WHEN c.UniqueId IS NULL THEN b.address1
    ELSE c.address1
    END as address,
    CASE WHEN c.UniqueId IS NULL THEN b.city
    ELSE c.city
    END as city,
    CASE WHEN c.UniqueId IS NULL THEN b.addrstate
    ELSE c.addrstate
    END as addrstate,
    CASE WHEN c.UniqueId IS NULL THEN b.zip
    ELSE c.zip
    END as zip,
    invno, descrip, qtyshp, price, extprice, b.tax, invdte 
FROM artran a
LEFT JOIN arcust b ON a.custno = b.custno
LEFT JOIN soaddr c ON a.custno = c.custno

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

TOP Ranking

  1. 1

    Failed to listen on localhost:8000 (reason: Cannot assign requested address)

  2. 2

    Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

  3. 3

    How to import an asset in swift using Bundle.main.path() in a react-native native module

  4. 4

    pump.io port in URL

  5. 5

    Compiler error CS0246 (type or namespace not found) on using Ninject in ASP.NET vNext

  6. 6

    BigQuery - concatenate ignoring NULL

  7. 7

    ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

  8. 8

    ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements

  9. 9

    Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

  10. 10

    How to remove the extra space from right in a webview?

  11. 11

    java.lang.NullPointerException: Cannot read the array length because "<local3>" is null

  12. 12

    Jquery different data trapped from direct mousedown event and simulation via $(this).trigger('mousedown');

  13. 13

    flutter: dropdown item programmatically unselect problem

  14. 14

    How to use merge windows unallocated space into Ubuntu using GParted?

  15. 15

    Change dd-mm-yyyy date format of dataframe date column to yyyy-mm-dd

  16. 16

    Nuget add packages gives access denied errors

  17. 17

    Svchost high CPU from Microsoft.BingWeather app errors

  18. 18

    Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

  19. 19

    12.04.3--- Dconf Editor won't show com>canonical>unity option

  20. 20

    Any way to remove trailing whitespace *FOR EDITED* lines in Eclipse [for Java]?

  21. 21

    maven-jaxb2-plugin cannot generate classes due to two declarations cause a collision in ObjectFactory class

HotTag

Archive