|
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" )
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_numWhen 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.