Post reply

how to return multiple values in the where clause using case statement

  • November 24, 2016 at 6:49 am

    #332046

    hi

    I've created this query for my ssrs report but i''m getting the error:

    Invalid length parameter passed to the LEFT or SUBSTRING function

    I want to be able to select multiple values for the @shift parameter.

    DECLARE @Shifts TABLE

    (

    code VARCHAR(10)

    ,Description VARCHAR(100)

    );

    INSERT INTO @Table

    ( code

    ,Description

    )

    VALUES

    ( 'CTN-A'

    ,'Morning Shift ( Film18H, Fed2L)'

    );

    INSERT INTO @Table

    ( code

    ,Description

    )

    VALUES

    ( 'CTN-B'

    ,'Morning Shift (Film18H)'

    );

    INSERT INTO @Table

    ( code

    ,Description

    )

    VALUES

    ( 'CTN-C'

    ,'Afternoon Shift (Film18H)'

    );

    INSERT INTO @Table

    ( code

    ,Description

    )

    VALUES

    ( 'CTN-D'

    ,'Afternoon Shift (FMF/SVP)'

    );

    INSERT INTO @Table

    ( code, Description )

    VALUES

    ( 'CTN-H', 'Night Shift' );

    INSERT INTO @Table

    ( code, Description )

    VALUES

    ( 'CTN-G', 'Afternoon Shift' );

    SELECT

    code

    ,Description AS Shifts

    FROM

    @Table AS s

    WHERE

    s.Description IN (

    CASE WHEN s.Description LIKE '%(%'

    THEN LEFT(s.Description, CHARINDEX('(', @Shift) - 1)

    ELSE @Shift

    END );

  • November 24, 2016 at 6:56 am

    #1914408

    Nomvula (11/24/2016)

    hi

    I've created this query for my ssrs report but i''m getting the error:

    Invalid length parameter passed to the LEFT or SUBSTRING function

    I want to be able to select multiple values for the @shift parameter.

    DECLARE @Shifts TABLE

    (

    code VARCHAR(10)

    ,Description VARCHAR(100)

    );

    INSERT INTO @Table

    ( code

    ,Description

    )

    VALUES

    ( 'CTN-A'

    ,'Morning Shift ( Film18H, Fed2L)'

    );

    INSERT INTO @Table

    ( code

    ,Description

    )

    VALUES

    ( 'CTN-B'

    ,'Morning Shift (Film18H)'

    );

    INSERT INTO @Table

    ( code

    ,Description

    )

    VALUES

    ( 'CTN-C'

    ,'Afternoon Shift (Film18H)'

    );

    INSERT INTO @Table

    ( code

    ,Description

    )

    VALUES

    ( 'CTN-D'

    ,'Afternoon Shift (FMF/SVP)'

    );

    INSERT INTO @Table

    ( code, Description )

    VALUES

    ( 'CTN-H', 'Night Shift' );

    INSERT INTO @Table

    ( code, Description )

    VALUES

    ( 'CTN-G', 'Afternoon Shift' );

    SELECT

    code

    ,Description AS Shifts

    FROM

    @Table AS s

    WHERE

    s.Description IN (

    CASE WHEN s.Description LIKE '%(%'

    THEN LEFT(s.Description, CHARINDEX('(', @Shift) - 1)

    ELSE @Shift

    END );

    Can you correct your code please, table variable @Table not declared and scalar variable @shift declared as table.

    😎

  • November 24, 2016 at 7:06 am

    #1914409

    noted thanks :

    DECLARE @Table TABLE

    (

    code VARCHAR(10)

    ,Description VARCHAR(100)

    );

    INSERT INTO @Table

    ( code

    ,Description

    )

    VALUES

    ( 'CTN-A'

    ,'Morning Shift ( Film18H, Fed2L)'

    );

    INSERT INTO @Table

    ( code

    ,Description

    )

    VALUES

    ( 'CTN-B'

    ,'Morning Shift (Film18H)'

    );

    INSERT INTO @Table

    ( code

    ,Description

    )

    VALUES

    ( 'CTN-C'

    ,'Afternoon Shift (Film18H)'

    );

    INSERT INTO @Table

    ( code

    ,Description

    )

    VALUES

    ( 'CTN-D'

    ,'Afternoon Shift (FMF/SVP)'

    );

    INSERT INTO @Table

    ( code, Description )

    VALUES

    ( 'CTN-H', 'Night Shift' );

    INSERT INTO @Table

    ( code, Description )

    VALUES

    ( 'CTN-G', 'Afternoon Shift' );

    SELECT

    code

    ,Description AS Shifts

    FROM

    @Table AS s

    WHERE

    s.Description IN (

    CASE WHEN s.Description LIKE '%(%'

    THEN LEFT(s.Description, CHARINDEX('(', @Shift) - 1)

    ELSE @Shift

    END );

  • November 24, 2016 at 7:39 am

    #1914415

    And what value are you passing for the @shift variable?

    😎

  • November 25, 2016 at 12:39 am

    #1914483

    if the value of the @shift = 'Afternoon Shift (FMF/SVP)' it must return 'Afternoon Shift'

    else 'Afternoon Shift'. Hence the case statement:

    WHERE

    s.Description IN (

    CASE WHEN s.Description LIKE '%(%'

    THEN LEFT(s.Description, CHARINDEX('(', @Shift) - 1)

    ELSE @Shift

    END );

  • November 25, 2016 at 1:25 am

    #1914487

    Here is a suggestion

    😎

    DECLARE @Shift VARCHAR(100) = 'Afternoon Shift (FMF/SVP)';

    DECLARE @Table TABLE

    (

    code VARCHAR(10)

    ,Description VARCHAR(100)

    );

    INSERT INTO @Table

    ( code ,Description)

    VALUES

    ( 'CTN-A','Morning Shift ( Film18H, Fed2L)')

    ,( 'CTN-B','Morning Shift (Film18H)' )

    ,( 'CTN-C','Afternoon Shift (Film18H)' )

    ,( 'CTN-D','Afternoon Shift (FMF/SVP)' )

    ,( 'CTN-H', 'Night Shift' )

    ,( 'CTN-G', 'Afternoon Shift' );

    SELECT

    code

    ,SUBSTRING(s.Description,1,ISNULL(NULLIF(CHARINDEX(' (',Description,1),0),LEN(Description))) AS XShifts

    ,Description AS Shifts

    FROM

    @Table AS s

    WHERE

    @Shift LIKE s.Description + '%';

    Output

    code XShifts Shifts

    ---------- ---------------- --------------------------

    CTN-D Afternoon Shift Afternoon Shift (FMF/SVP)

    CTN-G Afternoon Shift Afternoon Shift

  • November 25, 2016 at 3:31 am

    #1914498

    Thanks for the response, but I tried that solution it's only works if you select single value. I you select multiple selection the WHERE clause doesn't work.

    Regards,

  • November 25, 2016 at 3:44 am

    #1914500

    Nomvula (11/25/2016)

    Thanks for the response, but I tried that solution it's only works if you select single value. I you select multiple selection the WHERE clause doesn't work.

    Regards,

    Do you want @shift to contain more than one value?

    😎

  • November 25, 2016 at 5:10 am

    #1914512

    Yes, I want to be able to enter multiple values for the @shift.

  • November 25, 2016 at 7:07 am

    #1914517

    Nomvula (11/25/2016)

    Yes, I want to be able to enter multiple values for the @shift.

    @shift is a scalar variable, you are probably better of splitting the content or do some other tricks to make this work.

    😎

    This kind of works though

    DECLARE @Shift VARCHAR(100) = 'Afternoon Shift (FMF/SVP),Morning Shift ( Film18H, Fed2L),Night Shift';

    DECLARE @Table TABLE

    (

    code VARCHAR(10)

    ,Description VARCHAR(100)

    );

    INSERT INTO @Table

    ( code ,Description)

    VALUES

    ( 'CTN-A','Morning Shift ( Film18H, Fed2L)')

    ,( 'CTN-B','Morning Shift (Film18H)' )

    ,( 'CTN-C','Afternoon Shift (Film18H)' )

    ,( 'CTN-D','Afternoon Shift (FMF/SVP)' )

    ,( 'CTN-H', 'Night Shift' )

    ,( 'CTN-G', 'Afternoon Shift' );

    SELECT

    code

    ,SUBSTRING(s.Description,1,ISNULL(NULLIF(CHARINDEX(' (',Description,1),0),LEN(Description))) AS XShifts

    ,Description AS Shifts

    FROM

    @Table AS s

    WHERE

    @Shift LIKE '%' + s.Description + '%';

    Output

    code XShifts Shifts

    ---------- ----------------- --------------------------------

    CTN-A Morning Shift Morning Shift ( Film18H, Fed2L)

    CTN-D Afternoon Shift Afternoon Shift (FMF/SVP)

    CTN-H Night Shift Night Shift

    CTN-G Afternoon Shift Afternoon Shift

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy