Incorrect Syntax Near UNION - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Incorrect Syntax Near UNION

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-07 : 10:10:04

JAdauto writes "I have the following SQL statement using a UNION to join two seperate statements. Each statement produces the correct results if run seperately. However, I get "Incorrect Syntax near the keyword UNION" and am unable to execute the statement. Any help would be greatly appreciated. JAdauto

IF (SELECT tdh.DiscountID 
	FROM TicketDetailHistory AS tdh 
	WHERE tdh.TicketHeaderID = 402 
	AND tdh.DiscountAmount <> 0) IS NOT NULL 
BEGIN 
	IF (SELECT tdh.DiscountID 
	FROM TicketDetailHistory AS tdh 
	WHERE tdh.TicketHeaderID  = 402
	AND tdh.DiscountAmount <> 0)<> 0 
		BEGIN 
		SELECT tdh.DiscountAmount AS Extension, 
			0 AS GratutityTotal,
			0 AS TaxTotal, 
			d.RevCode as RevCode
			FROM TicketDetailHistory AS tdh 
			INNER JOIN Discount AS d 
			ON tdh.DiscountID = d.ID 
			WHERE tdh.TicketHeaderID = 402
			AND tdh.DiscountAmount <> 0 
		END 
	ELSE 
		BEGIN
		SELECT tdh.DiscountAmount AS Extension, 
			0 AS GratutityTotal, 
			0 AS TaxTotal, 
			l.CustomDiscountRevCode  as RevCode
			FROM TicketDetailHistory AS tdh 
			INNER JOIN TicketHeaderHistory AS thh 
			ON tdh.TicketHeaderId = thh.TicketHeaderID
			INNER JOIN Location AS l 
			ON thh.LocationID = l.ID 
			WHERE tdh.TicketHeaderID =  402
			AND tdh.DiscountAmount <> 0 
		END 
END

UNION

IF  (SELECT tdh.DiscountID 
	FROM TicketDiscountHistory AS tdh 
	WHERE  tdh.TicketHeaderID  = 402
 	AND tdh.DiscountAmount IS NOT NULL )  <>  0 
BEGIN 
	SELECT tdh.DiscountAmount as Extension, 
	0 AS GratutityTotal, 
	0 AS TaxTotal, 
	d.RevCode as RevCode
	FROM TicketDiscountHistory AS tdh 
	INNER JOIN Discount AS d
	ON tdh.DiscountID = d.ID 
	WHERE tdh.TicketHeaderID = 402
	AND tdh.DiscountAmount IS NOT NULL 
END 
ELSE 
BEGIN 
	SELECT tdh.DiscountAmount as Extension,
	0 AS GratutityTotal, 
	0 AS TaxTotal, 
	l.CustomDiscountRevCode  as RevCode
	FROM TicketDiscountHistory AS tdh 
	INNER JOIN TicketHeaderHistory AS thh 
	ON tdh.TicketHeaderId = thh.TicketHeaderID 
	INNER JOIN Location as l 
	ON thh.LocationID = l.ID 
	WHERE tdh.TicketHeaderID = 402
	AND tdh.DiscountAmount 
	IS NOT NULL 
END"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-07 : 10:16:29

union is used as part of the select syntax

select *
from tbl
union
select *
from tbl

to do what you are trying to do either use a temp table, generate multiple statements withinn the IF clauses or incorporate the conditions in the where clause.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources