Forcing order of evaluation
-
I recognize that a where clause with something like "WHERE A=B and C=D" you might not be able to count whether or not it will evaulate the "C=D" if A<>B.
But there are times you need to and I thought it was possible by doing nested selects. But it isn't working. Here is a real example:
select *
from
(
select *
from SBT..SBT s
where s.NatChainCd=86 and IsNumeric(s.ISAControlNbr)=1
) as x
where Convert(int,x.ISAControlNbr) = 404
This is failing with an error that
Syntax error converting the varchar value 'KROGER_282003' to a column of data type int.
And indeed that is a value for ISAControlNbr, however, IsNumeric does return a zero for rows with that value.
I would certainly have thought that nested like this it would absolutely ensure that the inner WHERE was evaulated before passing any candidate rows to the outer WHERE.
Other than a temp table, is there a way to ensure this kind of check will work? It's a pretty basic need -- if there's a numeric in the character field I want to determine its integer value and compare it to a number. The fields are randomly zero filled (i.e. different lengths) so character comparisons are difficult.
-
OK,
I tried your query with Ramdom test data and it works on my system!
can you check your query plan?
* Noel -
Check your data, whether there are any invalid numeric data, such as invalid characters (for numeric), such as space (behind or in-front), comma etc. Try to use LTRIM() and RTRIM().
Regards,
kokyan 
-
The query plan is below. It obviously combines the two queries into one.
as to the question about invalid data -- well, that's kind of the point, ISNUMERIC ought to catch it. But in this case it is showing the first specific case which is non-numeric, and it is clearly non-numeric, and in fact if I evaluate IsNumeric for that field for that row it returns zero. What's happened is that it has rearranged the order of evaluation (the query plan shows this).
It may be because this is an indexed column.
|--Filter(WHERE
.[NatChainCd]=86))|--Bookmark Lookup(BOOKMARK
[Bmk1000]), OBJECT[SBT].[dbo].[SBT] AS ))|--Parallelism(Gather Streams)
|--Index Scan(OBJECT
[SBT].[dbo].[SBT].[ISAGSSeqNbrIndex] AS ), WHEREConvert(.[ISAControlNbr])=404 AND isnumeric(.[ISAControlNbr])=1)) -
can you rewrite the statement as
select *
from SBT..SBT s
where s.NatchainCd = 86
and
(case when isnumeric(ISAControlNbr)=1) THEN
case when Convert(int,ISAControlNbr) = 404 then1 else 0 end
else 0 end) = 1
and see what happened?
* Noel -
That's an interesting construct, and it worked.
I thought for some reason you couldn't nest case statements, but apparently you can.
Rather ugly in a syntax sense, but perhaps you've got it! Thanks.
-
Hi,
what version of SQLS are you running? I tested on SQL 2k and I agree with noeld, on random data your query works fine. I wrote a similar query for one of our production DB tables varchar column, which I know contains mostly (but not only) numeric values. Table has more than 1M records. Query runs fine, as long as the "isnumeric" precedes the condition that requires conversion. As soon as the order of conditions is reversed, I'm getting conversion error. BTW, even implicit conversion worked perfect for me, so there was no difference between
Convert(int,x.ISAControlNbr) = 404
and
x.ISAControlNbr = 404.
Vladan
-
I'm running SQL 2000 Enterprise SP3A on a 4-way system with 32G of memory (all of which might change its optimizations I guess). The table in question has 25,127,151 rows. As noted, there is an index on this column, and I think that may also impact this -- it's trying to use the index and (erroneously?) pulling up the equality check before the IsNumeric.
It's been way too long since I saw a SQL standard, so I do not know if there is some guarenteed order of evaluation at any level (e.g. not just same precedence operators in an expression but in terms of consolidating where clauses amongst nested queries.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply