Geek asks and answers

Fast SQL query syntax

I have a standard insert in / select that is formatted similarly to this:

insert into Table
( ...,
 ...)

select
field1,
field2,
field3,
field4,
fieldetc
from .... etc

In the select statement there are three specific fields that will need different values, selected depending on another field, let its checker call it, and three fields - field2, field3 and field 4. The values ​​will be either 0, or in another situation, you need a case when. My question is: how do I format the if / else statement so that it works in the select statement? As I have now, it looks like this:

select
field1data,
if checker = 'A' or checker is null
begin
  0,
  0,
  0,
end
else 
begin
case when ... then ... else ... end,
case when ... then ... else ... end,
case when ... then ... else ... end,
end
fieldetcdata
from... etc

This returns errors. How can I format this so that it works correctly, either by choosing zeros for these three fields, or by performing my case when the statements are in a different situation. Thank!

+3
source share
3 answers

IF BEGIN/END, . , ,

 CASE COALESCE(checker,'A') WHEN 'A' THEN 0 ELSE alternate_value END

, SELECT

: :

SELECT
    field1data,
    CASE WHEN ISNULL(checker) THEN alternate_value1 
         WHEN checker = 'B' THEN alternate_value11 END,
    CASE WHEN ISNULL(checker) THEN alternate_value2 
         WHEN checker = 'B' THEN alternate_value22 END,
    CASE WHEN ISNULL(checker) THEN alternate_value3 
         WHEN checker = 'B' THEN alternate_value3 END,
    fieldetcdata
FROM
    TABLE

EDIT2: WHEN.

http://msdn.microsoft.com/en-us/library/ms181765.aspx

+3

case .

Select  field1data,

        Case When IsNull(Checker,'A') = 'A' Then 0
             When Cond1 Then Whatever1
             ...
             Else ...
        End,

        Case When IsNull(Checker,'A') = 'A' Then 0
             When Cond2 Then Whatever1
             ...
             Else ...
        End,

        Case When IsNull(Checker,'A') = 'A' Then 0
             When Cond2 Then Whatever1
             ...
             ELSE ...
        End,

        fieldetcdata
From    ETC
+5

Edited based on the comments below.

You need to use the CASE statement with several WHEN clauses.

SELECT
  field1data,
  CASE COALESCE(checker, 'A') WHEN 'A' THEN 0 WHEN condition2 THEN ... ELSE ... END,
  CASE COALESCE(checker, 'A') WHEN 'A' THEN 0 WHEN condition2 THEN ... ELSE ... END,
  CASE COALESCE(checker, 'A') WHEN 'A' THEN 0 WHEN condition2 THEN ... ELSE ... END,
  CASE COALESCE(checker, 'A') WHEN 'A' THEN 0 WHEN condition2 THEN ... ELSE ... END,
  CASE COALESCE(checker, 'A') WHEN 'A' THEN 0 WHEN condition2 THEN ... ELSE ... END
FROM ...
+1
source

More articles:


All Articles