Complex Select Statement

My attached SQL returns 3 fields...Information 1 Information2, Information3

I need to return a 4th field called "allInclusive" with this logic

Included in this stored procedure I need to search the 3 returned felds and if ANY of them contains the string "All Inclusive"  I need to set the new field value to 1  else 0
ALTER PROCEDURE [dbo].[SelectUrgents] (@resortID nVarchar(255))
AS
BEGIN
	
	SET NOCOUNT ON;
select      @resortID as resortID, 
			(SELECT msgLine1 + ' ' + msgLine2 + ' ' + msgLine3 + ' ' + msgLine4 + ' ' + msgLine5 + ' '   
					from dbo.urgents where resortID = @resortID and seqNumber = '1') as  information1 ,
			(SELECT msgLine1 + ' ' + msgLine2 + ' ' + msgLine3 + ' ' + msgLine4 + ' ' + msgLine5 + ''   
					from dbo.urgents where resortID = @resortID and seqNumber = '2') as  information2 ,
			(SELECT msgLine1 + ' ' + msgLine2 + ' ' + msgLine3 + ' ' + msgLine4 + ' ' + msgLine5 + ' '
					from dbo.urgents where resortID = @resortID and seqNumber = '3') as  information3
END

Open in new window

Larry Bristersr. DeveloperAsked:
Who is Participating?
 
rajvjaCommented:
ALTER PROCEDURE [dbo].[SelectUrgents] (@resortID nVarchar(255))
AS
BEGIN

SET NOCOUNT ON;
select resortid, information1, information2, information3,
case when information1='All Inclusive' OR information2='All Inclusive' OR information3='All Inclusive' THEN 1 else 0 end as Information4 from
(
select @resortID as resortID,
(SELECT msgLine1 + ' ' + msgLine2 + ' ' + msgLine3 + ' ' + msgLine4 + ' ' + msgLine5 + ' '
from dbo.urgents where resortID = @resortID and seqNumber = '1') as information1 ,
(SELECT msgLine1 + ' ' + msgLine2 + ' ' + msgLine3 + ' ' + msgLine4 + ' ' + msgLine5 + ''
from dbo.urgents where resortID = @resortID and seqNumber = '2') as information2 ,
(SELECT msgLine1 + ' ' + msgLine2 + ' ' + msgLine3 + ' ' + msgLine4 + ' ' + msgLine5 + ' '
from dbo.urgents where resortID = @resortID and seqNumber = '3') as information3
) tab
END

0
 
dbidbaCommented:
Try this.

ALTER PROCEDURE [dbo].[SelectUrgents] (@resortID nVarchar(255))
AS
BEGIN
	
	SET NOCOUNT ON;
select 
   A.information1
  ,A.information2
  ,A.information3
  ,case
   when
      ( A.information1 like "%All Inclusive%"
      or A.information2 like "%All Inclusive%"
      or A.information3 like "%All Inclusive%"
      )
   then 1
   else 0
   end
from
   (select      @resortID as resortID, 
   			(SELECT msgLine1 + ' ' + msgLine2 + ' ' + msgLine3 + ' ' + msgLine4 + ' ' + msgLine5 + ' '   
   					from dbo.urgents where resortID = @resortID and seqNumber = '1') as  information1 ,
   			(SELECT msgLine1 + ' ' + msgLine2 + ' ' + msgLine3 + ' ' + msgLine4 + ' ' + msgLine5 + ''   
   					from dbo.urgents where resortID = @resortID and seqNumber = '2') as  information2 ,
   			(SELECT msgLine1 + ' ' + msgLine2 + ' ' + msgLine3 + ' ' + msgLine4 + ' ' + msgLine5 + ' '
   					from dbo.urgents where resortID = @resortID and seqNumber = '3') as  information3
   ) A
END

Open in new window

0
 
Larry Bristersr. DeveloperAuthor Commented:
dbidba,
  I had already submitted an accept before I saw yours.

Which is actually better.  Sorry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.