I run the script below in sql2005 and am receiving the error msg 209. Do I
need a prefix to order by either STATE, TAXAMNT, or SUBTOTAL because I want
them displyed in front.
Thank you.
Charlie
select STATE, TAXAMNT, SUBTOTAL, *
from sop30200
where state in('NJ','NY','WA','CO','CA','ME','OK','VA','PA','T N')
AND SOPTYPE IN (3,4)
AND DOCDATE between '2007-02-01 00:00:00.000' and '2007-02-28 00:00:00.000'
--AND TAXEXMT1 = ' '
AND SUBTOTAL <> 0
--AND SOPNUMBE LIKE 'NCINV%'
AND VOIDSTTS = 0
AND SOPNUMBE NOT IN
(SELECT INVNO
FROM VERTEX..REGPRERETURNSTBL
WHERE COMPCD = 'ABS'
and invno between '20070201' and '20070228')
--AND SHIPTOGEOCODE LIKE '31%')
ORDER BY STATE DESC
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'STATE'.
While there is no actual ambiguity, the parser is not so sure.
Your select list contains the STATE column twice: once
explicitly as the first column and once again in the *. The
parser does not know whether you want to sort on the
source column sop30200.STATE or on the output column
STATE. They are the same in this query, but there are
queries where they could be different because some
output expression or column is aliased as STATE. The
parser does not try to figure out the meaning of the query.
There are two solutions. One is to specify
ORDER BY sop30200.STATE
and the other is to list all the columns you want in the output
separately and one each, not using the * notation. The second
solution is the better one, at least in production, because neither
* nor duplicate columns in the output are typically a good idea.
But if this is just a quick ad hoc query, the first solution is fine.
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
chas2006 wrote:
>I run the script below in sql2005 and am receiving the error msg 209. Do I
>need a prefix to order by either STATE, TAXAMNT, or SUBTOTAL because I want
>them displyed in front.
>Thank you.
>Charlie
>select STATE, TAXAMNT, SUBTOTAL, *
>from sop30200
>where state in('NJ','NY','WA','CO','CA','ME','OK','VA','PA','T N')
>AND SOPTYPE IN (3,4)
>AND DOCDATE between '2007-02-01 00:00:00.000' and '2007-02-28 00:00:00.000'
>--AND TAXEXMT1 = ' '
>AND SUBTOTAL <> 0
>--AND SOPNUMBE LIKE 'NCINV%'
>AND VOIDSTTS = 0
>AND SOPNUMBE NOT IN
>(SELECT INVNO
>FROM VERTEX..REGPRERETURNSTBL
>WHERE COMPCD = 'ABS'
>and invno between '20070201' and '20070228')
>--AND SHIPTOGEOCODE LIKE '31%')
>ORDER BY STATE DESC
>
>Msg 209, Level 16, State 1, Line 1
>Ambiguous column name 'STATE'.
>
Showing posts with label clause. Show all posts
Showing posts with label clause. Show all posts
Tuesday, March 6, 2012
"not in" clause doesn't work
Is there anything that would cause a "not in" clause to not work in SQL2000?
DETAIL: I have 2 simple tables called "People" and "PeopleImport". Each of
these tables has a "flxID" field of type varchar(10). There is a record in
the "PeopleImport" table with a "flxID" value of '12345' and no records in
the People table with that value (I deleted it). Running the following
query does not return any records but (I think) should:
select flxid from peopleimport where flxid not in (select flxid from people)
I have also tried the following diviations:
select flxid from peopleimport where convert(char(5), flxid) not in (select
convert(char(5), flxid) from people)
select flxid from peopleimport where rtrim(convert(char(5), flxid)) not in
(select rtim(convert(char(5), flxid)) from people)
I hope someone has some thoughts on this because I am out of ideas.
Schoo
On Thu, 23 Dec 2004 15:54:46 -0600, Schoo wrote:
>Is there anything that would cause a "not in" clause to not work in SQL2000?
Hi Schoo,
None that I'm aware of, but many people get tripped by the way NULL values
are treated in IN and NOT IN clauses. Remember that NULL is (to the
database) an indicator for "unknown value".
When you deal with NULLs, you start using three-valued logic: 'true' and
'false' get company of their little brother 'maybe'. A row is only
included in the query if the where clause evaluates to 'true'; it is
discarded on 'false' and on 'maybe'.
For IN, the "strange" behaviour doesn't cause apparently wrong results:
* Is the value 15 IN the list {14, 17} --> NO (false)
* Is the value 15 IN the list {15, unknown value, 17} --> YES (true)
* Is the value 15 IN the list {14, unknown value, 17} --> MAYBE
The row will only be included in the query in the second example.
For NOT IN, the same logic yields results that "look" unlogical:
* Is the value 15 NOT IN the list {14, 17} --> YES (true)
* Is the value 15 NOT IN the list {15, unknown value, 17} --> NO (false)
* Is the value 15 NOT IN the list {14, unknown value, 17} --> MAYBE
This time, only the first case will return a row. The NOT IN with a NULL
value in the subquery can only result in NO or MAYBE and will therefor
automatically exclude all rows from the result.
Two ways to fix it:
Recommended: use NOT EXISTS instead of NOT IN. I never use NOT IN, except
when testing against a fixed list of constants!
SELECT flxid
FROM peopleimport AS pi
WHERE NOT EXISTS (SELECT *
FROM people AS p
WHERE p.flxid = pi.flxid)
Quick fix (not recommended, as you'll keep running into this as long as
you have subqueries with NOT IN): include WHERE flxid IS NOT NULL in the
subquery.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
Thank you for your input. You are correct, dealing with the nulls in this
way works. I will incorporate these changes in the future.
Scott
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:mkhms0hfp9hjdrpbtsp9v85vkkb1ksrgt7@.4ax.com... [vbcol=seagreen]
> On Thu, 23 Dec 2004 15:54:46 -0600, Schoo wrote:
SQL2000?
> Hi Schoo,
> None that I'm aware of, but many people get tripped by the way NULL values
> are treated in IN and NOT IN clauses. Remember that NULL is (to the
> database) an indicator for "unknown value".
> When you deal with NULLs, you start using three-valued logic: 'true' and
> 'false' get company of their little brother 'maybe'. A row is only
> included in the query if the where clause evaluates to 'true'; it is
> discarded on 'false' and on 'maybe'.
> For IN, the "strange" behaviour doesn't cause apparently wrong results:
> * Is the value 15 IN the list {14, 17} --> NO (false)
> * Is the value 15 IN the list {15, unknown value, 17} --> YES (true)
> * Is the value 15 IN the list {14, unknown value, 17} --> MAYBE
> The row will only be included in the query in the second example.
> For NOT IN, the same logic yields results that "look" unlogical:
> * Is the value 15 NOT IN the list {14, 17} --> YES (true)
> * Is the value 15 NOT IN the list {15, unknown value, 17} --> NO (false)
> * Is the value 15 NOT IN the list {14, unknown value, 17} --> MAYBE
> This time, only the first case will return a row. The NOT IN with a NULL
> value in the subquery can only result in NO or MAYBE and will therefor
> automatically exclude all rows from the result.
> Two ways to fix it:
> Recommended: use NOT EXISTS instead of NOT IN. I never use NOT IN, except
> when testing against a fixed list of constants!
> SELECT flxid
> FROM peopleimport AS pi
> WHERE NOT EXISTS (SELECT *
> FROM people AS p
> WHERE p.flxid = pi.flxid)
> Quick fix (not recommended, as you'll keep running into this as long as
> you have subqueries with NOT IN): include WHERE flxid IS NOT NULL in the
> subquery.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
DETAIL: I have 2 simple tables called "People" and "PeopleImport". Each of
these tables has a "flxID" field of type varchar(10). There is a record in
the "PeopleImport" table with a "flxID" value of '12345' and no records in
the People table with that value (I deleted it). Running the following
query does not return any records but (I think) should:
select flxid from peopleimport where flxid not in (select flxid from people)
I have also tried the following diviations:
select flxid from peopleimport where convert(char(5), flxid) not in (select
convert(char(5), flxid) from people)
select flxid from peopleimport where rtrim(convert(char(5), flxid)) not in
(select rtim(convert(char(5), flxid)) from people)
I hope someone has some thoughts on this because I am out of ideas.
Schoo
On Thu, 23 Dec 2004 15:54:46 -0600, Schoo wrote:
>Is there anything that would cause a "not in" clause to not work in SQL2000?
Hi Schoo,
None that I'm aware of, but many people get tripped by the way NULL values
are treated in IN and NOT IN clauses. Remember that NULL is (to the
database) an indicator for "unknown value".
When you deal with NULLs, you start using three-valued logic: 'true' and
'false' get company of their little brother 'maybe'. A row is only
included in the query if the where clause evaluates to 'true'; it is
discarded on 'false' and on 'maybe'.
For IN, the "strange" behaviour doesn't cause apparently wrong results:
* Is the value 15 IN the list {14, 17} --> NO (false)
* Is the value 15 IN the list {15, unknown value, 17} --> YES (true)
* Is the value 15 IN the list {14, unknown value, 17} --> MAYBE
The row will only be included in the query in the second example.
For NOT IN, the same logic yields results that "look" unlogical:
* Is the value 15 NOT IN the list {14, 17} --> YES (true)
* Is the value 15 NOT IN the list {15, unknown value, 17} --> NO (false)
* Is the value 15 NOT IN the list {14, unknown value, 17} --> MAYBE
This time, only the first case will return a row. The NOT IN with a NULL
value in the subquery can only result in NO or MAYBE and will therefor
automatically exclude all rows from the result.
Two ways to fix it:
Recommended: use NOT EXISTS instead of NOT IN. I never use NOT IN, except
when testing against a fixed list of constants!
SELECT flxid
FROM peopleimport AS pi
WHERE NOT EXISTS (SELECT *
FROM people AS p
WHERE p.flxid = pi.flxid)
Quick fix (not recommended, as you'll keep running into this as long as
you have subqueries with NOT IN): include WHERE flxid IS NOT NULL in the
subquery.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
Thank you for your input. You are correct, dealing with the nulls in this
way works. I will incorporate these changes in the future.
Scott
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:mkhms0hfp9hjdrpbtsp9v85vkkb1ksrgt7@.4ax.com... [vbcol=seagreen]
> On Thu, 23 Dec 2004 15:54:46 -0600, Schoo wrote:
SQL2000?
> Hi Schoo,
> None that I'm aware of, but many people get tripped by the way NULL values
> are treated in IN and NOT IN clauses. Remember that NULL is (to the
> database) an indicator for "unknown value".
> When you deal with NULLs, you start using three-valued logic: 'true' and
> 'false' get company of their little brother 'maybe'. A row is only
> included in the query if the where clause evaluates to 'true'; it is
> discarded on 'false' and on 'maybe'.
> For IN, the "strange" behaviour doesn't cause apparently wrong results:
> * Is the value 15 IN the list {14, 17} --> NO (false)
> * Is the value 15 IN the list {15, unknown value, 17} --> YES (true)
> * Is the value 15 IN the list {14, unknown value, 17} --> MAYBE
> The row will only be included in the query in the second example.
> For NOT IN, the same logic yields results that "look" unlogical:
> * Is the value 15 NOT IN the list {14, 17} --> YES (true)
> * Is the value 15 NOT IN the list {15, unknown value, 17} --> NO (false)
> * Is the value 15 NOT IN the list {14, unknown value, 17} --> MAYBE
> This time, only the first case will return a row. The NOT IN with a NULL
> value in the subquery can only result in NO or MAYBE and will therefor
> automatically exclude all rows from the result.
> Two ways to fix it:
> Recommended: use NOT EXISTS instead of NOT IN. I never use NOT IN, except
> when testing against a fixed list of constants!
> SELECT flxid
> FROM peopleimport AS pi
> WHERE NOT EXISTS (SELECT *
> FROM people AS p
> WHERE p.flxid = pi.flxid)
> Quick fix (not recommended, as you'll keep running into this as long as
> you have subqueries with NOT IN): include WHERE flxid IS NOT NULL in the
> subquery.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Friday, February 24, 2012
"IS NOT NULL" is not working
We have data in datetime field and in the simple select statement where
clause "where dateofbusiness is not null" no data is returned. This
works in our other databases. This is a simple select with no
aggregation functions such as count(*) and no group bys. No joins.
We checked for table corruption and rebuilt the table and indexes.
I'm completely at a loss.
Anybody run into this?Run the following and post the results:
select
dateofbusiness
, count (*)
from
MyTable
group by
dateofbusiness
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104271245.345995.62690@.f14g2000cwb.googlegroups.com...
We have data in datetime field and in the simple select statement where
clause "where dateofbusiness is not null" no data is returned. This
works in our other databases. This is a simple select with no
aggregation functions such as count(*) and no group bys. No joins.
We checked for table corruption and rebuilt the table and indexes.
I'm completely at a loss.
Anybody run into this?|||Try,
where ISDATE(dateofbusiness) <> 0
--
Message posted via http://www.sqlmonster.com|||2003-03-25 00:00:00.000 15
2003-03-26 00:00:00.000 17
2003-03-27 00:00:00.000 73
2003-03-28 00:00:00.000 9
2003-04-11 00:00:00.000 18
2003-04-14 00:00:00.000 131
2003-04-15 00:00:00.000 11
2003-04-25 00:00:00.000 2
2003-04-28 00:00:00.000 18
2003-05-06 00:00:00.000 17
2003-05-08 00:00:00.000 16
2003-05-12 00:00:00.000 23
2003-05-13 00:00:00.000 80
2003-05-16 00:00:00.000 27
2003-05-19 00:00:00.000 15
2003-06-05 00:00:00.000 23
2003-06-16 00:00:00.000 11942
2003-06-17 00:00:00.000 12038
2003-06-18 00:00:00.000 12166
2003-06-19 00:00:00.000 12307
2003-06-20 00:00:00.000 12502
2003-06-21 00:00:00.000 11840
2003-06-22 00:00:00.000 11341
2003-06-23 00:00:00.000 11784
2003-06-24 00:00:00.000 12055
2003-06-25 00:00:00.000 11905
2003-06-26 00:00:00.000 12088
2003-06-27 00:00:00.000 12451
2003-06-28 00:00:00.000 11956
2003-06-29 00:00:00.000 11303
2003-06-30 00:00:00.000 12072
2003-07-01 00:00:00.000 11545
2003-07-02 00:00:00.000 11604
2003-07-03 00:00:00.000 11857
2003-07-04 00:00:00.000 636
2003-07-05 00:00:00.000 11045
2003-07-06 00:00:00.000 10303
2003-07-07 00:00:00.000 11324
2003-07-08 00:00:00.000 11002
2003-07-09 00:00:00.000 11446
2003-07-10 00:00:00.000 11486
2003-07-11 00:00:00.000 12115
2003-07-12 00:00:00.000 11240
2003-07-13 00:00:00.000 10871
2003-07-14 00:00:00.000 11395
2003-07-15 00:00:00.000 11563
2003-07-16 00:00:00.000 19
2003-08-27 00:00:00.000 26
2003-08-28 00:00:00.000 25
2003-09-16 00:00:00.000 805
2003-09-17 00:00:00.000 806
2003-09-18 00:00:00.000 783
2003-09-19 00:00:00.000 365
2003-09-20 00:00:00.000 312
2003-09-21 00:00:00.000 318
2003-09-22 00:00:00.000 334
2003-09-23 00:00:00.000 347
2003-09-24 00:00:00.000 358
2003-09-25 00:00:00.000 390
2003-09-26 00:00:00.000 388
2003-09-27 00:00:00.000 354
2003-09-28 00:00:00.000 340
2003-09-29 00:00:00.000 334
2003-09-30 00:00:00.000 367
2003-10-02 00:00:00.000 2
2003-10-03 00:00:00.000 7
2003-10-06 00:00:00.000 19
2003-10-08 00:00:00.000 13
2003-10-22 00:00:00.000 15
2003-12-01 00:00:00.000 82068
2003-12-02 00:00:00.000 83850
2003-12-03 00:00:00.000 84996
2003-12-04 00:00:00.000 85926
2003-12-05 00:00:00.000 89212
2003-12-06 00:00:00.000 86304
2003-12-07 00:00:00.000 75057
2003-12-08 00:00:00.000 83997
2003-12-09 00:00:00.000 83979
2003-12-10 00:00:00.000 86040
2003-12-11 00:00:00.000 87731
2003-12-12 00:00:00.000 91075
2003-12-13 00:00:00.000 88179
2003-12-14 00:00:00.000 73306
2003-12-15 00:00:00.000 161373
2003-12-16 00:00:00.000 94041
2003-12-17 00:00:00.000 97228
2003-12-18 00:00:00.000 99609
2003-12-19 00:00:00.000 102561
2003-12-20 00:00:00.000 98621
2003-12-21 00:00:00.000 85470
2003-12-22 00:00:00.000 102115
2003-12-23 00:00:00.000 103167
2003-12-24 00:00:00.000 50463
2003-12-26 00:00:00.000 95627
2003-12-27 00:00:00.000 93059
2003-12-28 00:00:00.000 87425
2003-12-29 00:00:00.000 96371
2003-12-30 00:00:00.000 97959
2003-12-31 00:00:00.000 81578
2004-02-01 00:00:00.000 384
2004-02-06 00:00:00.000 26
2004-02-12 00:00:00.000 52
2004-02-17 00:00:00.000 22
2004-02-19 00:00:00.000 17
2004-02-20 00:00:00.000 50
2004-02-21 00:00:00.000 107
2004-02-23 00:00:00.000 90
2004-02-24 00:00:00.000 22
2004-02-27 00:00:00.000 56
2004-03-01 00:00:00.000 56
2004-03-02 00:00:00.000 22
2004-03-05 00:00:00.000 107
2004-03-08 00:00:00.000 22
2004-03-11 00:00:00.000 501
2004-04-22 00:00:00.000 12
2004-04-29 00:00:00.000 22
2004-04-30 00:00:00.000 48
2004-05-03 00:00:00.000 44
2004-05-04 00:00:00.000 19
2004-05-07 00:00:00.000 22
2004-05-10 00:00:00.000 22
2004-05-13 00:00:00.000 1998
2004-05-26 00:00:00.000 15
2004-05-28 00:00:00.000 5
2004-06-01 00:00:00.000 52
2004-06-02 00:00:00.000 44
2004-06-03 00:00:00.000 31
2004-06-04 00:00:00.000 18
2004-06-07 00:00:00.000 18
2004-06-16 00:00:00.000 672
2004-06-17 00:00:00.000 698
2004-06-19 00:00:00.000 2
2004-06-20 00:00:00.000 640
2004-06-21 00:00:00.000 692
2004-06-22 00:00:00.000 326
2004-06-23 00:00:00.000 378
2004-06-24 00:00:00.000 400
2004-06-25 00:00:00.000 348
2004-06-26 00:00:00.000 345
2004-06-29 00:00:00.000 25
2004-06-30 00:00:00.000 82
2004-07-01 00:00:00.000 23
2004-07-08 00:00:00.000 21
2004-07-12 00:00:00.000 21
2004-07-15 00:00:00.000 467
2004-07-19 00:00:00.000 432
2004-07-23 00:00:00.000 427
2004-07-27 00:00:00.000 21
2004-08-08 00:00:00.000 392|||Also try,
"where convert(char(10),dateofbusiness,120) is not null"
and post results,
Jon
--
Message posted via http://www.sqlmonster.com|||OK, so there are none that are not null. Could you please post the DDL for
your table and the exact query you ran?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104272808.874749.162530@.z14g2000cwz.googlegroups.com...
2003-03-25 00:00:00.000 15
2003-03-26 00:00:00.000 17
2003-03-27 00:00:00.000 73
2003-03-28 00:00:00.000 9
2003-04-11 00:00:00.000 18
2003-04-14 00:00:00.000 131
2003-04-15 00:00:00.000 11
2003-04-25 00:00:00.000 2
2003-04-28 00:00:00.000 18
2003-05-06 00:00:00.000 17
2003-05-08 00:00:00.000 16
2003-05-12 00:00:00.000 23
2003-05-13 00:00:00.000 80
2003-05-16 00:00:00.000 27
2003-05-19 00:00:00.000 15
2003-06-05 00:00:00.000 23
2003-06-16 00:00:00.000 11942
2003-06-17 00:00:00.000 12038
2003-06-18 00:00:00.000 12166
2003-06-19 00:00:00.000 12307
2003-06-20 00:00:00.000 12502
2003-06-21 00:00:00.000 11840
2003-06-22 00:00:00.000 11341
2003-06-23 00:00:00.000 11784
2003-06-24 00:00:00.000 12055
2003-06-25 00:00:00.000 11905
2003-06-26 00:00:00.000 12088
2003-06-27 00:00:00.000 12451
2003-06-28 00:00:00.000 11956
2003-06-29 00:00:00.000 11303
2003-06-30 00:00:00.000 12072
2003-07-01 00:00:00.000 11545
2003-07-02 00:00:00.000 11604
2003-07-03 00:00:00.000 11857
2003-07-04 00:00:00.000 636
2003-07-05 00:00:00.000 11045
2003-07-06 00:00:00.000 10303
2003-07-07 00:00:00.000 11324
2003-07-08 00:00:00.000 11002
2003-07-09 00:00:00.000 11446
2003-07-10 00:00:00.000 11486
2003-07-11 00:00:00.000 12115
2003-07-12 00:00:00.000 11240
2003-07-13 00:00:00.000 10871
2003-07-14 00:00:00.000 11395
2003-07-15 00:00:00.000 11563
2003-07-16 00:00:00.000 19
2003-08-27 00:00:00.000 26
2003-08-28 00:00:00.000 25
2003-09-16 00:00:00.000 805
2003-09-17 00:00:00.000 806
2003-09-18 00:00:00.000 783
2003-09-19 00:00:00.000 365
2003-09-20 00:00:00.000 312
2003-09-21 00:00:00.000 318
2003-09-22 00:00:00.000 334
2003-09-23 00:00:00.000 347
2003-09-24 00:00:00.000 358
2003-09-25 00:00:00.000 390
2003-09-26 00:00:00.000 388
2003-09-27 00:00:00.000 354
2003-09-28 00:00:00.000 340
2003-09-29 00:00:00.000 334
2003-09-30 00:00:00.000 367
2003-10-02 00:00:00.000 2
2003-10-03 00:00:00.000 7
2003-10-06 00:00:00.000 19
2003-10-08 00:00:00.000 13
2003-10-22 00:00:00.000 15
2003-12-01 00:00:00.000 82068
2003-12-02 00:00:00.000 83850
2003-12-03 00:00:00.000 84996
2003-12-04 00:00:00.000 85926
2003-12-05 00:00:00.000 89212
2003-12-06 00:00:00.000 86304
2003-12-07 00:00:00.000 75057
2003-12-08 00:00:00.000 83997
2003-12-09 00:00:00.000 83979
2003-12-10 00:00:00.000 86040
2003-12-11 00:00:00.000 87731
2003-12-12 00:00:00.000 91075
2003-12-13 00:00:00.000 88179
2003-12-14 00:00:00.000 73306
2003-12-15 00:00:00.000 161373
2003-12-16 00:00:00.000 94041
2003-12-17 00:00:00.000 97228
2003-12-18 00:00:00.000 99609
2003-12-19 00:00:00.000 102561
2003-12-20 00:00:00.000 98621
2003-12-21 00:00:00.000 85470
2003-12-22 00:00:00.000 102115
2003-12-23 00:00:00.000 103167
2003-12-24 00:00:00.000 50463
2003-12-26 00:00:00.000 95627
2003-12-27 00:00:00.000 93059
2003-12-28 00:00:00.000 87425
2003-12-29 00:00:00.000 96371
2003-12-30 00:00:00.000 97959
2003-12-31 00:00:00.000 81578
2004-02-01 00:00:00.000 384
2004-02-06 00:00:00.000 26
2004-02-12 00:00:00.000 52
2004-02-17 00:00:00.000 22
2004-02-19 00:00:00.000 17
2004-02-20 00:00:00.000 50
2004-02-21 00:00:00.000 107
2004-02-23 00:00:00.000 90
2004-02-24 00:00:00.000 22
2004-02-27 00:00:00.000 56
2004-03-01 00:00:00.000 56
2004-03-02 00:00:00.000 22
2004-03-05 00:00:00.000 107
2004-03-08 00:00:00.000 22
2004-03-11 00:00:00.000 501
2004-04-22 00:00:00.000 12
2004-04-29 00:00:00.000 22
2004-04-30 00:00:00.000 48
2004-05-03 00:00:00.000 44
2004-05-04 00:00:00.000 19
2004-05-07 00:00:00.000 22
2004-05-10 00:00:00.000 22
2004-05-13 00:00:00.000 1998
2004-05-26 00:00:00.000 15
2004-05-28 00:00:00.000 5
2004-06-01 00:00:00.000 52
2004-06-02 00:00:00.000 44
2004-06-03 00:00:00.000 31
2004-06-04 00:00:00.000 18
2004-06-07 00:00:00.000 18
2004-06-16 00:00:00.000 672
2004-06-17 00:00:00.000 698
2004-06-19 00:00:00.000 2
2004-06-20 00:00:00.000 640
2004-06-21 00:00:00.000 692
2004-06-22 00:00:00.000 326
2004-06-23 00:00:00.000 378
2004-06-24 00:00:00.000 400
2004-06-25 00:00:00.000 348
2004-06-26 00:00:00.000 345
2004-06-29 00:00:00.000 25
2004-06-30 00:00:00.000 82
2004-07-01 00:00:00.000 23
2004-07-08 00:00:00.000 21
2004-07-12 00:00:00.000 21
2004-07-15 00:00:00.000 467
2004-07-19 00:00:00.000 432
2004-07-23 00:00:00.000 427
2004-07-27 00:00:00.000 21
2004-08-08 00:00:00.000 392|||And to emphasize futher the same statement with the count(*) function
returned no results. And without the count function and group by it
will return results, however, when keying in on an additional field
such as storeid like "where storeid = 34 and DateofBusiness is not
null" nothing returns. If you filter by store id, i see dateofbusiness
had values in it?
set transaction isolation level read uncommitted
select
dateofbusiness,
count (*)
from
Temp_HstSalesByInterval
where dateofbusiness is not null
group by
dateofbusiness|||John we have already done this as a hotfix to our database,
"rtrim(dateofbusiness) is not null" works, too. However we have over
2000 databases that do not have the hot fix, and other code affecting
the table.
SQL is not doing what its supposed to be doing and I'm looking for an
answer. I don't usually post until....|||Ken,
I did run into this problem in the past and am trying to remember what caused it to happen and how I fixed it. I've tried to replicate the date-time like your example and my query worked just fine.
I thought it had something to do with the hh:mm:ss being all zeros, but I'm not sure that's the problem. I also tried the ansi-null option on the db and it still worked. I also tried the connection object and thought maybe one of the settings was incorrectly set, but that did reproduce your error.
If I find out, I'll post or you can email me at: corncrowe@.aol.com
Jon
--
Message posted via http://www.sqlmonster.com|||CREATE TABLE [dbo].[Temp_HstSalesByInterval] (
[DateOfBusiness] [datetime] NULL ,
[FKStoreId] [int] NULL ,
[FKRevenueId] [int] NULL ,
[Period] [int] NULL ,
[Type] [int] NULL ,
[TypeId] [int] NULL ,
[TypeId2] [int] NULL ,
[Amount] [float] NULL ,
[OpenHour] [int] NULL ,
[lCount] [int] NULL ,
[DestinationServer] [int] NULL
) GO
CREATE CLUSTERED INDEX [IX_Temp_HstSalesByInterval] ON
[dbo].[Temp_HstSalesByInterval]([DateOfBusiness] DESC , [FKStoreId],
[DestinationServer]) ON [PRIMARY]
GO
select * from Temp_HstSalesByInterval where fkstoreid = 23 and
dateofbusiness is not null
select * from Temp_HstSalesByInterval where fkstoreid = 23 will return
results and dateofbusiness is populated.|||Ken,
The all zeros in hh:mm:ss is most likely the problem. I asked the other programmer here and we both agreed that we encountered this problem when trying to query a date field with zeros in the hh:mm:ss stamp.
Why or how did you manage to write all zeros on a date/time field? In SQL there is no such thing as 00:00:00?
Sorry,
Jon
--
Message posted via http://www.sqlmonster.com|||Ken,
Maybe we are wrong about the zeros. But threw that out for you,
Jon
--
Message posted via http://www.sqlmonster.com|||> Why or how did you manage to write all zeros on a date/time field? In SQL
there is no such thing as 00:00:00?
Midnight is not a valid time?|||create table lookatdate (d datetime null)
insert lookatdate values ('09/04/2004')
select * from lookatdate
returns 2004-09-04 00:00:00.000|||This never happenend. Jon mistook reading one field as two fields.
09/23/2004 00:00:00:000|||> This never happenend. Jon mistook reading one field as two fields.
> 09/23/2004 00:00:00:000
Okay, maybe another reason to visit the link from FAQ #5006 to generate
insert statements, instead of presenting sample data in heap style...
http://vyaskn.tripod.com/code.htm#inserts|||Well maybe there something to what you are saying, but why does it work
in the other databases on the same server?|||Ken,
I have another suggestion which may sound odd. But consider this:
"Specifies that the Boolean result be negated. The predicate reverses its return values, returning TRUE if the value is not NULL, and FALSE if the value is NULL."
"The WHERE and HAVING clauses in a SELECT statement control the rows from the source tables that are used to build the result set. WHERE and HAVING are filters. They specify a series of search conditions, and only those rows that meet the terms of the search conditions are used to build the result set. Those rows meeting the search conditions are said to be qualified to participate in the result set. (Microsoft)"
I am thinking that when you build the resultset with "not null" it evaluates the criteria as "unknown" and thereby won't produce any resultset. But when you trim or otherwise convert the datatype then the query returns a resultset.
Try a couple other approaches to see if you get a resultset:
select * from tblName
where not (datefield) is null
-or-
select somefield where datefield is not null
I am also wondering if building the resultset with the all "*" isn't masking the issue either? But either way, NOT NULL evaluates to a true condition if the value is not null. Otherwise, I think the field "as defined" is unknown.
I tried to replicate your error with a test database and couldn't. I tried several db_options and ansi null defaults and still couldn't reproduce your error. I do vaguely remember this problem, but can't for the life of me remember what cause it or how I fixed it.
Sorry I couldn't be more of a help,
Jon
--
Message posted via http://www.sqlmonster.com|||Ken,
The first suggestion is to make sure that FKstoreid 34 actually has data.
If so, then if the query works on other databases on the same server, that
would suggest something is peculiar to the data in that database.
What happens if you restore that particular db to another SQL server with
the same version and run the query? If it still returns no results, that
would suggest definitely something in the data is causing the issue.
At this stage in SQL Server 2000's lifecyle, it seems unlikely that your
data has uncovered a bug in the query engine, but it's possible. Try adding
an identity column as a primary key and see if that makes a difference.
Maybe grouping on a date column in a heap...?
Ron
--
Ron Talmage
SQL Server MVP
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104273359.147443.196280@.f14g2000cwb.googlegroups.com...
> And to emphasize futher the same statement with the count(*) function
> returned no results. And without the count function and group by it
> will return results, however, when keying in on an additional field
> such as storeid like "where storeid = 34 and DateofBusiness is not
> null" nothing returns. If you filter by store id, i see dateofbusiness
> had values in it?
> set transaction isolation level read uncommitted
> select
> dateofbusiness,
> count (*)
> from
> Temp_HstSalesByInterval
> where dateofbusiness is not null
> group by
> dateofbusiness
>|||Hi Ron,
The store exists. we are not doing any group bys or joins. Its a very
simple select statement.
select * from Temp_HstSalesByInterval where fkstoreid = 34 and
dateofbusiness is not null
select * from Temp_HstSalesByInterval where fkstoreid = 34 (without
above) will return
results and dateofbusiness is populated.|||I'm wondering if it's possible to create a new table and populate it with
the contents of the old one - preferably by bcp'ing the data out and back in
again.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104333999.756145.81940@.f14g2000cwb.googlegroups.com...
Hi Ron,
The store exists. we are not doing any group bys or joins. Its a very
simple select statement.
select * from Temp_HstSalesByInterval where fkstoreid = 34 and
dateofbusiness is not null
select * from Temp_HstSalesByInterval where fkstoreid = 34 (without
above) will return
results and dateofbusiness is populated.|||Hi Jon
select * from tblName
where not (datefield) is null
this produces no records
select somefield where datefield is not null
This will work, but when you add "and fkstoreid = anynumber" nothing
returns. If you query only on fkstoreid only you will see the results
and dateofbusiness is populated. I did an update to this table to set
datetime field with refreshed values and that did not fix it.
The actual select statement lists the fields not (*). both have the
same affect.|||> select somefield where datefield is not null
> This will work, but when you add "and fkstoreid = anynumber" nothing
> returns.
This does not seem possible. How large is your database? I am wondering if
you would be able to post a copy of it online and someone could try to
reproduce the problem on your actual data. I am guessing there is something
else inaccurate here that you're not catching...|||Hi Tom,
We did this with DTS.|||Ken,
Another (way out there) suggestion. Maybe, just maybe, there is a constraint on the fkStoreId between parent and child? I know this sounds weird, but maybe when you consider the where clause is failing when you combine both the fkStoreId and DateofBusiness fields in the same statement.
The condition is returning false for some reason. I know it's really a long shot, but could there be an issue with the Stores table referential integrity? Is the fkStoreId an orphan? I seriously doubt that this is the culprit, but weirder things have been know to happen in SQL world.
I did create your temp table and inserted rows of data, but couldn't reproduce the error. I don't know what the parent table looks like so I could do anything on that thread.
Good luck,
Jon
--
Message posted via http://www.sqlmonster.com|||Hi Jon, there is no contstraint defined on fkstoreid. However manually
dropping the nonunique clustered index(DateofBusiness, FKStoreid,
DestinationServer) columns (all allow nulls) and recreated it and now
not null is working finally! :)
The other db man here swears he ran DBCC REINDEX on the table and that
did not fix the issue. Isn't running DBCC REINDEX the same as dropping
and recreating an index? Any know issues with this?|||This is great news. It's hard to say what happened here. If you have a
copy of the database before you did the DBREINDEX, I'd be tempted to run
DBCC CHECKTABLE and see if it found anything.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104344333.997556.246980@.c13g2000cwb.googlegroups.com...
Hi Jon, there is no contstraint defined on fkstoreid. However manually
dropping the nonunique clustered index(DateofBusiness, FKStoreid,
DestinationServer) columns (all allow nulls) and recreated it and now
not null is working finally! :)
The other db man here swears he ran DBCC REINDEX on the table and that
did not fix the issue. Isn't running DBCC REINDEX the same as dropping
and recreating an index? Any know issues with this?|||Jon, when I recreated the nonunique clustered index DateOfBusiness
order was changed from descending to ascending. "IS NOT NULL" on
dateofbusiness works. When it was set back to descending it doesn't
work.
On the same table in other databases, descending does not have an issue
with the "IS NOT NULL" not working. However, this table has 3 million
records and the tables in the other databases have less than 200,000
records.
Throughing this out to see if you can recreate the issue?|||Ken,
Sorry if you did this already, but have you posted the result of
SELECT @.@.VERSION ? Also, is there anything you can see different with
this particular database, such as a compatibility level setting or
something?
Steve Kass
Drew University
Ken wrote:
>Jon, when I recreated the nonunique clustered index DateOfBusiness
>order was changed from descending to ascending. "IS NOT NULL" on
>dateofbusiness works. When it was set back to descending it doesn't
>work.
>On the same table in other databases, descending does not have an issue
>with the "IS NOT NULL" not working. However, this table has 3 million
>records and the tables in the other databases have less than 200,000
>records.
>Throughing this out to see if you can recreate the issue?
>
>|||Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: )
compatability on all databases 8.0|||There has a been a security hotfix issued since then. That would bring the
version to 8.00.818:
http://www.microsoft.com/downloads/details.aspx?FamilyID=9814AE9D-BD44-40C5-ADD3-B8C99618E68D&displaylang=en
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104349480.164105.285990@.c13g2000cwb.googlegroups.com...
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: )
compatability on all databases 8.0|||Hm. Are the "working" databases on the same instance of SQL Server? If
not, and the others are a later version,
try installing 8.00.818 (or later) and see if the problem goes away.
There are sometimes bug fixes in security hotfixes, since the hotfixes
are cumulative. If that's not the problem, I'll see if I can come up
with some other questions!
SK
Ken wrote:
>Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
>14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
>Edition on Windows NT 5.2 (Build 3790: )
>compatability on all databases 8.0
>
>|||Ken,
"nonunique clustered index DateOfBusiness order was changed from descending to ascending. "IS NOT NULL" on dateofbusiness works. When it was set back to descending it doesn't work."
CREATE CLUSTERED INDEX [IX_Temp_HstSalesByInterval] ON
[dbo].[Temp_HstSalesByInterval]([DateOfBusiness] DESC , [FKStoreId],
[DestinationServer]) ON [PRIMARY]
I was leading toward the index as my next suggestion. The index is a composite and is made up of different datatypes. When you were doing the query the index is backwards (desc) order for date and acs for the other two fields. That's probably why the query never returned anything. I can replicate this error on a test database with 30 million call records using essentially the same logic. I'll let you know later what happens.
I probably wouldn't use mix order for my indices. Maybe that was what I had done in the past and cause a problem? Anyway, glad you were able to correct the problem.
Jon
--
Message posted via http://www.sqlmonster.com|||Hi Jon, please replicate the error. Why does it only happen on tables
with a large number of rows?
My other thought could still be a data issue depending on what gets
evaluated first?|||> My other thought could still be a data issue depending on what gets
> evaluated first?
The WHERE clause does not work that way. Put the individual clauses in any
order, and the result will be the same...|||Hi Tom,
The hotfix does not address the particular problem in any
documentation. We have a lot of servers (clustered) and my boss never
lets me do anything on a guess, only until I have found the problem and
I can qualify it. If I could validate that the problem was server wide
and this occurrance was a documented bug or issue, I would implement
this asap.
I do appreciate your assistance. Your suggestion may be a fix to this,
but I'm on a supertanker thats not easy to steer or deploy changes.|||I hear ya. Nevertheless, the patch is a security hotfix and closes a
vulnerability. As another poster mentioned, fixes are cumulative and it is
possible that your issue may have been addressed there.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104353652.360283.184350@.f14g2000cwb.googlegroups.com...
Hi Tom,
The hotfix does not address the particular problem in any
documentation. We have a lot of servers (clustered) and my boss never
lets me do anything on a guess, only until I have found the problem and
I can qualify it. If I could validate that the problem was server wide
and this occurrance was a documented bug or issue, I would implement
this asap.
I do appreciate your assistance. Your suggestion may be a fix to this,
but I'm on a supertanker thats not easy to steer or deploy changes.|||Ken,
Maybe this newsgroup thread will help. Sorry I didn't remember this,
but it looks to me like the same bug you're seeing, and 8.00.818 fixed it:
http://groups-beta.google.com/groups?hl=en&q=8.00.818+fix+bug+kass+-turkish&qt_s=Search+Groups
SK
Ken wrote:
>Hi Tom,
>The hotfix does not address the particular problem in any
>documentation. We have a lot of servers (clustered) and my boss never
>lets me do anything on a guess, only until I have found the problem and
>I can qualify it. If I could validate that the problem was server wide
>and this occurrance was a documented bug or issue, I would implement
>this asap.
>I do appreciate your assistance. Your suggestion may be a fix to this,
>but I'm on a supertanker thats not easy to steer or deploy changes.
>
>|||Jon, I have a sneaky suspicioun that its in the data. When we move the
same data to another table it produces the same error. If we move or
create different data it does not. Weird.
clause "where dateofbusiness is not null" no data is returned. This
works in our other databases. This is a simple select with no
aggregation functions such as count(*) and no group bys. No joins.
We checked for table corruption and rebuilt the table and indexes.
I'm completely at a loss.
Anybody run into this?Run the following and post the results:
select
dateofbusiness
, count (*)
from
MyTable
group by
dateofbusiness
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104271245.345995.62690@.f14g2000cwb.googlegroups.com...
We have data in datetime field and in the simple select statement where
clause "where dateofbusiness is not null" no data is returned. This
works in our other databases. This is a simple select with no
aggregation functions such as count(*) and no group bys. No joins.
We checked for table corruption and rebuilt the table and indexes.
I'm completely at a loss.
Anybody run into this?|||Try,
where ISDATE(dateofbusiness) <> 0
--
Message posted via http://www.sqlmonster.com|||2003-03-25 00:00:00.000 15
2003-03-26 00:00:00.000 17
2003-03-27 00:00:00.000 73
2003-03-28 00:00:00.000 9
2003-04-11 00:00:00.000 18
2003-04-14 00:00:00.000 131
2003-04-15 00:00:00.000 11
2003-04-25 00:00:00.000 2
2003-04-28 00:00:00.000 18
2003-05-06 00:00:00.000 17
2003-05-08 00:00:00.000 16
2003-05-12 00:00:00.000 23
2003-05-13 00:00:00.000 80
2003-05-16 00:00:00.000 27
2003-05-19 00:00:00.000 15
2003-06-05 00:00:00.000 23
2003-06-16 00:00:00.000 11942
2003-06-17 00:00:00.000 12038
2003-06-18 00:00:00.000 12166
2003-06-19 00:00:00.000 12307
2003-06-20 00:00:00.000 12502
2003-06-21 00:00:00.000 11840
2003-06-22 00:00:00.000 11341
2003-06-23 00:00:00.000 11784
2003-06-24 00:00:00.000 12055
2003-06-25 00:00:00.000 11905
2003-06-26 00:00:00.000 12088
2003-06-27 00:00:00.000 12451
2003-06-28 00:00:00.000 11956
2003-06-29 00:00:00.000 11303
2003-06-30 00:00:00.000 12072
2003-07-01 00:00:00.000 11545
2003-07-02 00:00:00.000 11604
2003-07-03 00:00:00.000 11857
2003-07-04 00:00:00.000 636
2003-07-05 00:00:00.000 11045
2003-07-06 00:00:00.000 10303
2003-07-07 00:00:00.000 11324
2003-07-08 00:00:00.000 11002
2003-07-09 00:00:00.000 11446
2003-07-10 00:00:00.000 11486
2003-07-11 00:00:00.000 12115
2003-07-12 00:00:00.000 11240
2003-07-13 00:00:00.000 10871
2003-07-14 00:00:00.000 11395
2003-07-15 00:00:00.000 11563
2003-07-16 00:00:00.000 19
2003-08-27 00:00:00.000 26
2003-08-28 00:00:00.000 25
2003-09-16 00:00:00.000 805
2003-09-17 00:00:00.000 806
2003-09-18 00:00:00.000 783
2003-09-19 00:00:00.000 365
2003-09-20 00:00:00.000 312
2003-09-21 00:00:00.000 318
2003-09-22 00:00:00.000 334
2003-09-23 00:00:00.000 347
2003-09-24 00:00:00.000 358
2003-09-25 00:00:00.000 390
2003-09-26 00:00:00.000 388
2003-09-27 00:00:00.000 354
2003-09-28 00:00:00.000 340
2003-09-29 00:00:00.000 334
2003-09-30 00:00:00.000 367
2003-10-02 00:00:00.000 2
2003-10-03 00:00:00.000 7
2003-10-06 00:00:00.000 19
2003-10-08 00:00:00.000 13
2003-10-22 00:00:00.000 15
2003-12-01 00:00:00.000 82068
2003-12-02 00:00:00.000 83850
2003-12-03 00:00:00.000 84996
2003-12-04 00:00:00.000 85926
2003-12-05 00:00:00.000 89212
2003-12-06 00:00:00.000 86304
2003-12-07 00:00:00.000 75057
2003-12-08 00:00:00.000 83997
2003-12-09 00:00:00.000 83979
2003-12-10 00:00:00.000 86040
2003-12-11 00:00:00.000 87731
2003-12-12 00:00:00.000 91075
2003-12-13 00:00:00.000 88179
2003-12-14 00:00:00.000 73306
2003-12-15 00:00:00.000 161373
2003-12-16 00:00:00.000 94041
2003-12-17 00:00:00.000 97228
2003-12-18 00:00:00.000 99609
2003-12-19 00:00:00.000 102561
2003-12-20 00:00:00.000 98621
2003-12-21 00:00:00.000 85470
2003-12-22 00:00:00.000 102115
2003-12-23 00:00:00.000 103167
2003-12-24 00:00:00.000 50463
2003-12-26 00:00:00.000 95627
2003-12-27 00:00:00.000 93059
2003-12-28 00:00:00.000 87425
2003-12-29 00:00:00.000 96371
2003-12-30 00:00:00.000 97959
2003-12-31 00:00:00.000 81578
2004-02-01 00:00:00.000 384
2004-02-06 00:00:00.000 26
2004-02-12 00:00:00.000 52
2004-02-17 00:00:00.000 22
2004-02-19 00:00:00.000 17
2004-02-20 00:00:00.000 50
2004-02-21 00:00:00.000 107
2004-02-23 00:00:00.000 90
2004-02-24 00:00:00.000 22
2004-02-27 00:00:00.000 56
2004-03-01 00:00:00.000 56
2004-03-02 00:00:00.000 22
2004-03-05 00:00:00.000 107
2004-03-08 00:00:00.000 22
2004-03-11 00:00:00.000 501
2004-04-22 00:00:00.000 12
2004-04-29 00:00:00.000 22
2004-04-30 00:00:00.000 48
2004-05-03 00:00:00.000 44
2004-05-04 00:00:00.000 19
2004-05-07 00:00:00.000 22
2004-05-10 00:00:00.000 22
2004-05-13 00:00:00.000 1998
2004-05-26 00:00:00.000 15
2004-05-28 00:00:00.000 5
2004-06-01 00:00:00.000 52
2004-06-02 00:00:00.000 44
2004-06-03 00:00:00.000 31
2004-06-04 00:00:00.000 18
2004-06-07 00:00:00.000 18
2004-06-16 00:00:00.000 672
2004-06-17 00:00:00.000 698
2004-06-19 00:00:00.000 2
2004-06-20 00:00:00.000 640
2004-06-21 00:00:00.000 692
2004-06-22 00:00:00.000 326
2004-06-23 00:00:00.000 378
2004-06-24 00:00:00.000 400
2004-06-25 00:00:00.000 348
2004-06-26 00:00:00.000 345
2004-06-29 00:00:00.000 25
2004-06-30 00:00:00.000 82
2004-07-01 00:00:00.000 23
2004-07-08 00:00:00.000 21
2004-07-12 00:00:00.000 21
2004-07-15 00:00:00.000 467
2004-07-19 00:00:00.000 432
2004-07-23 00:00:00.000 427
2004-07-27 00:00:00.000 21
2004-08-08 00:00:00.000 392|||Also try,
"where convert(char(10),dateofbusiness,120) is not null"
and post results,
Jon
--
Message posted via http://www.sqlmonster.com|||OK, so there are none that are not null. Could you please post the DDL for
your table and the exact query you ran?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104272808.874749.162530@.z14g2000cwz.googlegroups.com...
2003-03-25 00:00:00.000 15
2003-03-26 00:00:00.000 17
2003-03-27 00:00:00.000 73
2003-03-28 00:00:00.000 9
2003-04-11 00:00:00.000 18
2003-04-14 00:00:00.000 131
2003-04-15 00:00:00.000 11
2003-04-25 00:00:00.000 2
2003-04-28 00:00:00.000 18
2003-05-06 00:00:00.000 17
2003-05-08 00:00:00.000 16
2003-05-12 00:00:00.000 23
2003-05-13 00:00:00.000 80
2003-05-16 00:00:00.000 27
2003-05-19 00:00:00.000 15
2003-06-05 00:00:00.000 23
2003-06-16 00:00:00.000 11942
2003-06-17 00:00:00.000 12038
2003-06-18 00:00:00.000 12166
2003-06-19 00:00:00.000 12307
2003-06-20 00:00:00.000 12502
2003-06-21 00:00:00.000 11840
2003-06-22 00:00:00.000 11341
2003-06-23 00:00:00.000 11784
2003-06-24 00:00:00.000 12055
2003-06-25 00:00:00.000 11905
2003-06-26 00:00:00.000 12088
2003-06-27 00:00:00.000 12451
2003-06-28 00:00:00.000 11956
2003-06-29 00:00:00.000 11303
2003-06-30 00:00:00.000 12072
2003-07-01 00:00:00.000 11545
2003-07-02 00:00:00.000 11604
2003-07-03 00:00:00.000 11857
2003-07-04 00:00:00.000 636
2003-07-05 00:00:00.000 11045
2003-07-06 00:00:00.000 10303
2003-07-07 00:00:00.000 11324
2003-07-08 00:00:00.000 11002
2003-07-09 00:00:00.000 11446
2003-07-10 00:00:00.000 11486
2003-07-11 00:00:00.000 12115
2003-07-12 00:00:00.000 11240
2003-07-13 00:00:00.000 10871
2003-07-14 00:00:00.000 11395
2003-07-15 00:00:00.000 11563
2003-07-16 00:00:00.000 19
2003-08-27 00:00:00.000 26
2003-08-28 00:00:00.000 25
2003-09-16 00:00:00.000 805
2003-09-17 00:00:00.000 806
2003-09-18 00:00:00.000 783
2003-09-19 00:00:00.000 365
2003-09-20 00:00:00.000 312
2003-09-21 00:00:00.000 318
2003-09-22 00:00:00.000 334
2003-09-23 00:00:00.000 347
2003-09-24 00:00:00.000 358
2003-09-25 00:00:00.000 390
2003-09-26 00:00:00.000 388
2003-09-27 00:00:00.000 354
2003-09-28 00:00:00.000 340
2003-09-29 00:00:00.000 334
2003-09-30 00:00:00.000 367
2003-10-02 00:00:00.000 2
2003-10-03 00:00:00.000 7
2003-10-06 00:00:00.000 19
2003-10-08 00:00:00.000 13
2003-10-22 00:00:00.000 15
2003-12-01 00:00:00.000 82068
2003-12-02 00:00:00.000 83850
2003-12-03 00:00:00.000 84996
2003-12-04 00:00:00.000 85926
2003-12-05 00:00:00.000 89212
2003-12-06 00:00:00.000 86304
2003-12-07 00:00:00.000 75057
2003-12-08 00:00:00.000 83997
2003-12-09 00:00:00.000 83979
2003-12-10 00:00:00.000 86040
2003-12-11 00:00:00.000 87731
2003-12-12 00:00:00.000 91075
2003-12-13 00:00:00.000 88179
2003-12-14 00:00:00.000 73306
2003-12-15 00:00:00.000 161373
2003-12-16 00:00:00.000 94041
2003-12-17 00:00:00.000 97228
2003-12-18 00:00:00.000 99609
2003-12-19 00:00:00.000 102561
2003-12-20 00:00:00.000 98621
2003-12-21 00:00:00.000 85470
2003-12-22 00:00:00.000 102115
2003-12-23 00:00:00.000 103167
2003-12-24 00:00:00.000 50463
2003-12-26 00:00:00.000 95627
2003-12-27 00:00:00.000 93059
2003-12-28 00:00:00.000 87425
2003-12-29 00:00:00.000 96371
2003-12-30 00:00:00.000 97959
2003-12-31 00:00:00.000 81578
2004-02-01 00:00:00.000 384
2004-02-06 00:00:00.000 26
2004-02-12 00:00:00.000 52
2004-02-17 00:00:00.000 22
2004-02-19 00:00:00.000 17
2004-02-20 00:00:00.000 50
2004-02-21 00:00:00.000 107
2004-02-23 00:00:00.000 90
2004-02-24 00:00:00.000 22
2004-02-27 00:00:00.000 56
2004-03-01 00:00:00.000 56
2004-03-02 00:00:00.000 22
2004-03-05 00:00:00.000 107
2004-03-08 00:00:00.000 22
2004-03-11 00:00:00.000 501
2004-04-22 00:00:00.000 12
2004-04-29 00:00:00.000 22
2004-04-30 00:00:00.000 48
2004-05-03 00:00:00.000 44
2004-05-04 00:00:00.000 19
2004-05-07 00:00:00.000 22
2004-05-10 00:00:00.000 22
2004-05-13 00:00:00.000 1998
2004-05-26 00:00:00.000 15
2004-05-28 00:00:00.000 5
2004-06-01 00:00:00.000 52
2004-06-02 00:00:00.000 44
2004-06-03 00:00:00.000 31
2004-06-04 00:00:00.000 18
2004-06-07 00:00:00.000 18
2004-06-16 00:00:00.000 672
2004-06-17 00:00:00.000 698
2004-06-19 00:00:00.000 2
2004-06-20 00:00:00.000 640
2004-06-21 00:00:00.000 692
2004-06-22 00:00:00.000 326
2004-06-23 00:00:00.000 378
2004-06-24 00:00:00.000 400
2004-06-25 00:00:00.000 348
2004-06-26 00:00:00.000 345
2004-06-29 00:00:00.000 25
2004-06-30 00:00:00.000 82
2004-07-01 00:00:00.000 23
2004-07-08 00:00:00.000 21
2004-07-12 00:00:00.000 21
2004-07-15 00:00:00.000 467
2004-07-19 00:00:00.000 432
2004-07-23 00:00:00.000 427
2004-07-27 00:00:00.000 21
2004-08-08 00:00:00.000 392|||And to emphasize futher the same statement with the count(*) function
returned no results. And without the count function and group by it
will return results, however, when keying in on an additional field
such as storeid like "where storeid = 34 and DateofBusiness is not
null" nothing returns. If you filter by store id, i see dateofbusiness
had values in it?
set transaction isolation level read uncommitted
select
dateofbusiness,
count (*)
from
Temp_HstSalesByInterval
where dateofbusiness is not null
group by
dateofbusiness|||John we have already done this as a hotfix to our database,
"rtrim(dateofbusiness) is not null" works, too. However we have over
2000 databases that do not have the hot fix, and other code affecting
the table.
SQL is not doing what its supposed to be doing and I'm looking for an
answer. I don't usually post until....|||Ken,
I did run into this problem in the past and am trying to remember what caused it to happen and how I fixed it. I've tried to replicate the date-time like your example and my query worked just fine.
I thought it had something to do with the hh:mm:ss being all zeros, but I'm not sure that's the problem. I also tried the ansi-null option on the db and it still worked. I also tried the connection object and thought maybe one of the settings was incorrectly set, but that did reproduce your error.
If I find out, I'll post or you can email me at: corncrowe@.aol.com
Jon
--
Message posted via http://www.sqlmonster.com|||CREATE TABLE [dbo].[Temp_HstSalesByInterval] (
[DateOfBusiness] [datetime] NULL ,
[FKStoreId] [int] NULL ,
[FKRevenueId] [int] NULL ,
[Period] [int] NULL ,
[Type] [int] NULL ,
[TypeId] [int] NULL ,
[TypeId2] [int] NULL ,
[Amount] [float] NULL ,
[OpenHour] [int] NULL ,
[lCount] [int] NULL ,
[DestinationServer] [int] NULL
) GO
CREATE CLUSTERED INDEX [IX_Temp_HstSalesByInterval] ON
[dbo].[Temp_HstSalesByInterval]([DateOfBusiness] DESC , [FKStoreId],
[DestinationServer]) ON [PRIMARY]
GO
select * from Temp_HstSalesByInterval where fkstoreid = 23 and
dateofbusiness is not null
select * from Temp_HstSalesByInterval where fkstoreid = 23 will return
results and dateofbusiness is populated.|||Ken,
The all zeros in hh:mm:ss is most likely the problem. I asked the other programmer here and we both agreed that we encountered this problem when trying to query a date field with zeros in the hh:mm:ss stamp.
Why or how did you manage to write all zeros on a date/time field? In SQL there is no such thing as 00:00:00?
Sorry,
Jon
--
Message posted via http://www.sqlmonster.com|||Ken,
Maybe we are wrong about the zeros. But threw that out for you,
Jon
--
Message posted via http://www.sqlmonster.com|||> Why or how did you manage to write all zeros on a date/time field? In SQL
there is no such thing as 00:00:00?
Midnight is not a valid time?|||create table lookatdate (d datetime null)
insert lookatdate values ('09/04/2004')
select * from lookatdate
returns 2004-09-04 00:00:00.000|||This never happenend. Jon mistook reading one field as two fields.
09/23/2004 00:00:00:000|||> This never happenend. Jon mistook reading one field as two fields.
> 09/23/2004 00:00:00:000
Okay, maybe another reason to visit the link from FAQ #5006 to generate
insert statements, instead of presenting sample data in heap style...
http://vyaskn.tripod.com/code.htm#inserts|||Well maybe there something to what you are saying, but why does it work
in the other databases on the same server?|||Ken,
I have another suggestion which may sound odd. But consider this:
"Specifies that the Boolean result be negated. The predicate reverses its return values, returning TRUE if the value is not NULL, and FALSE if the value is NULL."
"The WHERE and HAVING clauses in a SELECT statement control the rows from the source tables that are used to build the result set. WHERE and HAVING are filters. They specify a series of search conditions, and only those rows that meet the terms of the search conditions are used to build the result set. Those rows meeting the search conditions are said to be qualified to participate in the result set. (Microsoft)"
I am thinking that when you build the resultset with "not null" it evaluates the criteria as "unknown" and thereby won't produce any resultset. But when you trim or otherwise convert the datatype then the query returns a resultset.
Try a couple other approaches to see if you get a resultset:
select * from tblName
where not (datefield) is null
-or-
select somefield where datefield is not null
I am also wondering if building the resultset with the all "*" isn't masking the issue either? But either way, NOT NULL evaluates to a true condition if the value is not null. Otherwise, I think the field "as defined" is unknown.
I tried to replicate your error with a test database and couldn't. I tried several db_options and ansi null defaults and still couldn't reproduce your error. I do vaguely remember this problem, but can't for the life of me remember what cause it or how I fixed it.
Sorry I couldn't be more of a help,
Jon
--
Message posted via http://www.sqlmonster.com|||Ken,
The first suggestion is to make sure that FKstoreid 34 actually has data.
If so, then if the query works on other databases on the same server, that
would suggest something is peculiar to the data in that database.
What happens if you restore that particular db to another SQL server with
the same version and run the query? If it still returns no results, that
would suggest definitely something in the data is causing the issue.
At this stage in SQL Server 2000's lifecyle, it seems unlikely that your
data has uncovered a bug in the query engine, but it's possible. Try adding
an identity column as a primary key and see if that makes a difference.
Maybe grouping on a date column in a heap...?
Ron
--
Ron Talmage
SQL Server MVP
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104273359.147443.196280@.f14g2000cwb.googlegroups.com...
> And to emphasize futher the same statement with the count(*) function
> returned no results. And without the count function and group by it
> will return results, however, when keying in on an additional field
> such as storeid like "where storeid = 34 and DateofBusiness is not
> null" nothing returns. If you filter by store id, i see dateofbusiness
> had values in it?
> set transaction isolation level read uncommitted
> select
> dateofbusiness,
> count (*)
> from
> Temp_HstSalesByInterval
> where dateofbusiness is not null
> group by
> dateofbusiness
>|||Hi Ron,
The store exists. we are not doing any group bys or joins. Its a very
simple select statement.
select * from Temp_HstSalesByInterval where fkstoreid = 34 and
dateofbusiness is not null
select * from Temp_HstSalesByInterval where fkstoreid = 34 (without
above) will return
results and dateofbusiness is populated.|||I'm wondering if it's possible to create a new table and populate it with
the contents of the old one - preferably by bcp'ing the data out and back in
again.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104333999.756145.81940@.f14g2000cwb.googlegroups.com...
Hi Ron,
The store exists. we are not doing any group bys or joins. Its a very
simple select statement.
select * from Temp_HstSalesByInterval where fkstoreid = 34 and
dateofbusiness is not null
select * from Temp_HstSalesByInterval where fkstoreid = 34 (without
above) will return
results and dateofbusiness is populated.|||Hi Jon
select * from tblName
where not (datefield) is null
this produces no records
select somefield where datefield is not null
This will work, but when you add "and fkstoreid = anynumber" nothing
returns. If you query only on fkstoreid only you will see the results
and dateofbusiness is populated. I did an update to this table to set
datetime field with refreshed values and that did not fix it.
The actual select statement lists the fields not (*). both have the
same affect.|||> select somefield where datefield is not null
> This will work, but when you add "and fkstoreid = anynumber" nothing
> returns.
This does not seem possible. How large is your database? I am wondering if
you would be able to post a copy of it online and someone could try to
reproduce the problem on your actual data. I am guessing there is something
else inaccurate here that you're not catching...|||Hi Tom,
We did this with DTS.|||Ken,
Another (way out there) suggestion. Maybe, just maybe, there is a constraint on the fkStoreId between parent and child? I know this sounds weird, but maybe when you consider the where clause is failing when you combine both the fkStoreId and DateofBusiness fields in the same statement.
The condition is returning false for some reason. I know it's really a long shot, but could there be an issue with the Stores table referential integrity? Is the fkStoreId an orphan? I seriously doubt that this is the culprit, but weirder things have been know to happen in SQL world.
I did create your temp table and inserted rows of data, but couldn't reproduce the error. I don't know what the parent table looks like so I could do anything on that thread.
Good luck,
Jon
--
Message posted via http://www.sqlmonster.com|||Hi Jon, there is no contstraint defined on fkstoreid. However manually
dropping the nonunique clustered index(DateofBusiness, FKStoreid,
DestinationServer) columns (all allow nulls) and recreated it and now
not null is working finally! :)
The other db man here swears he ran DBCC REINDEX on the table and that
did not fix the issue. Isn't running DBCC REINDEX the same as dropping
and recreating an index? Any know issues with this?|||This is great news. It's hard to say what happened here. If you have a
copy of the database before you did the DBREINDEX, I'd be tempted to run
DBCC CHECKTABLE and see if it found anything.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104344333.997556.246980@.c13g2000cwb.googlegroups.com...
Hi Jon, there is no contstraint defined on fkstoreid. However manually
dropping the nonunique clustered index(DateofBusiness, FKStoreid,
DestinationServer) columns (all allow nulls) and recreated it and now
not null is working finally! :)
The other db man here swears he ran DBCC REINDEX on the table and that
did not fix the issue. Isn't running DBCC REINDEX the same as dropping
and recreating an index? Any know issues with this?|||Jon, when I recreated the nonunique clustered index DateOfBusiness
order was changed from descending to ascending. "IS NOT NULL" on
dateofbusiness works. When it was set back to descending it doesn't
work.
On the same table in other databases, descending does not have an issue
with the "IS NOT NULL" not working. However, this table has 3 million
records and the tables in the other databases have less than 200,000
records.
Throughing this out to see if you can recreate the issue?|||Ken,
Sorry if you did this already, but have you posted the result of
SELECT @.@.VERSION ? Also, is there anything you can see different with
this particular database, such as a compatibility level setting or
something?
Steve Kass
Drew University
Ken wrote:
>Jon, when I recreated the nonunique clustered index DateOfBusiness
>order was changed from descending to ascending. "IS NOT NULL" on
>dateofbusiness works. When it was set back to descending it doesn't
>work.
>On the same table in other databases, descending does not have an issue
>with the "IS NOT NULL" not working. However, this table has 3 million
>records and the tables in the other databases have less than 200,000
>records.
>Throughing this out to see if you can recreate the issue?
>
>|||Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: )
compatability on all databases 8.0|||There has a been a security hotfix issued since then. That would bring the
version to 8.00.818:
http://www.microsoft.com/downloads/details.aspx?FamilyID=9814AE9D-BD44-40C5-ADD3-B8C99618E68D&displaylang=en
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104349480.164105.285990@.c13g2000cwb.googlegroups.com...
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: )
compatability on all databases 8.0|||Hm. Are the "working" databases on the same instance of SQL Server? If
not, and the others are a later version,
try installing 8.00.818 (or later) and see if the problem goes away.
There are sometimes bug fixes in security hotfixes, since the hotfixes
are cumulative. If that's not the problem, I'll see if I can come up
with some other questions!
SK
Ken wrote:
>Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
>14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
>Edition on Windows NT 5.2 (Build 3790: )
>compatability on all databases 8.0
>
>|||Ken,
"nonunique clustered index DateOfBusiness order was changed from descending to ascending. "IS NOT NULL" on dateofbusiness works. When it was set back to descending it doesn't work."
CREATE CLUSTERED INDEX [IX_Temp_HstSalesByInterval] ON
[dbo].[Temp_HstSalesByInterval]([DateOfBusiness] DESC , [FKStoreId],
[DestinationServer]) ON [PRIMARY]
I was leading toward the index as my next suggestion. The index is a composite and is made up of different datatypes. When you were doing the query the index is backwards (desc) order for date and acs for the other two fields. That's probably why the query never returned anything. I can replicate this error on a test database with 30 million call records using essentially the same logic. I'll let you know later what happens.
I probably wouldn't use mix order for my indices. Maybe that was what I had done in the past and cause a problem? Anyway, glad you were able to correct the problem.
Jon
--
Message posted via http://www.sqlmonster.com|||Hi Jon, please replicate the error. Why does it only happen on tables
with a large number of rows?
My other thought could still be a data issue depending on what gets
evaluated first?|||> My other thought could still be a data issue depending on what gets
> evaluated first?
The WHERE clause does not work that way. Put the individual clauses in any
order, and the result will be the same...|||Hi Tom,
The hotfix does not address the particular problem in any
documentation. We have a lot of servers (clustered) and my boss never
lets me do anything on a guess, only until I have found the problem and
I can qualify it. If I could validate that the problem was server wide
and this occurrance was a documented bug or issue, I would implement
this asap.
I do appreciate your assistance. Your suggestion may be a fix to this,
but I'm on a supertanker thats not easy to steer or deploy changes.|||I hear ya. Nevertheless, the patch is a security hotfix and closes a
vulnerability. As another poster mentioned, fixes are cumulative and it is
possible that your issue may have been addressed there.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104353652.360283.184350@.f14g2000cwb.googlegroups.com...
Hi Tom,
The hotfix does not address the particular problem in any
documentation. We have a lot of servers (clustered) and my boss never
lets me do anything on a guess, only until I have found the problem and
I can qualify it. If I could validate that the problem was server wide
and this occurrance was a documented bug or issue, I would implement
this asap.
I do appreciate your assistance. Your suggestion may be a fix to this,
but I'm on a supertanker thats not easy to steer or deploy changes.|||Ken,
Maybe this newsgroup thread will help. Sorry I didn't remember this,
but it looks to me like the same bug you're seeing, and 8.00.818 fixed it:
http://groups-beta.google.com/groups?hl=en&q=8.00.818+fix+bug+kass+-turkish&qt_s=Search+Groups
SK
Ken wrote:
>Hi Tom,
>The hotfix does not address the particular problem in any
>documentation. We have a lot of servers (clustered) and my boss never
>lets me do anything on a guess, only until I have found the problem and
>I can qualify it. If I could validate that the problem was server wide
>and this occurrance was a documented bug or issue, I would implement
>this asap.
>I do appreciate your assistance. Your suggestion may be a fix to this,
>but I'm on a supertanker thats not easy to steer or deploy changes.
>
>|||Jon, I have a sneaky suspicioun that its in the data. When we move the
same data to another table it produces the same error. If we move or
create different data it does not. Weird.
Sunday, February 19, 2012
"IN" clause limitation
Does anybody know what is the limit for the number of values one can
have in a list of expressions (to test for a match) for the IN clause?
have in a list of expressions (to test for a match) for the IN clause?
For example,
SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')
Is there a limit in the number of expressions that can be included in
the list to check for a match to STATE? I know this is a bad example as
there are only 50 states, but my actual query is similar to this, the
only difference being I may have about 1000 values in the 'IN' clause
to test for a match.
TIA!I don't think there is a documented limit and with 1000 values you'll
probably be ok. At some point you'll hit a memory allocation or batch
size limit if you keep adding values to the list.
Is that really the best way you can pass 1000 values? Why don't you put
the data in a table instead?
--
David Portas
SQL Server MVP
--|||Thanks David! You were right on target - I've already hit the memory
allocation limit!
Table sounds good, but I was just tweaking somebody else's code. Thanks
for the idea though! :)
David Portas wrote:
> I don't think there is a documented limit and with 1000 values you'll
> probably be ok. At some point you'll hit a memory allocation or batch
> size limit if you keep adding values to the list.
> Is that really the best way you can pass 1000 values? Why don't you
put
> the data in a table instead?
> --
> David Portas
> SQL Server MVP
> --|||karthik (karthiksmiles@.gmail.com) writes:
> Does anybody know what is the limit for the number of values one can
> have in a list of expressions (to test for a match) for the IN clause?
> For example,
> SELECT au_lname, state
> FROM authors
> WHERE state IN ('CA', 'IN', 'MD')
> Is there a limit in the number of expressions that can be included in
> the list to check for a match to STATE? I know this is a bad example as
> there are only 50 states, but my actual query is similar to this, the
> only difference being I may have about 1000 values in the 'IN' clause
> to test for a match.
1000 values should work. However, the performance for large IN list
is absymal. The problem is not with query execution time, but query
compile time. So putting the values into a table is certainly a good idea.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Thursday, February 9, 2012
"Best" error messages contest
Server: Msg 1015, Level 15, State 1, Line 1
An aggregate cannot appear in an ON clause unless it is in a subquery
contained in a HAVING clause or select list, and the column being aggregated
is an outer reference.Why not to post the statement also?
AMB
"Ian Boyd" wrote:
> Server: Msg 1015, Level 15, State 1, Line 1
> An aggregate cannot appear in an ON clause unless it is in a subquery
> contained in a HAVING clause or select list, and the column being aggregat
ed
> is an outer reference.
>
>|||Cause it was querying a view, with a self aggregate join. It wouldn't make
sense.
Besides, that's not the contest.
> Why not to post the statement also?|||"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%23iiDq5LTFHA.3840@.tk2msftngp13.phx.gbl...
> Server: Msg 1015, Level 15, State 1, Line 1
> An aggregate cannot appear in an ON clause unless it is in a subquery
> contained in a HAVING clause or select list, and the column being
> aggregated is an outer reference.
>
RAISERROR('Two guys walk into a bar...',0,1)|||Q: "Who Killed the Monkey in Raiders of the Lost Ark?"
A: "Bad Date."
"Chris Hohmann" <nospam@.thankyou.com> wrote in message
news:eUuKDXOTFHA.584@.TK2MSFTNGP15.phx.gbl...
> "Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
> news:%23iiDq5LTFHA.3840@.tk2msftngp13.phx.gbl...
> RAISERROR('Two guys walk into a bar...',0,1)
>|||"Michael C#" <howsa@.boutdat.com> wrote in message
news:uQjzyhOTFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Q: "Who Killed the Monkey in Raiders of the Lost Ark?"
> A: "Bad Date."
> "Chris Hohmann" <nospam@.thankyou.com> wrote in message
> news:eUuKDXOTFHA.584@.TK2MSFTNGP15.phx.gbl...
>
Dilbert: What color do you want that database?
PHB: I think mauve has more RAM.
An aggregate cannot appear in an ON clause unless it is in a subquery
contained in a HAVING clause or select list, and the column being aggregated
is an outer reference.Why not to post the statement also?
AMB
"Ian Boyd" wrote:
> Server: Msg 1015, Level 15, State 1, Line 1
> An aggregate cannot appear in an ON clause unless it is in a subquery
> contained in a HAVING clause or select list, and the column being aggregat
ed
> is an outer reference.
>
>|||Cause it was querying a view, with a self aggregate join. It wouldn't make
sense.
Besides, that's not the contest.
> Why not to post the statement also?|||"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%23iiDq5LTFHA.3840@.tk2msftngp13.phx.gbl...
> Server: Msg 1015, Level 15, State 1, Line 1
> An aggregate cannot appear in an ON clause unless it is in a subquery
> contained in a HAVING clause or select list, and the column being
> aggregated is an outer reference.
>
RAISERROR('Two guys walk into a bar...',0,1)|||Q: "Who Killed the Monkey in Raiders of the Lost Ark?"
A: "Bad Date."
"Chris Hohmann" <nospam@.thankyou.com> wrote in message
news:eUuKDXOTFHA.584@.TK2MSFTNGP15.phx.gbl...
> "Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
> news:%23iiDq5LTFHA.3840@.tk2msftngp13.phx.gbl...
> RAISERROR('Two guys walk into a bar...',0,1)
>|||"Michael C#" <howsa@.boutdat.com> wrote in message
news:uQjzyhOTFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Q: "Who Killed the Monkey in Raiders of the Lost Ark?"
> A: "Bad Date."
> "Chris Hohmann" <nospam@.thankyou.com> wrote in message
> news:eUuKDXOTFHA.584@.TK2MSFTNGP15.phx.gbl...
>
Dilbert: What color do you want that database?
PHB: I think mauve has more RAM.
Subscribe to:
Posts (Atom)
