Discussion:
Incorrect syntax near the keyword 'UNION'. ????
(too old to reply)
DraguVaso
2004-11-10 14:33:55 UTC
Permalink
Hi,

Why does this query give this error?
SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE (Number = '+32479990284') OR (Number = '0479990284')
ORDER BY SendDate DESC
UNION
SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND (SendDate
DATEADD(hh, 48 ,GETDATE() ))
Error:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'UNION'.

What I need to do is Select all the records (tblSMS) with SendDate from the
last 48 hours, and I have also to select the Last one (in case it isn't yet
in the last 48 hours).

Does anybody knwos what goes wrong?

If I try the two query's separetly they work fine...

Thanks a lot,

Pieter
Adam Machanic
2004-11-10 14:37:06 UTC
Permalink
You can't have an ORDER BY before the UNION. Put the ORDER BY after the
final SELECT.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
Post by DraguVaso
Hi,
Why does this query give this error?
SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE (Number = '+32479990284') OR (Number = '0479990284')
ORDER BY SendDate DESC
UNION
SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND (SendDate
DATEADD(hh, 48 ,GETDATE() ))
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'UNION'.
What I need to do is Select all the records (tblSMS) with SendDate from the
last 48 hours, and I have also to select the Last one (in case it isn't yet
in the last 48 hours).
Does anybody knwos what goes wrong?
If I try the two query's separetly they work fine...
Thanks a lot,
Pieter
Armando Prato
2004-11-10 14:38:06 UTC
Permalink
An Order By can only be used to determine the output of the entire result
set when you use
UNION. You can't use it with the individual queries.
Post by DraguVaso
Hi,
Why does this query give this error?
SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE (Number = '+32479990284') OR (Number = '0479990284')
ORDER BY SendDate DESC
UNION
SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND (SendDate
DATEADD(hh, 48 ,GETDATE() ))
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'UNION'.
What I need to do is Select all the records (tblSMS) with SendDate from the
last 48 hours, and I have also to select the Last one (in case it isn't yet
in the last 48 hours).
Does anybody knwos what goes wrong?
If I try the two query's separetly they work fine...
Thanks a lot,
Pieter
Adam Machanic
2004-11-10 14:38:52 UTC
Permalink
By the way, that will totally distroy your TOP 1 logic... but here's a very
simple solution:

SELECT *
FROM
(SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE (Number = '+32479990284') OR (Number = '0479990284')
ORDER BY SendDate DESC) x

UNION

SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE ((Number = '+32479990284') OR (Number = '0479990284'))
AND (SendDate > DATEADD(hh, 48 ,GETDATE() ))
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
Post by DraguVaso
Hi,
Why does this query give this error?
SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE (Number = '+32479990284') OR (Number = '0479990284')
ORDER BY SendDate DESC
UNION
SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND (SendDate
DATEADD(hh, 48 ,GETDATE() ))
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'UNION'.
What I need to do is Select all the records (tblSMS) with SendDate from the
last 48 hours, and I have also to select the Last one (in case it isn't yet
in the last 48 hours).
Does anybody knwos what goes wrong?
If I try the two query's separetly they work fine...
Thanks a lot,
Pieter
DraguVaso
2004-11-10 14:46:40 UTC
Permalink
Thansk guys!! It works fine now!
Post by Adam Machanic
By the way, that will totally distroy your TOP 1 logic... but here's a very
SELECT *
FROM
(SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE (Number = '+32479990284') OR (Number = '0479990284')
ORDER BY SendDate DESC) x
UNION
SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE ((Number = '+32479990284') OR (Number = '0479990284'))
AND (SendDate > DATEADD(hh, 48 ,GETDATE() ))
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
Post by DraguVaso
Hi,
Why does this query give this error?
SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE (Number = '+32479990284') OR (Number = '0479990284')
ORDER BY SendDate DESC
UNION
SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND
(SendDate
Post by DraguVaso
DATEADD(hh, 48 ,GETDATE() ))
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'UNION'.
What I need to do is Select all the records (tblSMS) with SendDate from
the
Post by DraguVaso
last 48 hours, and I have also to select the Last one (in case it isn't
yet
Post by DraguVaso
in the last 48 hours).
Does anybody knwos what goes wrong?
If I try the two query's separetly they work fine...
Thanks a lot,
Pieter
Aaron [SQL Server MVP]
2004-11-10 14:37:51 UTC
Permalink
You can't put ORDER BY inside individual parts of the UNION. Move it to the
end...
--
http://www.aspfaq.com/
(Reverse address to reply.)
Post by DraguVaso
Hi,
Why does this query give this error?
SELECT TOP 1 tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE (Number = '+32479990284') OR (Number = '0479990284')
ORDER BY SendDate DESC
UNION
SELECT DISTINCT tblSMS.*, tblCampaigns.Mail
FROM tblSMS LEFT JOIN tblCampaigns ON tblSms.Campaign =
tblCampaigns.Campaign
WHERE ((Number = '+32479990284') OR (Number = '0479990284')) AND (SendDate
DATEADD(hh, 48 ,GETDATE() ))
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'UNION'.
What I need to do is Select all the records (tblSMS) with SendDate from the
last 48 hours, and I have also to select the Last one (in case it isn't yet
in the last 48 hours).
Does anybody knwos what goes wrong?
If I try the two query's separetly they work fine...
Thanks a lot,
Pieter
Loading...