Asked  6 Months ago    Answers:  5   Viewed   353 times

The following image is a part of Microsoft SQL Server 2008 R2 System Views. From the image we can see that the relationship between sys.partitions and sys.allocation_units depends on the value of sys.allocation_units.type. So to join them together I would write something similar to this:

SELECT  *
FROM    sys.indexes i
        JOIN sys.partitions p
            ON i.index_id = p.index_id 
        JOIN sys.allocation_units a
            ON CASE
               WHEN a.type IN (1, 3)
                   THEN a.container_id = p.hobt_id 
               WHEN a.type IN (2)
                   THEN a.container_id = p.partition_id
               END 

But the upper code gives a syntax error. I guess that's because of the CASE statement. Can anyone help to explain a little?


Add error message:

Msg 102, Level 15, State 1, Line 6 Incorrect syntax near '='.

this is the image

 Answers

40

A CASE expression returns a value from the THEN portion of the clause. You could use it thusly:

SELECT  * 
FROM    sys.indexes i 
    JOIN sys.partitions p 
        ON i.index_id = p.index_id  
    JOIN sys.allocation_units a 
        ON CASE 
           WHEN a.type IN (1, 3) AND a.container_id = p.hobt_id THEN 1
           WHEN a.type IN (2) AND a.container_id = p.partition_id THEN 1
           ELSE 0
           END = 1

Note that you need to do something with the returned value, e.g. compare it to 1. Your statement attempted to return the value of an assignment or test for equality, neither of which make sense in the context of a CASE/THEN clause. (If BOOLEAN was a datatype then the test for equality would make sense.)

Tuesday, June 1, 2021
 
Akdeniz
answered 6 Months ago
19
  1. In MySQL, backticks quote names, while single quotes create strings. If you have a column called select, MySQL would throw an syntax error when using this name without backticks -- like in SELECT select FROM foo -- as it would interpret it as keyword which may not occur there.

  2. This IF function can be used as a column specification in SELECT statements. See the MySQL reference.

  3. This function returns the value from the default column, if value is the empty string. Else it returns the value from value itself. The result will be called value. See the MySQL reference for details.

Monday, June 14, 2021
 
nfechner
answered 6 Months ago
13

You can do it using an OR:

WHERE   (@Id > 0 AND Table1.Field = @Id)
OR      (@Id = 0 AND Table1.Field IN (6,16,18))

However, I would advise using (as you have said) IF/ELSE, when mashing together two conditions like this you can often force suboptimal plans. e.g In your example you could simplify this to a schema as follows:

CREATE TABLE T
(   ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, 
    Field INT NOT NULL, 
    SomeOtherField INT NULL
);
GO
INSERT T  (Field)
SELECT  Number
FROM    Master..spt_values
        CROSS JOIN (VALUES (1), (2), (3)) t (A)
WHERE   Type = 'P'
GO
CREATE NONCLUSTERED INDEX IX_T_Field ON T (Field) INCLUDE (SomeOtherField);

This simply populates one of the columns with the numbers 0-2047 repeated 4 times each (just for some example data). Then If I create two procedures, one that uses 'IF/ELSE' one that combines the criteria as above:

CREATE PROCEDURE dbo.Test @ID INT
AS
    SELECT  ID, Field, SomeOtherField
    FROM    T
    WHERE   (@Id > 0 AND T.Field = @Id)
    OR      (@Id = 0 AND T.Field IN (6,16,18))

GO
CREATE PROCEDURE dbo.Test2 @ID INT
AS
    IF @ID = 0
        SELECT  ID, Field, SomeOtherField
        FROM    T
        WHERE   T.Field IN (6, 16, 18)
    ELSE
        SELECT  ID, Field, SomeOtherField
        FROM    T
        WHERE   T.Field = @Id
GO

Since compilation of queries will only happen once (unless you explicitly say otherwise), the optimiser will not pick a different plan depending on whether you pass 0 or pass an ID > 0 to the procedure, so both of the following:

EXECUTE dbo.Test 0;
EXECUTE dbo.Test 1;

Will give this plan:

enter image description here

The second procedure is able to estimate the best execution plan much better so running this:

EXECUTE dbo.Test2 0;
EXECUTE dbo.Test2 1;

Gives the following plan:

enter image description here

Real world examples will obviously vary, and I have deliberately constructed an example that proves my point. It is slightly more effort to duplicate a lot of code by using IF/ELSE, but it is often worth it.

Saturday, August 7, 2021
 
mschuett
answered 4 Months ago
22

No. You cannot do this,

switch (age){
case (>79):
  // Do this stuff
  break;
case (>50):
  // Do this other stuff
  break;
}

You need an if and else,

if (age > 79) {
 // do this stuff.
} else if (age > 50) {
 // do this other stuff.
} // ...
Wednesday, September 22, 2021
 
Nenad Dordevic
answered 2 Months ago
64

You could do it an alternate way:

SELECT * 
FROM dbo.tblOrders o
WHERE o.OrderId IN (SELECT [Value] FROM [dbo].[udf_GenerateVarcharTableFromStringList](@OrderId, ','))
AND ((@ActiveInactive = 'Active' AND o.[orderactivedate] > o.[orderinactivedate])
OR   (@ActiveInactive = 'Inactive' AND o.[orderactivedate] < o.[orderinactivedate]))
Monday, November 22, 2021
 
jezrael
answered 1 Week ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share