Prev: Question about index usage in a query
Next: 1 query; 2 different actual execution plan
From: NetNewbie on 3 Dec 2009 15:39
Hi, I have a table(T1) which has 7 columns out of which 4 columns are of
char(1) data which either have Y or N.

Now the task is I need to update a column in a different table based on
foll. conditions.

treating each of the 4 columns C1, C2, C3, c4 as A, B, C, D in that order if
the data in the 4 columns is
- YNNN, then return A
- YNYN then return AC
- YNNY then return AD
- YYYY then return AD (max and min) A being the max and D beign the min.
- YYYN then return AC (max and min)

I hope I made my question clear. What is the best way to achieve this in
T-SQL if it can be without writing a long series of CASE statements

The data in T! has been loaded from a flatfile. I can re-load these 4 single
char columns into 1 column of char(4) if that makes the task easy. To start
with i loaded into 4 columns.

Thank you for your time
From: NetNewbie on 3 Dec 2009 16:23
I don't know if this is the best solution but I was able to get what I need
as follows

select * ,
case when len(cSUM) = 1 then cSUM
when len(cSUM) = 2 then cSUM
when len(cSUM) = 3 then substring(cSUM,1,1)+substring(cSUM,3,1)
when len(cSUM) = 4 then substring(cSUM,1,1)+substring(cSUM,4,1) end cSUM1
from (
select
case when c1 = 'y' then 'A' else '' end c1
, case when c2 = 'y' then 'B' else '' end c2
, case when c3 = 'y' then 'C' else '' end c3
, case when c4 = 'y' then 'D' else '' end c4
,case when c1 = 'y' then 'A' else '' end +
case when c2 = 'y' then 'B' else '' end +
case when c3 = 'y' then 'C' else '' end +
case when c4 = 'y' then 'D' else '' end CSUM
FROM AdtJuvVSAMFiles
where c1 <>'N' or c2 <>'N' or c3 <>'N' or c4 <>'N'
) t


"NetNewbie" wrote:

> Hi, I have a table(T1) which has 7 columns out of which 4 columns are of
> char(1) data which either have Y or N.
>
> Now the task is I need to update a column in a different table based on
> foll. conditions.
>
> treating each of the 4 columns C1, C2, C3, c4 as A, B, C, D in that order if
> the data in the 4 columns is
> - YNNN, then return A
> - YNYN then return AC
> - YNNY then return AD
> - YYYY then return AD (max and min) A being the max and D beign the min.
> - YYYN then return AC (max and min)
>
> I hope I made my question clear. What is the best way to achieve this in
> T-SQL if it can be without writing a long series of CASE statements
>
> The data in T! has been loaded from a flatfile. I can re-load these 4 single
> char columns into 1 column of char(4) if that makes the task easy. To start
> with i loaded into 4 columns.
>
> Thank you for your time
From: Plamen Ratchev on 3 Dec 2009 17:08
Here are two solutions. The first one is based on string manipulation and will be more efficient. The second uses
unpivoting technique.

CREATE TABLE Foo (
keycol INT NOT NULL PRIMARY KEY,
col1 CHAR(1) NOT NULL,
col2 CHAR(1) NOT NULL,
col3 CHAR(1) NOT NULL,
col4 CHAR(1) NOT NULL);

INSERT INTO Foo VALUES(1, 'Y', 'N', 'N', 'N');
INSERT INTO Foo VALUES(2, 'Y', 'N', 'Y', 'N');
INSERT INTO Foo VALUES(3, 'Y', 'N', 'N', 'Y');
INSERT INTO Foo VALUES(4, 'Y', 'Y', 'Y', 'Y');
INSERT INTO Foo VALUES(5, 'Y', 'Y', 'Y', 'N');

SELECT keycol, CASE WHEN LEFT(val, 1) = RIGHT(val, 1)
THEN LEFT(val, 1)
ELSE LEFT(val, 1) + RIGHT(val, 1)
END AS result
FROM (
SELECT keycol,
REPLACE(
COALESCE(NULLIF(col1, 'Y'), 'A') +
COALESCE(NULLIF(col2, 'Y'), 'B') +
COALESCE(NULLIF(col3, 'Y'), 'C') +
COALESCE(NULLIF(col4, 'Y'), 'D'), 'N', '') AS val
FROM Foo) AS T;

SELECT keycol, CASE WHEN MIN(val) = MAX(val)
THEN MIN(val)
ELSE MIN(val) + MAX(val)
END AS result
FROM (
SELECT keycol,
CASE code WHEN 'A' THEN COALESCE(NULLIF(col1, 'Y'), code)
WHEN 'B' THEN COALESCE(NULLIF(col2, 'Y'), code)
WHEN 'C' THEN COALESCE(NULLIF(col3, 'Y'), code)
WHEN 'D' THEN COALESCE(NULLIF(col4, 'Y'), code)
END AS val
FROM Foo AS F
CROSS JOIN (SELECT 'A' UNION
SELECT 'B' UNION
SELECT 'C' UNION
SELECT 'D') AS T(code)) AS T
WHERE val <> 'N'
GROUP BY keycol;

