SQL Server T-SQL – How to Use SET Operation in CASE Clause

casesql serversql-server-2012t-sql

I have the following structure, and I'm doing a SET operation in a CASE clause but it is producing an error:

Incorrect syntax near SET

Query:

DECLARE @tbl_Result TABLE
(
    RowID INT IDENTITY(1,1),
    TtlNOfCmpSale7to12 INT,
    TtlNOfCmpSale4to6 INT,
    TtlNOfCmpSale0to3 INT,
    TtlNOfCmpSaleSpanFirst SMALLINT DEFAULT 0,
    TtlNOfCmpSaleSpanSecond SMALLINT DEFAULT 0,
    TtlNOfCmpSaleSpanThird SMALLINT DEFAULT 0
);

DECLARE 
    @Declining FLOAT,
    @StableStart SMALLINT,
    @StableEnd SMALLINT,
    @Increasing SMALLINT;

SELECT
    @Declining = 0.90,
    @StableStart = 90,
    @StableEnd = 110,
    @Increasing = 110;

DECLARE @TrendValue FLOAT; 

UPDATE R
SET R.TtlNOfCmpSaleSpanFirst =
    CASE
        WHEN R.TtlNOfCmpSale7to12 != 0 THEN
            SET @TrendValue = R.TtlNOfCmpSale4to6*2/R.TtlNOfCmpSale7to12
            (
                CASE 
                    WHEN @TrendValue > @Increasing THEN 0
                    WHEN @TrendValue >  @StableStart 
                        AND @TrendValue <  @StableEND THEN 1
                    WHEN @TrendValue < @Declining THEN 2
                END
            )
    END,
    R.TtlNOfCmpSaleSpanSecond = 
        CASE 
            WHEN R.TtlNOfCmpSale4to6!= 0 THEN
                SET @TrendValue = R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale4to6
                (
                    CASE 
                        WHEN @TrendValue > @Increasing THEN 0
                        WHEN @TrendValue >  @StableStart 
                            AND @TrendValue <  @StableEND THEN 1
                        WHEN @TrendValue < @Declining THEN 2
                    END
                )
        END,
    R.TtlNOfCmpSaleSpanThird = 
        CASE 
            WHEN R.TtlNOfCmpSale7to12 != 0 THEN
                SET @TrendValue = R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale7to12
                (
                    CASE 
                        WHEN @TrendValue > @Increasing THEN 0
                        WHEN @TrendValue >  @StableStart 
                            AND @TrendValue <  @StableEND THEN 1
                        WHEN @TrendValue < @Declining THEN 2
                    END
                )
        END
FROM Result R;

It would be better if there is another way to store the result of the expression into @TrendValue so that there is no need to calculate that expression each time.

