CODEWITHSUNDEEP
sqldatabaseoracle-database
User87797
User87797

Reputation: 53

SQL to display a column value as a set value or null depending on status

below is the code I have written - what I want is when the status is equal to 'N' to display 'Export to WMS' in the column 'Export to WMS'.

If it has any other value than 'N' in the status, I want the column to still appear because some results will be at status 'N' but for those that aren't I want the value of that column to be blank.

select 
     m.display_order_number, m.record_create_date, l.lookup_description,m.wh_id, 
     m.client_code,m.order_type,m.order_date,m.UIN, m.ship_to_name,m.carrier,
     (select DISTINCT 'Export to WMS' 
     from 
     t_3pl_order_master 
     where 
     status = 'N') AS "Export to WMS"
from 
     t_3pl_order_master m
INNER JOIN 
     t_3pl_lookup l on m.status = l.lookup_value AND l.lookup_type = 'Order Status';

Results I get are: enter image description here

Where you can clearly see when the status is 'W' it still displays 'Export to WMS' but ideally I would want that to be blank and those with status 'N' to display 'Export to WMS'.

Hope you can help!

Upvotes: 0

Views: 63

Answers (4)

shawnt00
shawnt00

Reputation: 17935

I would highly recommend using the case expressions already described in other answers. But to explain why your attempt didn't work I'll give you a fix of sorts.

select 
     m.display_order_number, m.record_create_date, l.lookup_description,m.wh_id, 
     m.client_code,m.order_type,m.order_date,m.UIN, m.ship_to_name,m.carrier,
     (
     select distinct 'Export to WMS' 
     from t_3pl_order_master m2
     where m.status = 'N' /* <<--- Difference is here */
     ) AS "Export to WMS"
from 
     t_3pl_order_master m inner join
     t_3pl_lookup l on m.status = l.lookup_value AND l.lookup_type = 'Order Status';

The subquery as you wrote it will always return the single value "Export to WMS" as long as there is at least one row in the entire table where status = 'N'. What you needed to do was correlate (connect) the subquery to the outer query by referring to m.status rather than just status. Note that it would then evaluate to a null as "blank". Use coalesce() to turn that into the empty string if that's actually necessary.

Now the truth is that this is a very roundabout way of getting what you wanted although clever in the sense that you used the syntax you already knew to piece something together that was pretty close.

Upvotes: 0

JBrooks
JBrooks

Reputation: 10013

Need the ELSE in there to make it blank. Otherwise it will be null.

CASE WHEN status = 'N' THEN 'Export to WMS' ELSE ' ' END  AS "Export to WMS"

Upvotes: 1

codeMonger123
codeMonger123

Reputation: 505

If I read your post correctly just use the case statement to handle this.

Upvotes: 0

krokodilko
krokodilko

Reputation: 36127

Use a case expression:

CASE WHEN status = 'N' THEN 'Export to WMS' END

in this way:

select m.display_order_number, 
m.record_create_date, 
l.lookup_description, 
m.wh_id, 
m.client_code,
m.order_type,  
m.order_date, 
m.UIN, 
m.ship_to_name, 
m.carrier,
CASE WHEN status = 'N' THEN 'Export to WMS' END  AS "Export to WMS"
from t_3pl_order_master m
INNER JOIN t_3pl_lookup l on m.status = l.lookup_value AND l.lookup_type = 'Order Status';

see this link for details: https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm

Upvotes: 2

Related Questions