Difficult Query question: TOP 1 + UNION + GROUP BY?!

Hi,
I need to perform an SQL Query, but I'm not able to find out how. It should
do the following stuff:
I have a table (tblSMS) that contains different SMS-records (each record
with a Number, a SendDate (DateTime-field) and a Campaign).
When I receive an SMS I need to find the following record(s):
- All the records with a SendDate between now (GETDATE() ) and the last 48
hours AND the Number-Field should be equal to my input
- But: In all the records that it selects, there can't be 2 with the same
Campaign
- At least the last record which has the same Number (ORDER BY SendDate = DateTime field) should be selected, even when the SendDate is smaller than
GETDATE - 48 hours...
This query works for the first and third objectives, but not for the
second...
SELECT * FROM
(SELECT TOP 1 tblSMS.* FROM tblSMS
WHERE (Number = '1234567890')
ORDER BY SendDate DESC) X
UNION
SELECT DISTINCT tblSMS.* FROM tblSMS
WHERE ((Number = '1234567890'))
AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
ORDER BY SendDate DESC
Does anybody knows how to do this?
Thanks a lot in advance,
PieterFirst, I don't understand what you mean exactly by your second statement "In
all the records that it selects, there can't be 2 with the same Campaign".
A little more explanation, with an example of a false result, should be
given.
Second, there is nothing in the WHERE clauses of your query about the
Campaign field.
Third, I'm not sure about the usefullness of the "SELECT * FROM (subquery
here) X" statement.
S. L.
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:Oq37ACr5EHA.828@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I need to perform an SQL Query, but I'm not able to find out how. It
> should
> do the following stuff:
> I have a table (tblSMS) that contains different SMS-records (each record
> with a Number, a SendDate (DateTime-field) and a Campaign).
> When I receive an SMS I need to find the following record(s):
> - All the records with a SendDate between now (GETDATE() ) and the last 48
> hours AND the Number-Field should be equal to my input
> - But: In all the records that it selects, there can't be 2 with the same
> Campaign
> - At least the last record which has the same Number (ORDER BY SendDate => DateTime field) should be selected, even when the SendDate is smaller than
> GETDATE - 48 hours...
> This query works for the first and third objectives, but not for the
> second...
> SELECT * FROM
> (SELECT TOP 1 tblSMS.* FROM tblSMS
> WHERE (Number = '1234567890')
> ORDER BY SendDate DESC) X
> UNION
> SELECT DISTINCT tblSMS.* FROM tblSMS
> WHERE ((Number = '1234567890'))
> AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
> ORDER BY SendDate DESC
> Does anybody knows how to do this?
> Thanks a lot in advance,
> Pieter
>|||Well, my table tblSMS contains the fields "Campaign", "Number", "SendDate",
...
So when I don't want two select two records with the same Campaign I want to
say with that that there can't be 2 or more records selected thath has in
the field "Campaign" the same value.
Indeed there is nothing in the WHERE clause about the campaign-field, that's
the reason I wrote that the query works for the first and third objective,
but not for the second (the one with the Campaign-stuff).
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:OAA8v3r5EHA.2012@.TK2MSFTNGP15.phx.gbl...
> First, I don't understand what you mean exactly by your second statement
"In
> all the records that it selects, there can't be 2 with the same Campaign".
> A little more explanation, with an example of a false result, should be
> given.
> Second, there is nothing in the WHERE clauses of your query about the
> Campaign field.
> Third, I'm not sure about the usefullness of the "SELECT * FROM (subquery
> here) X" statement.
> S. L.
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:Oq37ACr5EHA.828@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > I need to perform an SQL Query, but I'm not able to find out how. It
> > should
> > do the following stuff:
> > I have a table (tblSMS) that contains different SMS-records (each record
> > with a Number, a SendDate (DateTime-field) and a Campaign).
> >
> > When I receive an SMS I need to find the following record(s):
> > - All the records with a SendDate between now (GETDATE() ) and the last
48
> > hours AND the Number-Field should be equal to my input
> > - But: In all the records that it selects, there can't be 2 with the
same
> > Campaign
> > - At least the last record which has the same Number (ORDER BY SendDate
=> > DateTime field) should be selected, even when the SendDate is smaller
than
> > GETDATE - 48 hours...
> >
> > This query works for the first and third objectives, but not for the
> > second...
> > SELECT * FROM
> > (SELECT TOP 1 tblSMS.* FROM tblSMS
> > WHERE (Number = '1234567890')
> > ORDER BY SendDate DESC) X
> > UNION
> > SELECT DISTINCT tblSMS.* FROM tblSMS
> > WHERE ((Number = '1234567890'))
> > AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
> > ORDER BY SendDate DESC
> >
> > Does anybody knows how to do this?
> >
> > Thanks a lot in advance,
> >
> > Pieter
> >
> >
>|||Unfortunately, you cannot define a query in terms of what it can NOT select.
You specify criteria that the rows must match. In this case, you need to
define what logic is used to select only 1 of many rows that match a
campaign. Since the last part of the union query you posted uses distinct,
I'm guessing that there is no real PK for the table; perhaps there is
nothing that can be relied on to uniquely identify a particular row. If so,
you may need to use a temp table along with some sort of identity column to
generate sufficient unique infomation for each row to accomplish your goal.
Once you have a way of uniquely identifying a row, you can use min, max, or
top to limit rows by campaign. Or use a cursor.
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23qUydd25EHA.824@.TK2MSFTNGP11.phx.gbl...
> Well, my table tblSMS contains the fields "Campaign", "Number",
"SendDate",
> ...
> So when I don't want two select two records with the same Campaign I want
to
> say with that that there can't be 2 or more records selected thath has in
> the field "Campaign" the same value.
> Indeed there is nothing in the WHERE clause about the campaign-field,
that's
> the reason I wrote that the query works for the first and third objective,
> but not for the second (the one with the Campaign-stuff).
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:OAA8v3r5EHA.2012@.TK2MSFTNGP15.phx.gbl...
> > First, I don't understand what you mean exactly by your second statement
> "In
> > all the records that it selects, there can't be 2 with the same
Campaign".
> > A little more explanation, with an example of a false result, should be
> > given.
> >
> > Second, there is nothing in the WHERE clauses of your query about the
> > Campaign field.
> >
> > Third, I'm not sure about the usefullness of the "SELECT * FROM
(subquery
> > here) X" statement.
> >
> > S. L.
> >
> > "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> > news:Oq37ACr5EHA.828@.TK2MSFTNGP14.phx.gbl...
> > > Hi,
> > >
> > > I need to perform an SQL Query, but I'm not able to find out how. It
> > > should
> > > do the following stuff:
> > > I have a table (tblSMS) that contains different SMS-records (each
record
> > > with a Number, a SendDate (DateTime-field) and a Campaign).
> > >
> > > When I receive an SMS I need to find the following record(s):
> > > - All the records with a SendDate between now (GETDATE() ) and the
last
> 48
> > > hours AND the Number-Field should be equal to my input
> > > - But: In all the records that it selects, there can't be 2 with the
> same
> > > Campaign
> > > - At least the last record which has the same Number (ORDER BY
SendDate
> => > > DateTime field) should be selected, even when the SendDate is smaller
> than
> > > GETDATE - 48 hours...
> > >
> > > This query works for the first and third objectives, but not for the
> > > second...
> > > SELECT * FROM
> > > (SELECT TOP 1 tblSMS.* FROM tblSMS
> > > WHERE (Number = '1234567890')
> > > ORDER BY SendDate DESC) X
> > > UNION
> > > SELECT DISTINCT tblSMS.* FROM tblSMS
> > > WHERE ((Number = '1234567890'))
> > > AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
> > > ORDER BY SendDate DESC
> > >
> > > Does anybody knows how to do this?
> > >
> > > Thanks a lot in advance,
> > >
> > > Pieter
> > >
> > >
> >
> >
>|||Pieter,
I think this may work for you. The WITH TIES is a trick that
uses the CASE expression in the ORDER BY clause to select
all records within the last two days as tied, but only a single
older record if there are no recent ones. Give it a try, even
if it looks wrong at first!
So this identifies the most recent 1234567890 row for
each Campaign value, regardless of date, then returns to you
only the top 1 with ties, which is either all results within the
last 2 days, or the one most recent result from earlier.
SELECT TOP 1 WITH TIES *
FROM tblSMS as t1
WHERE Number = '1234567890'
AND NOT EXISTS (
SELECT * FROM tblSMS as t2
WHERE t2.Number = '1234567890'
AND t2.Campaign = t1.Campaign
AND t2.SendDate > t1.SendDate
)
ORDER BY
CASE WHEN SendDate <= getdate() - 2
THEN SendDate
ELSE 0 END
GO
Steve Kass
Drew University
DraguVaso wrote:
>Hi,
>I need to perform an SQL Query, but I'm not able to find out how. It should
>do the following stuff:
>I have a table (tblSMS) that contains different SMS-records (each record
>with a Number, a SendDate (DateTime-field) and a Campaign).
>When I receive an SMS I need to find the following record(s):
>- All the records with a SendDate between now (GETDATE() ) and the last 48
>hours AND the Number-Field should be equal to my input
>- But: In all the records that it selects, there can't be 2 with the same
>Campaign
>- At least the last record which has the same Number (ORDER BY SendDate =>DateTime field) should be selected, even when the SendDate is smaller than
>GETDATE - 48 hours...
>This query works for the first and third objectives, but not for the
>second...
>SELECT * FROM
> (SELECT TOP 1 tblSMS.* FROM tblSMS
> WHERE (Number = '1234567890')
> ORDER BY SendDate DESC) X
>UNION
> SELECT DISTINCT tblSMS.* FROM tblSMS
> WHERE ((Number = '1234567890'))
> AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
> ORDER BY SendDate DESC
>Does anybody knows how to do this?
>Thanks a lot in advance,
>Pieter
>
>

