Stuck with logic and conditional statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stuck with logic and conditional statement

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-05-27 : 12:44:30

Hi

What is the equivalent of OR in a case statment? I have found nothing on the net so I am imagining there is none it just supports IF - THEN - ELSE.

Here is more details on what I am trying to do - if you're a geek see if you can solve it!

I have a report that I have to display two DB values - each one returned from a different function - if any of the returned values are non-zero. So my first Q is what logic should I use to check this condition as I need to check the values returned from two functions then effectively display both returned values (even zero values) as seperate columns but how can I do that from one condition in a SQL statement.

so in basic textual description of the requirement would be:

If f(x) <> 0 or f(y) <> 0 THEN -- If either returned results are zero 
SHOW f(x) and f(y) -- Show both of the returned results from (fx)(fy)
ELSEIF f(x) = 0 AND f(y) = 0 THEN -- If both returned results are zero I need to check both returned results of next functions before carrying on
(IF f1(x) <> 0 or f1(y) <> 0 THEN -- Check returned results of functions (1x) (1y)
 SHOW f1(x) and f1(y) - if any are non zero show both values of f1(x) and f1(y)
 ELSEIF f1(x) = 0 AND f1(y) = 0 THEN --I need to check both returned results of next functions f2(y) and f2(y) before carrying on but only if f1(x) and f1(y) are both zero
  (IF f2(x) <> 0 or f2(y) <> 0 THEN
   SHOW F2(x) and f2(y) - Show f2(x) and f2(y )results if any are non-zero
   ELSE 
   SHOW f3(x) and f3(y) -- final function values to show if all other function sets are both zero
End))

I'm not quite sure if the logic I posted is correct as I am thinking all processing needs to be kept in same condition as this is what the requirements want. To reiterate:

I need to figure out the right logic to use
Than figure out a way to show both returned results somehow at one point in the condition.

Any ideas or suggestions would be most welcome.

G

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-27 : 12:59:04

case when ... or ... then ... else ... end

The when part just has to return a boolean. You can nest and everything is an expression that returns a single value per row.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-05-28 : 03:01:58

if you need to reiterate your data you can do this by using either cursors and within each row run case statement.
or you can do this with common table expressions.

or you can to this with IF, WHILE loops and runs all the rows until condition satisfied.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-05-29 : 00:20:34

>> What is the equivalent of OR in a CASE statement? <<

%There is no CASE statement in SQL; we have a CASE expression. Fundamental difference here!

>> I have a report that I have to display two DB values - each one returned from a different function - if any of the returned values are non-zero. <<

Functions? We hate those things in SQL. They return scalar values, Ugh!! We write queries which returns sets. In short, your whole mindset is wrong for RDBMS and SQL. Are you the kid that asked about WHILE loops in LISP? This is theat kind of mindsety error.

There is no sequential processing in a query; that was file processing.

so in basic textual description of the requirement would be:

IF f(x) <> 0 OR f(y) <> 0
THEN SHOW f(x) AND f(y)
ELSE IF f(x) = 0 AND f(y) = 0
THEN IF f1(x) <> 0 OR f1(y) <> 0
THEN SHOW f1(x) AND f1(y)
ELSE IF f1(x) = 0 AND f1(y) = 0
THEN IF f2(x) <> 0 OR f2(y) <> 0
THEN SHOW f2(x) AND f2(y)
ELSE SHOW f3(x) AND f3(y);

SQL people would put all of those chains of procedural code into an expression. We would have a skeleton like this:

SELECT (<expression for computation>) AS fx,
(<expression for computation>) AS fy
FROM Foobar;

<expression for computation> can have CASE expression in them to do those variant computations. We could do better with more details.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-05-29 : 16:46:50

Hi

I have managed to build up a query case "expression" but have one more question. During the case statement I need to populate a variable with as a pseudo-heading for the report. So I am unsure how to do that in the statement. What is the syntax for using a variable within a case statement. The case statement has a few sub case statements so need to populate it there as well.

[CODE]CASE (WHEN 1 = '0.0' OR 2 = '0') THEN
CASE WHEN (3 = '0.0' OR 4 = '0') THEN
CASE WHEN (5 = '0.0' OR 6 = '0') THEN
CASE WHEN (7 = '0.0' OR 8 = '0') THEN
'Heading 1' << Variable Value
END
ELSE 'Heading 2' << Variable Value
END
END
ELSE 'Heading 3' << Variable Value
END[/CODE]

I am using the case statement on it's own so I can return the heading as a psuedo column.

Any thoughts on this do I use SET|SELECT|'' @Variable = (Case Statement) and do I assign the string value to the variable each time in the case statement (at heading 1/2/3)or does it get done through the case statement itself?

Thanks

G

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-05-29 : 17:36:50

>> have managed to build up a query CASE "expression" but have one more question. <<
Damn it, it is a CASE expression and there is a honking big difference. A CASE statement controls execution flow; there is no flow in declarative languages. An expression has one data type and return a scalar result. During the CASE statement I need to populate a variable with as a pseudo-heading for the report. <<

Assignment statements? There is no "during" with declarative code; it happens all at once. We have declarative expressions. Pseudo-heading? Header? NO! SQL is a database language and it does no display formatting. Zip, Nada, None! That was monolithic COBOL programming before we went to tiered architectures.

>> So I am unsure how to do that in the statement. What is the syntax for using a variable within a CASE statement [sic]. The CASE statement [sic] has a few sub CASE statements [sic] so need to populate it there as well. <<

Yes, you can nest CASE expressions, just like you can nest arithmetic expressions.
CASE WHEN 1 = '0.0' OR 2 = '0'
THEN CASE WHEN 3 = '0.0' OR 4 = '0'
THEN CASE WHEN 5 = '0.0' OR 6 = '0'
THEN CASE WHEN (7 = '0.0' OR 8 = '0')
THEN 'Heading 1' << Variable Value
END
ELSE 'Heading 2' << Variable Value
END
END
ELSE 'Heading 3' << Variable Value
END

Did you notice that your predicates are constants? This whole thing is 'Heading 3' when you evaluate it.

>> Any thoughts on this do I use SET|SELECT|'' @variable = (CASE statement [sic] ) and do I assign the string value to the variable each time in the CASE statement [sic] <<

If you understood the differences between statements and expression, you would never have asked this. Yes, you can use T-SQL procedural dialect and mimic 1950's COBOL at huge cost:

BEGIN
DECLARE @display_header VARCHAR(15);
SET @display_header = CASE .. END;
END;

Another thought that could help you; use look up tables for complex functions; SQL is a data language. Read http://www.simple-talk.com/sql/t-sql-programming/look-up-tables-in-sql-/

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-05-31 : 14:40:22

I ended up using a function to do the processing and a much simpler CASE Expression/Statement in my query.

G

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources