cancel
Showing results for 
Search instead for 
Did you mean: 

case expression

t1950
Participant
2,579

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
Participant

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

t1950
Participant
0 Kudos

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
Contributor
0 Kudos

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
Participant
0 Kudos

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

t1950
Participant
0 Kudos

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
Participant
0 Kudos

The "link text" link is empty.

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

t1950
Participant
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Breck_Carter
Participant

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
Participant
0 Kudos

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.

Answers (2)

Answers (2)

Breck_Carter
Participant
0 Kudos

Check your work. It returns 'N' for the same version of SQL Anywhere, regardless of whether dbinit -b was specified or not...

begin
declare recon_status CHAR ( 10 );
set recon_status = '';
select @@VERSION, case when recon_status = '' then 'N'  
when recon_status is null then 'NULL'  
else recon_status end as recon_status;
end;

-- dbinit with -b

@@VERSION,recon_status
'11.0.1.2960','N'

-- dbinit without -b

@@VERSION,recon_status
'11.0.1.2960','N'

This also works as expected...

begin
select @@VERSION, case when recon_status = '' then 'N'  
when recon_status is null then 'NULL'  
else recon_status end as recon_status
FROM ( SELECT '' AS recon_status ) AS t;
end;
VolkerBarth
Contributor
0 Kudos

Why would you think blank-padding would make a difference here?

Breck_Carter
Participant
0 Kudos

Well, I didn't... but I never think there's going to be bugs either 🙂

VolkerBarth
Contributor
0 Kudos

IMHO, a test on an empty string does work as expected with SA 12.0.1:

begin
   declare recon_status varchar;
   set recon_status  = '';
   select case when recon_status = '' then 'N'  
      when recon_status is null then 'NULL'  
     else recon_status end as recon_status;
end;

returns 'N'. And it would be very surprising if it did not, well, at least as long as SQL Anywhere won't follow Oracle's "treat an empty string as null" misconception concept...