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

• Nomvula

  Hall of Fame

  Points: 3778

  More actions

  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 );

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  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.

  😎

• Nomvula

  Hall of Fame

  Points: 3778

  More actions

  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 );

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  November 24, 2016 at 7:39 am

  #1914415

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

  😎

• Nomvula

  Hall of Fame

  Points: 3778

  More actions

  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 );

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  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

• Nomvula

  Hall of Fame

  Points: 3778

  More actions

  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,

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  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?

  😎

• Nomvula

  Hall of Fame

  Points: 3778

  More actions

  November 25, 2016 at 5:10 am

  #1914512

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

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  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