Difficult Query question: TOP 1 + UNION + GROUP BY?!

Hi,
I need to perform an SQL Query, but I'm not able to find out how. It should
do the following stuff:
I have a table (tblSMS) that contains different SMS-records (each record
with a Number, a SendDate (DateTime-field) and a Campaign).
When I receive an SMS I need to find the following record(s):
- All the records with a SendDate between now (GETDATE() ) and the last 48
hours AND the Number-Field should be equal to my input
- But: In all the records that it selects, there can't be 2 with the same
Campaign
- At least the last record which has the same Number (ORDER BY SendDate =
DateTime field) should be selected, even when the SendDate is smaller than
GETDATE - 48 hours...
This query works for the first and third objectives, but not for the
second...
SELECT * FROM
(SELECT TOP 1 tblSMS.* FROM tblSMS
WHERE (Number = '1234567890')
ORDER BY SendDate DESC) X
UNION
SELECT DISTINCT tblSMS.* FROM tblSMS
WHERE ((Number = '1234567890'))
AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
ORDER BY SendDate DESC
Does anybody knows how to do this?
Thanks a lot in advance,
Pieter
First, I don't understand what you mean exactly by your second statement "In
all the records that it selects, there can't be 2 with the same Campaign".
A little more explanation, with an example of a false result, should be
given.
Second, there is nothing in the WHERE clauses of your query about the
Campaign field.
Third, I'm not sure about the usefullness of the "SELECT * FROM (subquery
here) X" statement.
S. L.
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:Oq37ACr5EHA.828@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I need to perform an SQL Query, but I'm not able to find out how. It
> should
> do the following stuff:
> I have a table (tblSMS) that contains different SMS-records (each record
> with a Number, a SendDate (DateTime-field) and a Campaign).
> When I receive an SMS I need to find the following record(s):
> - All the records with a SendDate between now (GETDATE() ) and the last 48
> hours AND the Number-Field should be equal to my input
> - But: In all the records that it selects, there can't be 2 with the same
> Campaign
> - At least the last record which has the same Number (ORDER BY SendDate =
> DateTime field) should be selected, even when the SendDate is smaller than
> GETDATE - 48 hours...
> This query works for the first and third objectives, but not for the
> second...
> SELECT * FROM
> (SELECT TOP 1 tblSMS.* FROM tblSMS
> WHERE (Number = '1234567890')
> ORDER BY SendDate DESC) X
> UNION
> SELECT DISTINCT tblSMS.* FROM tblSMS
> WHERE ((Number = '1234567890'))
> AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
> ORDER BY SendDate DESC
> Does anybody knows how to do this?
> Thanks a lot in advance,
> Pieter
>
|||Well, my table tblSMS contains the fields "Campaign", "Number", "SendDate",
...
So when I don't want two select two records with the same Campaign I want to
say with that that there can't be 2 or more records selected thath has in
the field "Campaign" the same value.
Indeed there is nothing in the WHERE clause about the campaign-field, that's
the reason I wrote that the query works for the first and third objective,
but not for the second (the one with the Campaign-stuff).
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:OAA8v3r5EHA.2012@.TK2MSFTNGP15.phx.gbl...
> First, I don't understand what you mean exactly by your second statement
"In[vbcol=seagreen]
> all the records that it selects, there can't be 2 with the same Campaign".
> A little more explanation, with an example of a false result, should be
> given.
> Second, there is nothing in the WHERE clauses of your query about the
> Campaign field.
> Third, I'm not sure about the usefullness of the "SELECT * FROM (subquery
> here) X" statement.
> S. L.
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:Oq37ACr5EHA.828@.TK2MSFTNGP14.phx.gbl...
48[vbcol=seagreen]
same[vbcol=seagreen]
=[vbcol=seagreen]
than
>
|||Unfortunately, you cannot define a query in terms of what it can NOT select.
You specify criteria that the rows must match. In this case, you need to
define what logic is used to select only 1 of many rows that match a
campaign. Since the last part of the union query you posted uses distinct,
I'm guessing that there is no real PK for the table; perhaps there is
nothing that can be relied on to uniquely identify a particular row. If so,
you may need to use a temp table along with some sort of identity column to
generate sufficient unique infomation for each row to accomplish your goal.
Once you have a way of uniquely identifying a row, you can use min, max, or
top to limit rows by campaign. Or use a cursor.
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23qUydd25EHA.824@.TK2MSFTNGP11.phx.gbl...
> Well, my table tblSMS contains the fields "Campaign", "Number",
"SendDate",
> ...
> So when I don't want two select two records with the same Campaign I want
to
> say with that that there can't be 2 or more records selected thath has in
> the field "Campaign" the same value.
> Indeed there is nothing in the WHERE clause about the campaign-field,
that's[vbcol=seagreen]
> the reason I wrote that the query works for the first and third objective,
> but not for the second (the one with the Campaign-stuff).
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:OAA8v3r5EHA.2012@.TK2MSFTNGP15.phx.gbl...
> "In
Campaign".[vbcol=seagreen]
(subquery[vbcol=seagreen]
record[vbcol=seagreen]
last[vbcol=seagreen]
> 48
> same
SendDate
> =
> than
>
|||Pieter,
I think this may work for you. The WITH TIES is a trick that
uses the CASE expression in the ORDER BY clause to select
all records within the last two days as tied, but only a single
older record if there are no recent ones. Give it a try, even
if it looks wrong at first!
So this identifies the most recent 1234567890 row for
each Campaign value, regardless of date, then returns to you
only the top 1 with ties, which is either all results within the
last 2 days, or the one most recent result from earlier.
SELECT TOP 1 WITH TIES *
FROM tblSMS as t1
WHERE Number = '1234567890'
AND NOT EXISTS (
SELECT * FROM tblSMS as t2
WHERE t2.Number = '1234567890'
AND t2.Campaign = t1.Campaign
AND t2.SendDate > t1.SendDate
)
ORDER BY
CASE WHEN SendDate <= getdate() - 2
THEN SendDate
ELSE 0 END
GO
Steve Kass
Drew University
DraguVaso wrote:

>Hi,
>I need to perform an SQL Query, but I'm not able to find out how. It should
>do the following stuff:
>I have a table (tblSMS) that contains different SMS-records (each record
>with a Number, a SendDate (DateTime-field) and a Campaign).
>When I receive an SMS I need to find the following record(s):
>- All the records with a SendDate between now (GETDATE() ) and the last 48
>hours AND the Number-Field should be equal to my input
>- But: In all the records that it selects, there can't be 2 with the same
>Campaign
>- At least the last record which has the same Number (ORDER BY SendDate =
>DateTime field) should be selected, even when the SendDate is smaller than
>GETDATE - 48 hours...
>This query works for the first and third objectives, but not for the
>second...
>SELECT * FROM
> (SELECT TOP 1 tblSMS.* FROM tblSMS
> WHERE (Number = '1234567890')
> ORDER BY SendDate DESC) X
>UNION
> SELECT DISTINCT tblSMS.* FROM tblSMS
> WHERE ((Number = '1234567890'))
> AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
> ORDER BY SendDate DESC
>Does anybody knows how to do this?
>Thanks a lot in advance,
>Pieter
>
>

different results with the same query

I have SELECT query which include 2 UNION operators.
SELECT ....
UNION
(select....
UNION
selec...)as t1...
If I execute this query in query analyzer, I get 7 records as a result.
If I create new SP and copy the same query into this new STORED PROCEDURE
and execute this procedure,
I get 13 records as a result.
How is that possible that absolutly the same sintax return diferent results?
Both queries are executed on the same database with sa account.
It seems to me that if I execute this query in query analyzer, it return
results as without the union.
Does anybody know why?
This is the first time for me experiencing something like this in 7 years.
Regards,
Simonno It should not. check somebody might have updated mean while.
Post complete script if you find problem
--
Regards
R.D
--Knowledge gets doubled when shared
"simon" wrote:

> I have SELECT query which include 2 UNION operators.
> SELECT ....
> UNION
> (select....
> UNION
> selec...)as t1...
> If I execute this query in query analyzer, I get 7 records as a result.
> If I create new SP and copy the same query into this new STORED PROCEDURE
> and execute this procedure,
> I get 13 records as a result.
> How is that possible that absolutly the same sintax return diferent result
s?
> Both queries are executed on the same database with sa account.
> It seems to me that if I execute this query in query analyzer, it return
> results as without the union.
> Does anybody know why?
> This is the first time for me experiencing something like this in 7 years.
> Regards,
> Simon
>
>|||nobody changed anything.
I execut the procedure in query analyzer: exec dbo.test
OR I execute script below in query analyzer(without "create procedure
dbo.test as" text)
and I get different results.
Amazing, isn't it? Both scripts are executed in the same query analyzer
window with the Sa account butt different result.
Is there some bug or what?
I also try to execute the script with SQL2005 SQL server managment studio
but the same result.
The script is:
CREATE PROCEDURE dbo.test
AS
declare @.idIzdelka varchar(20),@.idDrzave char(3)
set @.idIzdelka='I2314'
set @.idDrzave='CZK'
SELECT
Tk.st_nar_dob,nerazdeljena=sum(Tk.nerazdeljena),razdeljena=sum(Tk.razdeljena
),skupaj=sum(Tk.skupaj),
dobava=(SELECT rok_dobave FROM narDobIzdNed WHERE st_nar_dob=tk.st_nar_dob
AND izd_id=@.idIzdelka)FROM
(SELECT
st_nar_dob=T2.navisionId,sum(isnull(T2.navKolicina,0))-sum(T2.nar_kolicina)-
sum(isnull(T2.zakljucenaKol,0))
as nerazdeljena,
sum(T2.nar_kolicina)+sum(isnull(T2.zakljucenaKol,0)) as
razdeljena,sum(isnull(T2.navKolicina,0)) as skupaj FROM
(SELECT T1.*,zalogaNavision=(SELECT kolicina FROM skladisceIzdelek WHERE
navisionId=t1.navisionId
AND idIzdelka=@.idIzdelka),
navKolicina=(SELECT isnull(kolicina_dej,kolicina_nar) FROM narDobIzdNed
WHERE st_nar_dob=t1.navisionId AND izd_id=@.idIzdelka),
zakljucenaKol=(SELECT sum(nar_kolicina) FROM narociloIzdelek WHERE
navisionID=t1.navisionId AND izd_id=@.idIzdelka and izd_zakljucen=1)
FROM
(SELECT sum(n2.nar_kolicina) as nar_Kolicina,n2.navisionId FROM
(select n1.nar_id,n1.izd_id,max(n1.datum_spremembe) as datumSpremembe FROM
narociloIzdelek n1
GROUP BY n1.nar_id,n1.izd_id)AS T1
INNER JOIN narociloIzdelek n2 ON T1.nar_id=n2.nar_id AND T1.izd_id=n2.izd_id
AND T1.datumSpremembe=n2.datum_spremembe
INNER JOIN narocilo n ON n2.nar_id=n.nar_id INNER JOIN skladisce s ON
n.nar_skladisce_id=s.skladisce_id
INNER JOIN uporabnik u ON u.up_id=n.nar_up_id
WHERE n.nar_status=2 AND n2.izd_zakljucen=0 AND n2.izd_id=@.idIzdelka and
n2.nar_kolicina<>0
and not (n2.navisionId is null OR n2.navisionId='')AND
s.skladisce_drzava_id=@.idDrzave
GROUP BY n2.navisionId)as T1 )as T2 GROUP BY T2.navisionId
UNION
SELECT DISTINCT
T1. st_nar_dob,nerazdeljena=isnull(kolicina_
dej,kolicina_nar),0 as
razdeljena,
skupaj=isnull(kolicina_dej,kolicina_nar)
from--vse, ki so pod sprosti
(select st_nar_dob from narDobIzdNed WHERE izd_id=@.idIzdelka and (naZalogo
is null OR odobri=1)
UNION --vse, ki so obviseli z navision id-jem in so prav tako pod sprosti
select st_nar_dob=navisionID from skladisceIzdelek WHERE navisionID is not
null AND idSkladisca=4 AND idIzdelka=@.idIzdelka)
as T1 INNER JOIN narDobIzdNed n ON T1.st_nar_dob=n.st_nar_dob AND
n.izd_id=@.idIzdelka
WHERE T1.st_nar_dob not in(SELECT n2.navisionID FROM
(select n1.nar_id,n1.izd_id,max(n1.datum_spremembe) as datumSpremembe FROM
narociloIzdelek n1
WHERE n1.izd_id=@.idIzdelka GROUP BY n1.nar_id,n1.izd_id)AS T1
INNER JOIN narociloIzdelek n2 ON T1.nar_id=n2.nar_id AND T1.izd_id=n2.izd_id
AND T1.datumSpremembe=n2.datum_spremembe WHERE n2.izd_id=@.idIzdelka))as Tk
GROUP BY Tk.st_nar_dob
regards,S
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:726AB188-438B-4DBA-8CE6-DA4E1C35CEC1@.microsoft.com...
> no It should not. check somebody might have updated mean while.
> Post complete script if you find problem
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "simon" wrote:
>|||-- Check the settings for ansi nulls for the two stored procedures
-- which I believe are set when the stored procedure are created.
-- This could affect your results.
select name, OBJECTPROPERTY ( id , 'ExecIsAnsiNullsOn' ) as
ExecIsAnsiNullsOn
from sysobjects
where OBJECTPROPERTY ( id , 'IsProcedure' )=1
and name in ('test','?')|||thank you, that was the right answer.
In query analyzer is set to ON.
But I don't understand, I don't compare NULL=NULL anywhere in my query.
Does that UNION operator do internally?
I thought that SET ANSI NULLS ON or OFF affects only comparisations.
Regards,
Simon
<markc600@.hotmail.com> wrote in message
news:1128425201.376908.171720@.o13g2000cwo.googlegroups.com...
> -- Check the settings for ansi nulls for the two stored procedures
> -- which I believe are set when the stored procedure are created.
> -- This could affect your results.
> select name, OBJECTPROPERTY ( id , 'ExecIsAnsiNullsOn' ) as
> ExecIsAnsiNullsOn
> from sysobjects
> where OBJECTPROPERTY ( id , 'IsProcedure' )=1
> and name in ('test','?')
>|||On Tue, 4 Oct 2005 14:33:11 +0200, simon wrote:

>thank you, that was the right answer.
>In query analyzer is set to ON.
>But I don't understand, I don't compare NULL=NULL anywhere in my query.
>Does that UNION operator do internally?
>I thought that SET ANSI NULLS ON or OFF affects only comparisations.
Hi Simon,
Your query has many comparisons between columns, such as
WHERE st_nar_dob=t1.navisionId (random snippet)
If both st_nar_dob and t1.navisionId can hold NULL, then there will be
combinations that test as equal with SET ANSI_NULLS OFF, but unequal
with standard ANSI null handling.
As far as I know, both explicit DISTINCT and the implied DISTINCT in the
UNION operator are not affected by ANSI_NULLS setting - but I'm not 100%
sure.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)