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

asked 25 Mar '14, 19:19

Tom%20Mangano's gravatar image

Tom Mangano
672242738
accept rate: 8%

1

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

(25 Mar '14, 21:07) Breck Carter

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
(25 Mar '14, 22:31) Tom Mangano
Replies hidden

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

(26 Mar '14, 04:18) Volker Barth

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

(26 Mar '14, 04:31) Martin

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

(26 Mar '14, 10:18) Tom Mangano
Replies hidden

The "link text" link is empty.

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

(26 Mar '14, 12:57) Breck Carter

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

(26 Mar '14, 18:53) Tom Mangano
showing 5 of 7 show all flat view

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, 
permanent link

answered 26 Mar '14, 20:34

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

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" misconception concept...

permanent link

answered 26 Mar '14, 04:26

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

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;
permanent link

answered 26 Mar '14, 08:24

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 26 Mar '14, 08:27

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×5

question asked: 25 Mar '14, 19:19

question was seen: 2,234 times

last updated: 27 Mar '14, 04:15