oopss.Incorrect syntax near the keyword 'union',Incorrect syntax near the keyword 'if'. HAving NO ClUE

Rajar Ahmed
Rajar Ahmed used Ask the Experts™
on

 Hi ,

   i want to get worked this union condition with the if else condition , but am stuck with these error . Please gimme a solution which involves logic wit union
condition as it is mentioned below .

Scenario,
I need to generate a  seperate select statement with
where condition SUBJECT query and make union of the other queries . i want to have all queries which i mentioned below .
MY only problem , i dunno how to do union with the one will be generated in if else loop and  am getting all these errorss.....
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'if'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'union'.
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'union'.


declare @str nvarchar(20)
set @str = 'test'
select * from tbl1 where cname='jame'        
Union       
if (@str = 'test')
begin
select * from tbl1 where SUBJECT  like 'dest' or SUBJECT  = 'dest'       
end
else
begin
select * from tbl1 where SUBJECT like 'depa' or SUBJECT = 'depa'
end  
union
select * from tbl1 where cname like 'nape'
union
select * from tbl1 where cname like 'uesed'

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you should do something like this:



declare @str nvarchar(20)
declare @SQL nvarchar(max)
set @str = 'test'

set @SQL=' select * from tbl1 where cname=''jame''        
Union '      
if (@str = 'test')
begin
	set @SQL= @SQL + ' select * from tbl1 where SUBJECT  like ''dest'' or SUBJECT  = ''dest'''       
end
else
begin
	set @SQL= @SQL + ' select * from tbl1 where SUBJECT like ''depa'' or SUBJECT = ''depa'''
end  
print @sql
exec (@sql)

Open in new window

Hi,

Instead of trying to do a union with an if (which I don't think will work) add a where clause to each of the union statements with @str = 'test'.
This will only return the unioned data if the @Str matches your test.
see updated version.


declare @str nvarchar(20)
declare @SQL nvarchar(max)
set @str = 'test'

set @SQL=' select * from tbl1 where cname=''jame''        
Union '      
if (@str = 'test')
begin
	set @SQL= @SQL + ' select * from tbl1 where SUBJECT  like ''dest'' or SUBJECT  = ''dest'''       
end
else
begin
	set @SQL= @SQL + ' select * from tbl1 where SUBJECT like ''depa'' or SUBJECT = ''depa'''
end  

set @SQL= @SQL + ' union
select * from tbl1 where cname like ''nape''
union
select * from tbl1 where cname like ''uesed'''

print @sql
--exec (@sql)
--exec sp_executeSQL @sql

Open in new window

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
hi Riteshshah,

  It was good to see that print statement
but am not gud in sql can u plz explain ,
what u did each  step , Because i see so many red color in that query ??

Am unable to understand..?

Thanks For ur timely help

I will test ur query now , and post my result soon..!!

Meeran03:)


comment print statement and execute query like this.

declare @str nvarchar(20)
declare @SQL nvarchar(max)
set @str = 'test'

set @SQL=' select * from tbl1 where cname=''jame''        
Union '      
if (@str = 'test')
begin
        set @SQL= @SQL + ' select * from tbl1 where SUBJECT  like ''dest'' or SUBJECT  = ''dest'''       
end
else
begin
        set @SQL= @SQL + ' select * from tbl1 where SUBJECT like ''depa'' or SUBJECT = ''depa'''
end  

set @SQL= @SQL + ' union
select * from tbl1 where cname like ''nape''
union
select * from tbl1 where cname like ''uesed'''

--print @sql
--exec (@sql)
exec sp_executeSQL @sql

Open in new window

I did nothing but generate dynamic SQL in one variable named @SQL, which can be executed either by sp_executeSQL or by EXEC. nothing much. try to run the query and let me know.
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
can you try this one which does not involve dynamic SQL:
declare @str nvarchar(20) 
set @str = 'test' 
select * from tbl1 where cname='jame'         
Union        
select * from tbl1 where SUBJECT  like case @str when 'test' then 'dest' else 'depa' end 
union 
select * from tbl1 where cname like 'nape' 
union 
select * from tbl1 where cname like 'uesed'

Open in new window

Author

Commented:
Hi ritestshah,
 
 I wud like to order my result , is it possible to do  this way ...
  I get this error , when i try this way ....
 
 Msg 156, Level 15, State 1, Line 9
 Incorrect syntax near the keyword 'set'.
 Msg 102, Level 15, State 1, Line 24
 Incorrect syntax near ')'.
 
 Meeran03

declare @str nvarchar(20)
declare @SQL  varchar(max)
set @str = 'test'

SELECT * FROM     
  (SELECT row_number() OVER (ORDER BY order1 desc) AS num,* from     
  (
set @SQL=' select ''1'' as order1,* from tbl1 where cname=''jame''       
Union '     
if (@str = 'test')
begin
    set @SQL= @SQL + ' select ''2'' as order1,* from tbl1 where SUBJECT  like ''dest'' or SUBJECT  = ''dest'''      
end
else
begin
    set @SQL= @SQL + ' select ''2'' as order1,* from tbl1 where SUBJECT like ''depa'' or SUBJECT = ''depa'''
end 

set @SQL= @SQL + ' union
select ''3'' as order1,* from tbl1 where cname like ''nape''
union
select ''4'' as order1,* from tbl1 where cname like ''uesed'''
 ) test1) test2     

print @sql

Open in new window

try this:



declare @str nvarchar(20)
declare @SQL  varchar(max)
set @str = 'test'


set @SQL='SELECT * FROM     
  (SELECT row_number() OVER (ORDER BY order1 desc) AS num,* from     
  (
   select ''1'' as order1,* from tbl1 where cname=''jame''       
Union '     
if (@str = 'test')
begin
    set @SQL= @SQL + ' select ''2'' as order1,* from tbl1 where SUBJECT  like ''dest'' or SUBJECT  = ''dest'''      
end
else
begin
    set @SQL= @SQL + ' select ''2'' as order1,* from tbl1 where SUBJECT like ''depa'' or SUBJECT = ''depa'''
end 

set @SQL= @SQL + ' union
select ''3'' as order1,* from tbl1 where cname like ''nape''
union
select ''4'' as order1,* from tbl1 where cname like ''uesed'' ) test1) test2     '
 

print @sql

Open in new window

Author

Commented:
hi riteshshah,

 It seems like i had added more issues from which what i had asked ...I had posted the same in the another question .

This is my question :
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_25096603.html

Meeran 03
ok than please check my previous answer and close this question as it had resolved the issue (errors) you mentioned above.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial