CASE like function to set multiple fields?

[mdlueck]

Greetings,

I have a place in a query where I would like to use CASE to key off of one value, and then in each case set multiple fields.

Is that possible using a single case, or must I use a separate CASE for each field to be set and always refer to the one field that indicates which CASE is true?

Air Code:

      CASE ProductLine
         WHEN 'R' THEN ColA = 'Road', ColB = 5
         WHEN 'M' THEN ColA = 'Mountain', ColB = 6
         WHEN 'T' THEN ColA = 'Touring', ColB = 7
         WHEN 'S' THEN ColA = 'Other sale items', ColB = 8
         ELSE ColA = 'Not for sale', ColB = 0
      END

Is something like that possible, or does it take separate CASE statements to set each variable?

 

[sjdeetz]

I was wondering the same thing and your post gave me some ideas to try out. I created this type of code to aggregate two similar types of deposits into a specific field (SubHra) with the following statement:

CASE SubscriberAccountType WHEN 1 THEN DepositAmount WHEN 5 THEN DepositAmount ELSE 0 END AS SubHRA

SQL Server did not like it when the comma entered behind the first DepositAmount. The above statement summarized the DepositAmount field perfectly on the statement.

 

[bparkinson]

I think you might want to create a stratified histogram. Ken Henderson's excellent book describes how:

http://books.google.com.my/books?id...=onepage&q=t-sql stratified histogram&f=false

 

[mdlueck]

I think you might want to create a stratified histogram.

??? I do not see that syntax setting multiple values based on a single control value.

What I would like to do is roughly:

air code SQL sudo

If ColA = 'PROD' then
  [jderev] AS [rev]
  [jdever] AS [ver]
  [jdesip] AS [price]
else
  [prev] AS [rev]
  [pver] AS [ver]
  [qprice] AS [price]
EndIf

As it, I see needing to use multiple CASE statements ALL looking at the same control variable/field.

Better?

 

[tehNellie]

I don't think you can do it this way, but what are you actually trying to achieve?

I think as you suspect that if you're trying to substitute a number of columns (in a select statement) that you'll have to do it column at a time.

ie

SELECT ....
CASE @test
  WHEN @test = 1 THEN t.thiscolumn
   ELSE t.thatcolumn
END as example

Is fine

SELECT ....
CASE @test
  WHEN @test = 1 THEN t.thiscolumn, t.thatcolumn
   ELSE t.thatcolumn, t.thiscolumn
END as example

Isn't.

 

[mdlueck]

but what are you actually trying to achieve?

I would like to choose if to select production JDE columns or per-production Fandango columns based on a single indicator variable.

It seemed redundant to have multiple CASE statements all toggling on the same indicator.

 

[tehNellie]

I suspect that what you might need to do is something more along the lines of

If somevalue = 'foo'
   SELECT 1,2,3,4 
   FROM etc etc etc
else if somevalue = 'bar'
   Select 5,6,7,8 
   FROM etc etc
end

 

[mdlueck]

And that would require two separate queries. The first to look up the indicator, and the second the correct query based on which way the indicator was pointing.

Since I am using pass-through queries, I was seeking some fancy SQL to do it all in one query!

 

[tehNellie]

You can always use the query to generate a string of SQL and pass that to sp_executesql. Granted that's still essentially running two queries as is the other alternative that springs to mind of using a UNION [ALL]

SELECT The Final Output
FROM table
INNER JOIN 
(
  Select 1,2,3, 'criteria' as filter
  FROM table
  UNION 
  SELECT 4,5,6, 'differentCriteria' as filter
  FROM table
) u on
 tbl.column = u.column AND tbl.criteriacolumn = u.filter

 

[mdlueck]

... AND tbl.criteriacolumn = u.filter

Ohhhh, and so the values such as 'criteria' are the values to be tested for to determine which state the DB record is in? (JDE Prod vs Fandango Dev)

So substitude actual values that the flag column could be in where you have 'criteria'? Just like the CASE control variable? If so, that looks like a reasonable solution. Thanks!

 

[tehNellie]

It's a bit of a blunderbuss approach for sure but yeah

SELECT columns
FROM Table t
INNER JOIN
(
 select ID, 1,2,3, 'Prod' as criteria
 FROM prodTable
 UNION [ALL]
 SELECT ID, 4,5,6 'DEV' as criteria
 FROM devTable
) as u ON
t.ID = u.ID AND t.colA = u.criteria