It would also be good to avoid division by zero errors (that's the reason I added the various R.TtlNOfCmpSale7to12 != 0 checks).

Best Answer

Based on your comment, you seem to be looking for a way to avoid repeating the same calculation over and over again.

You could use APPLY VALUES to calculate the required values once, then use the aliased column in the rest of your calculations.

DECLARE @Declining FLOAT,
@StableStart SMALLINT,
@StableEnd SMALLINT,
@Increasing SMALLINT

SELECT  @Declining = 0.90,
@StableStart = 90,
@StableEnd = 110,
@Increasing = 110

DECLARE @TrendValue FLOAT; 

UPDATE R
SET
 R.TtlNOfCmpSaleSpanFirst = 
            CASE 
                WHEN R.TtlNOfCmpSale7to12 != 0 THEN
                  CASE WHEN Trends.TrendFirst > @Increasing THEN 0
                        WHEN Trends.TrendFirst >  @StableStart AND Trends.TrendFirst <  @StableEND THEN 1
                        WHEN Trends.TrendFirst < @Declining THEN 2
                  END
            END,
 R.TtlNOfCmpSaleSpanSecond = 
            CASE 
                WHEN R.TtlNOfCmpSale4to6!= 0 THEN
                  CASE WHEN Trends.TrendSecond > @Increasing THEN 0
                        WHEN Trends.TrendSecond >  @StableStart AND Trends.TrendSecond <  @StableEND THEN 1
                        WHEN Trends.TrendSecond < @Declining THEN 2
                  END
            END,

 R.TtlNOfCmpSaleSpanThird = 
            CASE 
                WHEN R.TtlNOfCmpSale7to12 != 0 THEN
                  CASE WHEN Trends.TrendThird > @Increasing THEN 0
                        WHEN Trends.TrendThird >  @StableStart AND Trends.TrendThird <  @StableEND THEN 1
                        WHEN Trends.TrendThird < @Declining THEN 2
                  END
            END

FROM Result R
CROSS APPLY (
    VALUES (IIF(R.TtlNOfCmpSale7to12 <> 0, R.TtlNOfCmpSale4to6*2/R.TtlNOfCmpSale7to12, 0)
              IIF(R.TtlNOfCmpSale4to6 <> 0, R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale4to6, 0) 
              IIF(R.TtlNOfCmpSale7to12 <> 0, R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale7to12, 0)
) AS Trends(TrendFirst, TrendSecond, TrendThird);

Related Solutions

Oracle SQL – CASE in a WHERE clause

Thanks for posting the sample data. It would also be helpful to describe in words and with actual output what you want to be returned by your query.

I'm guessing that you want something like

SQL> ed
Wrote file afiedt.buf

  1  WITH T1 AS
  2  (
  3  SELECT 1 AS SEQ, 'NOTHING 1' AS SOME_TYPE FROM DUAL UNION ALL
  4  SELECT 2 AS SEQ, 'NOTHING 2' AS SOME_TYPE FROM DUAL UNION ALL
  5  SELECT 3 AS SEQ, 'SOMETHING 1' AS SOME_TYPE FROM DUAL UNION ALL
  6  SELECT 4 AS SEQ, 'SOMETHING 2' AS SOME_TYPE FROM DUAL UNION ALL
  7  SELECT 5 AS SEQ, 'SOMETHING 3' AS SOME_TYPE FROM DUAL UNION ALL
  8  select 6 as seq, 'SOMETHING 4' AS SOME_type from dual
  9  )
 10  , T2 AS
 11  (
 12  SELECT 'A' AS COMPARE_TYPE FROM DUAL UNION ALL
 13  SELECT 'B' AS COMPARE_type FROM DUAL
 14  )
 15  SELECT T2.*, T1.*
 16    FROM T1, T2
 17   WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND
 18                    T1.SOME_TYPE LIKE 'NOTHING%'
 19                 THEN 1
 20               WHEN T2.COMPARE_TYPE != 'A' AND
 21                    T1.SOME_TYPE NOT LIKE 'NOTHING%'
 22                 THEN 1
 23               ELSE 0
 24*           END) = 1
SQL> / 

C        SEQ SOME_TYPE
- ---------- -----------
A          1 NOTHING 1
A          2 NOTHING 2
B          3 SOMETHING 1
B          4 SOMETHING 2
B          5 SOMETHING 3
B          6 SOMETHING 4

6 rows selected.

But I'm making a lot of guesses about what your code is supposed to mean.

That this appears to be identical to a question someone asked in the OTN forums. My answer is the same in both places.

Sql-server – SQL Case Sensitive Query

You need to check that the collation of the servers and columns match (collation isn't only determined at the database level).

To check the servers:

SELECT SERVERPROPERTY('COLLATION');

To check the columns (a much, much, much more likely culprit for this symptom IMHO):

SELECT collation_name
  FROM [Temp].sys.columns AS c
   INNER JOIN [Temp].sys.objects AS o ON c.[object_id] = o.[object_id]
   INNER JOIN [Temp].sys.schemas AS s ON o.[schema_id] = s.[schema_id]
WHERE UPPER(c.name = N'TABLE_NAME')
  AND UPPER(o.name = N'CLASS')
  AND UPPER(s.name = N'TEAM');

If these don't match, then your choices are:

Make them match. E.g. on the server where the collation is case sensitive, you can make it not case sensitive using:

ALTER TABLE [Team].[CLASS] 
  ALTER COLUMN [TABLE_NAME] VARCHAR(32) -- guessing
  COLLATE SQL_Latin1_General_CP1_CI_AS 
  NOT NULL; -- also guessing

Use a case-insensitive query, e.g.

WHERE UPPER([TABLE_NAME]) LIKE '%LINK%';

-- or

WHERE [TABLE_NAME] COLLATE SQL_Latin1_General_CP1_CI_AS
  LIKE '%LINK%';

Also be sure to use an N prefix if the column is NVARCHAR.

Best Answer

Related Solutions

Oracle SQL – CASE in a WHERE clause

Sql-server – SQL Case Sensitive Query

Related Question