select c1, c2, c3, when recon_status = '', i'm not getting an N, i'm getting '' ASA 11.0.1.2960 asked 25 Mar '14, 19:19 Tom Mangano |
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, answered 26 Mar '14, 20:34 Breck Carter 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" answered 26 Mar '14, 04:26 Volker Barth |
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; answered 26 Mar '14, 08:24 Breck Carter 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.