Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

CASE statement and if ... then ... else

3 posts in General Discussion Last posting was on 2012-04-06 16:27:59.0Z
Ericrad Posted on 2012-04-05 18:22:27.0Z
Sender: 4ea0.4f7de0b7.1804289383@sybase.com
From: ericrad
Newsgroups: sybase.public.ase.general
Subject: CASE statement and if ... then ... else
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f7de2e3.4f13.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 5 Apr 2012 11:22:27 -0700
X-Trace: forums-1-dub 1333650147 172.20.134.41 (5 Apr 2012 11:22:27 -0700)
X-Original-Trace: 5 Apr 2012 11:22:27 -0700, 172.20.134.41
Lines: 28
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31011
Article PK: 73900

Hi,
I need to do the following in a select query

select col1, col2,
case
when my business_unit = 'SH' I need to find the value of
the customers country_code and then set this column to
either 'US' or 'NC'

else 'NC' -- this else would be if business_unit != 'SH'
END) as remit_id,
col8, col10 ....

In a simple test it works

(case
when t3.business_unit = 'SH' then 'USA'
else 'NC'
end) as remit_id,

The error is in trying to write a select with an
if/then/else to set the final value.

Can you provide some guidance on this query? Can I write a
complex 'THEN' for the when case?

Thanks
eric


Paul Posted on 2012-04-06 14:14:05.0Z
Sender: 7c28.4f7ef12c.1804289383@sybase.com
From: Paul
Newsgroups: sybase.public.ase.general
Subject: Re: CASE statement and if ... then ... else
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f7efa2d.7d3e.1681692777@sybase.com>
References: <4f7de2e3.4f13.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 6 Apr 2012 07:14:05 -0700
X-Trace: forums-1-dub 1333721645 172.20.134.41 (6 Apr 2012 07:14:05 -0700)
X-Original-Trace: 6 Apr 2012 07:14:05 -0700, 172.20.134.41
Lines: 25
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31013
Article PK: 73901

You do not say how you are going to find the value of a
customer's country code. Running a SELECT?

This is what your query looks like at the moment:

SELECT col1,
col2,
CASE t3.business_unit
WHEN 'SH' THEN 'USA'
ELSE 'NC'
END AS remit_id,
col8,
col10
FROM <table> t3

Following THEN instead of 'USA', you can put a SELECT (it
may return no more than one row):

THEN (SELECT <customer's country code> FROM <customers>
WHERE <customer's business unit> = t3.business_unit)

Alternatively, you can add another CASE if this is enough:

THEN CASE WHEN <condition 1> THEN <value 1> WHEN <condition
2> THEN <value 2> ELSE <need ELSE?> END


Ericrad Posted on 2012-04-06 16:27:59.0Z
Sender: 7fb1.4f7f0cfa.1804289383@sybase.com
From: ericrad
Newsgroups: sybase.public.ase.general
Subject: Re: CASE statement and if ... then ... else
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f7f198f.266.1681692777@sybase.com>
References: <4f7efa2d.7d3e.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 6 Apr 2012 09:27:59 -0700
X-Trace: forums-1-dub 1333729679 172.20.134.41 (6 Apr 2012 09:27:59 -0700)
X-Original-Trace: 6 Apr 2012 09:27:59 -0700, 172.20.134.41
Lines: 36
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31014
Article PK: 73902

Thank you. Yes, I am going to find a single record on the
select for country code. I think my problem was that I
didn't put the select into () and couldn't get the command
to parse/run.

I corrected my code to match your example and it works
perfectly.

Thank you for your prompt and helpful reply,
Eric

> You do not say how you are going to find the value of a
> customer's country code. Running a SELECT?
>
> This is what your query looks like at the moment:
>
> SELECT col1,
> col2,
> CASE t3.business_unit
> WHEN 'SH' THEN 'USA'
> ELSE 'NC'
> END AS remit_id,
> col8,
> col10
> FROM <table> t3
>
> Following THEN instead of 'USA', you can put a SELECT (it
> may return no more than one row):
>
> THEN (SELECT <customer's country code> FROM <customers>
> WHERE <customer's business unit> = t3.business_unit)
>
> Alternatively, you can add another CASE if this is enough:
>
> THEN CASE WHEN <condition 1> THEN <value 1> WHEN
> <condition 2> THEN <value 2> ELSE <need ELSE?> END