Incorrect syntax near the keyword 'UNION'. ??

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
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
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> 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
> 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
>
|||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.
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> 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
> 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
>
|||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
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> 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
> 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
>
|||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.)
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> 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
> 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
>
|||Thansk guys!! It works fine now!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uqB6AMzxEHA.2036@.TK2MSFTNGP12.phx.gbl...
> 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
> --
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:u1zDVHzxEHA.4040@.TK2MSFTNGP11.phx.gbl...
> (SendDate
> the
> yet
>