Using Case within Select

[carmelo]

Hello,
I'm working on a DB on which I can run only SELECT commands, so I'd
like to modify this UPDATE command, which makes use of CASE:

update Table
set field2 = case
when (SUBSTR(field,1 ,2) in ('01','02','05' )) then 1
when (SUBSTR(field,1 ,2) in ('15','16')) then 2
when (SUBSTR(field,1 ,2) in ('18','19','22' ,'36') or SUBSTR(field,1, 3)
in ('174','175','1 77','244','245' ,'297','323','3 34','335')) then 3
else 0
end;

I've modified it into this:
select *,
case
when (SUBSTR(field,1 ,2) in ('01','02','05' )) then 1
when (SUBSTR(field,1 ,2) in ('15','16')) then 2
when (SUBSTR(field,1 ,2) in ('18','19','22' ,'36') or SUBSTR(field,1, 3)
in ('174','175','1 77','244','245' ,'297','323','3 34','335')) then 3
else 0
end
as field2
from Table

But it doesn't work properly, because it always returns 0.
So I thought to change it into this:
select *,
case SUBSTR(field,1, 2)
when '01' then '1'
when '02' then '1'
when '05' then '1'
when '15' then '2'
when '16' then '2'
when '18' then '3'
when '19' then '3'
when '22' then '3'
when '36' then '3'
else 0
end
as field2
from Table

But, in this way, I only check 2 chars values, even if I need to check
3 chars values too...
How can I do? Have you got any idea?

Thank you very much in advance
Cheers
Carmelo

[Plamen Ratchev]

Re: Using Case within Select

Please do not multi-post, replied in .programming, copy/paste here:

There is no SUBSTR function in SQL Server, the correct function is
SUBSTRING. The CASE expression is correct and should return the values if
your data has the expected strings.

Try this for a test:

CREATE TABLE Foo (
keycol INT PRIMARY KEY,
datacol VARCHAR(10));

INSERT INTO Foo VALUES(1, '0123456789');
INSERT INTO Foo VALUES(2, '1523456789');
INSERT INTO Foo VALUES(3, '2234567890');
INSERT INTO Foo VALUES(4, '2454567890');
INSERT INTO Foo VALUES(5, '9994567890');

SELECT keycol,
CASE
WHEN SUBSTRING(datac ol, 1, 2) IN ('01', '02', '05') THEN 1
WHEN SUBSTRING(datac ol, 1, 2) IN ('15', '16') THEN 2
WHEN SUBSTRING(datac ol, 1, 2) IN ('18', '19', '22', '36')
OR SUBSTRING(datac ol, 1, 3) IN ('174', '175', '177', '244',
'245', '297', '323', '334', '335')
THEN 3
ELSE 0
END AS col2
FROM Foo;

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[carmelo]

Re: Using Case within Select

On 7 Giu, 15:37, "Plamen Ratchev" <Pla...@SQLStud io.comwrote:

Please do not multi-post, replied in .programming, copy/paste here:
>
There is no SUBSTR function in SQL Server, the correct function is
SUBSTRING. The CASE expression is correct and should return the values if
your data has the expected strings.
>
Try this for a test:
>
CREATE TABLE Foo (
keycol INT PRIMARY KEY,
datacol VARCHAR(10));
>
INSERT INTO Foo VALUES(1, '0123456789');
INSERT INTO Foo VALUES(2, '1523456789');
INSERT INTO Foo VALUES(3, '2234567890');
INSERT INTO Foo VALUES(4, '2454567890');
INSERT INTO Foo VALUES(5, '9994567890');
>
SELECT keycol,
CASE
WHEN SUBSTRING(datac ol, 1, 2) IN ('01', '02', '05') THEN 1
WHEN SUBSTRING(datac ol, 1, 2) IN ('15', '16') THEN 2
WHEN SUBSTRING(datac ol, 1, 2) IN ('18', '19', '22', '36')
OR SUBSTRING(datac ol, 1, 3) IN ('174', '175', '177', '244',
'245', '297', '323', '334', '335')
THEN 3
ELSE 0
END AS col2
FROM Foo;
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.c om

Thank you Plamen, it works :-)