Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Change case logic to boolean logic Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, April 5, 2013 3:26 PM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, November 3, 2016 11:52 AM Points: 89, Visits: 515
 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 Group: General Forum Members Last Login: Friday, December 2, 2016 4:50 PM Points: 23,515, Visits: 37,731
 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?
Post #1439509
 Posted Friday, April 5, 2013 3:37 PM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, November 3, 2016 11:52 AM Points: 89, Visits: 515
 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 Group: General Forum Members Last Login: Friday, December 2, 2016 4:50 PM Points: 23,515, Visits: 37,731
 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?
Post #1439513
 Posted Friday, April 5, 2013 3:46 PM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, November 3, 2016 11:52 AM Points: 89, Visits: 515
 Yes Lynn Pettis.
Post #1439514
 Posted Friday, April 5, 2013 3:49 PM
 SSC-Insane Group: General Forum Members Last Login: Friday, December 2, 2016 4:50 PM Points: 23,515, Visits: 37,731
 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.
Post #1439515
 Posted Friday, April 5, 2013 4:07 PM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, November 3, 2016 11:52 AM Points: 89, Visits: 515
 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

 Permissions