/*

keycol result
----------- ------
1 A
2 AC
3 AD
4 AD
5 AC

*/

--
Plamen Ratchev
http://www.SQLStudio.com
From: Jeroen Mostert on 3 Dec 2009 17:59
NetNewbie wrote:
> Hi, I have a table(T1) which has 7 columns out of which 4 columns are of
> char(1) data which either have Y or N.
>
> Now the task is I need to update a column in a different table based on
> foll. conditions.
>
> treating each of the 4 columns C1, C2, C3, c4 as A, B, C, D in that order if
> the data in the 4 columns is
> - YNNN, then return A
> - YNYN then return AC
> - YNNY then return AD
> - YYYY then return AD (max and min) A being the max and D beign the min.
> - YYYN then return AC (max and min)
>
> I hope I made my question clear. What is the best way to achieve this in
> T-SQL if it can be without writing a long series of CASE statements
>
This works, but it's cheating slightly for the sake of avoiding any sort of
conditional completely. I don't actually suggest you use this.

LEFT(
SUBSTRING('ABCD', CHARINDEX('Y', C1 + C2 + C3 + C4), 1) +
SUBSTRING('DCBA', CHARINDEX('Y', C4 + C3 + C2 + C1), 1),
2 - (CHARINDEX('Y', C1 + C2 + C3 + C4) + CHARINDEX('Y', C4 + C3 + C2 +
C1)) / 5
)

Too clever by half. Plamen's suggestions are more sensible.

--
J.
From: NetNewbie on 4 Dec 2009 10:19
Hello Plamen,

Your solutions are very interesting. I like them. the 1st solution was easy
to follow and is much cleaner to look at than mine. I did know of the left
and right functions in T-sql and NullIf I have seen that a long time ago, but
did not remember about it

the 2nd solution, can you explain a little bit. The second part of the query
where you specify "AS T(code)" is that an alias or is it supposed to be a
parameter?

I haven't used the pivot/unpivot functions so far though i know it is
available.

Thanks,

"Plamen Ratchev" wrote:

> Here are two solutions. The first one is based on string manipulation and will be more efficient. The second uses
> unpivoting technique.
>
> CREATE TABLE Foo (
> keycol INT NOT NULL PRIMARY KEY,
> col1 CHAR(1) NOT NULL,
> col2 CHAR(1) NOT NULL,
> col3 CHAR(1) NOT NULL,
> col4 CHAR(1) NOT NULL);
>
> INSERT INTO Foo VALUES(1, 'Y', 'N', 'N', 'N');
> INSERT INTO Foo VALUES(2, 'Y', 'N', 'Y', 'N');
> INSERT INTO Foo VALUES(3, 'Y', 'N', 'N', 'Y');
> INSERT INTO Foo VALUES(4, 'Y', 'Y', 'Y', 'Y');
> INSERT INTO Foo VALUES(5, 'Y', 'Y', 'Y', 'N');
>
> SELECT keycol, CASE WHEN LEFT(val, 1) = RIGHT(val, 1)
> THEN LEFT(val, 1)
> ELSE LEFT(val, 1) + RIGHT(val, 1)
> END AS result
> FROM (
> SELECT keycol,
> REPLACE(
> COALESCE(NULLIF(col1, 'Y'), 'A') +
> COALESCE(NULLIF(col2, 'Y'), 'B') +
> COALESCE(NULLIF(col3, 'Y'), 'C') +
> COALESCE(NULLIF(col4, 'Y'), 'D'), 'N', '') AS val
> FROM Foo) AS T;
>
> SELECT keycol, CASE WHEN MIN(val) = MAX(val)
> THEN MIN(val)
> ELSE MIN(val) + MAX(val)
> END AS result
> FROM (
> SELECT keycol,
> CASE code WHEN 'A' THEN COALESCE(NULLIF(col1, 'Y'), code)
> WHEN 'B' THEN COALESCE(NULLIF(col2, 'Y'), code)
> WHEN 'C' THEN COALESCE(NULLIF(col3, 'Y'), code)
> WHEN 'D' THEN COALESCE(NULLIF(col4, 'Y'), code)
> END AS val
> FROM Foo AS F
> CROSS JOIN (SELECT 'A' UNION
> SELECT 'B' UNION
> SELECT 'C' UNION
> SELECT 'D') AS T(code)) AS T
> WHERE val <> 'N'
> GROUP BY keycol;
>
> /*
>
> keycol result
> ----------- ------
> 1 A
> 2 AC
> 3 AD
> 4 AD
> 5 AC
>
> */
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Question about index usage in a query
Next: 1 query; 2 different actual execution plan