select c1, c2, c3, when recon_status = '', i'm not getting an N, i'm getting '' ASA 11.0.1.2960 |
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, 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.
(26 Mar '14, 21:17)
Tom Mangano
|
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" |
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; Why would you think blank-padding would make a difference here?
(26 Mar '14, 08:34)
Volker Barth
Replies hidden
Well, I didn't... but I never think there's going to be bugs either :)
(26 Mar '14, 12:53)
Breck Carter
|
Please copy-and-paste the actual code... which probably contains "recon_status" instead of "recon-status" :)
This is the actual code (still haven't figured out how to make it "pretty" )
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...)
11.0.1.2960 is rather old, try the latest EBF, in current SQLA the result for me is as expected
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
The "link text" link is empty.
What we want is proof of the statement " i'm getting '' "... how are you testing the code?
i hope the file attaches this time. it's an rtf with screen sholink textts.