case expression

[t1950]

select c1, c2, c3,
( CASE when recon-status = '' then 'N'
when recon-status is null then 'NULL'
else recon-status END )
from ...

when recon_status = '', i'm not getting an N, i'm getting ''

ASA 11.0.1.2960

[Breck_Carter]

Please copy-and-paste the actual code... which probably contains "recon_status" instead of "recon-status" 🙂

[t1950]

This is the actual code (still haven't figured out how to make it "pretty" )

SELECT 0 seq_num, ap_check.ck_seq_num,  payment_type,  check_number,  stub_page_ct, check_date,   
        ck_source, ap_check.ck_batch, check_amount, check_discount,  pay_to,   
        vendor_id, 
( case when recon_status = '' then 'N'  
when recon_status is null then 'NULL'  
else recon_status end ) recon_status,  
recon_amount, 0.01 recon_deposit_amount, ck_status, invoice_count,   
        check_format, stub_page, ck_stub_key, recon_op_id, recon_date, current_balance, 0.001 statement_balance 
    FROM ap_check, ap_bank_accts 
   WHERE ck_status in ('O','X') 
    AND  ap_check.ck_seq_num = ap_bank_accts.ck_seq_num 
    and  ap_check.ck_seq_num = :ck_seq_num

[VolkerBarth]

When you feel uncomfortable with formatting comments, use an answer instead (with its edit toolbar) and turn the answer into a comment later (that's what I've done here with your comment...)

[MCMartin]

11.0.1.2960 is rather old, try the latest EBF, in current SQLA the result for me is as expected

[t1950]

i've attached a file for your review.

i must have a typo because i'm not getting what i expect.
both sql statements (11 and 16) were run from iSQL.

please advise

link text

[Breck_Carter]

The "link text" link is empty.

What we want is proof of the statement " i'm getting '' "... how are you testing the code?

[t1950]

i hope the file attaches this time. it's an rtf with screen sholink textts.

[Breck_Carter · Accepted solution]

The question remains, how do you know that recon_status contains ''? Maybe it contains ' ' or ' ' (one or two or more spaces)... you can't tell from the screenshots.

Maybe you need TRIM...

( case when TRIM ( recon_status ) = '' then 'N'  
when recon_status is null then 'NULL'  
else recon_status end ) recon_status, 

[t1950 · Accepted solution]

thank you Breck

my bad, it is a single space. i haven't had a space in a char column since i don't know when. it's a throw back to very old, old programming. as soon as i put '>' + recon_status + '<' in the select, the space showed up.