Change case logic to boolean logic

Question

Change case logic to boolean logic

ssc_san

SSCommitted

Points: 1573
More actions
April 5, 2013 at 3:26 pm

#292113

Hi All,
I am trying to convert a case login to boolean logic, but I could not figure out one part of it.
Here is the DDL and the query I have done so far.
CREATE TABLE #t
(
c1 INT,
c2 INT,
r1 INT
)
INSERT INTO #t
SELECT 1, 2, 1
UNION ALL
SELECT 1, 1, 1
UNION ALL
SELECT 2, 1, 1
UNION ALL
SELECT 2, 3, 1
UNION ALL
SELECT 2, 2, 5
UNION ALL
SELECT 2, 1, 5
SELECT c1,
c2,
r1,
CASE
WHEN c1 = 2 THEN ( CASE
WHEN c2 = 2 THEN 'X'
WHEN r1 = 1 THEN 'O'
ELSE 'O'
END )
END 't1',
CASE
WHEN ( ( c1 = 2 OR r1 = 1 ) AND ( c2 <> 2 ) ) THEN 'O'
ELSE 'X'
END 't2'
FROM #t
Column "t1" is the case statement and Column "t2" I am trying to change the case to boolean (Not case with in case)
Thanks in advance!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

ssc_san (4/5/2013)
Hi All,
I am trying to convert a case login to boolean logic, but I could not figure out one part of it.
Here is the DDL and the query I have done so far.
CREATE TABLE #t
(
c1 INT,
c2 INT,
r1 INT
)
INSERT INTO #t
SELECT 1, 2, 1
UNION ALL
SELECT 1, 1, 1
UNION ALL
SELECT 2, 1, 1
UNION ALL
SELECT 2, 3, 1
UNION ALL
SELECT 2, 2, 5
UNION ALL
SELECT 2, 1, 5
SELECT c1,
c2,
r1,
CASE
WHEN c1 = 2 THEN ( CASE
WHEN c2 = 2 THEN 'X'
WHEN r1 = 1 THEN 'O'
ELSE 'O'
END )
END 't1',
CASE
WHEN ( ( c1 = 2 OR r1 = 1 ) AND ( c2 <> 2 ) ) THEN 'O'
ELSE 'X'
END 't2'
FROM #t
Column "t1" is the case statement and Column "t2" I am trying to change the case to boolean (Not case with in case)
Thanks in advance!!

Well, hard to know what is wrong when all I can determine is what your query currently does without knowing what it should be doing. What is your expected output based on the sample data?

ssc_san SSCommitted Points: 1573 More actions · Answer 2

Thanks for the reply Lynn Pettis.

There are two records with a value of '1' for column c1, t1 value for those records is null where as t2 has values as 'X' and 'O'. I want to display null's for those two records for t2 with out hardcoding or using case with in case.

Thanks again!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

ssc_san (4/5/2013)
Thanks for the reply Lynn Pettis.
There are two records with a value of '1' for column c1, t1 value for those records is null where as t2 has values as 'X' and 'O'. I want to display null's for those two records for t2 with out hardcoding or using case with in case.
Thanks again!

So, to paraphrase, the results for t2 should mirror the results of t1, correct?

ssc_san SSCommitted Points: 1573 More actions · Answer 4

ssc_san

SSCommitted

Points: 1573

April 5, 2013 at 3:46 pm

#1604163

Yes Lynn Pettis.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Don't think you can get it without added an addition WHEN clause to the CASE for t2. You are getting a null where c1 = 1 in t1 because you have no ELSE clause for the outer CASE used to determine t1.

ssc_san SSCommitted Points: 1573 More actions · Answer 6

Okay Lynn Pettis, Actually I want to code for the 'O' part of the case statement and populate a flag, that is the reason, I was trying to re-write it without multiple case statements.

Thanks!