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','177','244','245','297','323','334',' 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','177','244','245','297','323','334',' 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]

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(datacol, 1, 2) IN ('01', '02', '05') THEN 1
WHEN SUBSTRING(datacol, 1, 2) IN ('15', '16') THEN 2
WHEN SUBSTRING(datacol, 1, 2) IN ('18', '19', '22', '36')
OR SUBSTRING(datacol, 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]

On 7 Giu, 15:37, "Plamen Ratchev" <[e-mail]:

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(datacol, 1, 2) IN ('01', '02', '05') THEN 1
WHEN SUBSTRING(datacol, 1, 2) IN ('15', '16') THEN 2
WHEN SUBSTRING(datacol, 1, 2) IN ('18', '19', '22', '36')
OR SUBSTRING(datacol, 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.com

Thank you Plamen, it works :-)