Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Change case logic to boolean logic Expand / Collapse
Author
Message
Posted Friday, April 5, 2013 3:26 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 9:09 AM
Points: 73, Visits: 486
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!!
Post #1439505
Posted Friday, April 5, 2013 3:31 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:24 PM
Points: 23,066, Visits: 31,595
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?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1439509
Posted Friday, April 5, 2013 3:37 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 9:09 AM
Points: 73, Visits: 486
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!
Post #1439511
Posted Friday, April 5, 2013 3:42 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:24 PM
Points: 23,066, Visits: 31,595
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?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1439513
Posted Friday, April 5, 2013 3:46 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 9:09 AM
Points: 73, Visits: 486
Yes Lynn Pettis.
Post #1439514
Posted Friday, April 5, 2013 3:49 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:24 PM
Points: 23,066, Visits: 31,595
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1439515
Posted Friday, April 5, 2013 4:07 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 9:09 AM
Points: 73, Visits: 486
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!
Post #1439520
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse