Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 147
  • Last Modified:

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

0
lrbrister
Asked:
lrbrister
1 Solution
 
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
 
lrbristerAuthor Commented:
dbidba,
  I had already submitted an accept before I saw yours.

Which is actually better.  Sorry
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now