Prev: jobangebote hannover stellenangebote stellenanzeigen Chemielaborant Chemielaborantin stellenangebote wien stellenangebote lehrstellen stellenausschreibung deutschland stellenangebote verkauf
Next: SQL Wishes , Correct path
From: Mukut on 2 Jul 2008 03:25
Hi,

In a table I have the value like following:

X_CREATE_SOURCE INTEGRATION_ID
Sales SR0000000082
Sales SR0000000083
Sales SR0000000084
Sales SR0000000085
Sales SR0000000086
Sales SR0000000087
Sales 23000.0000084
Sales 220000.000084
Sales 13000000.0084
Sales 5600000.00084

when I run the below query it is working fine and I am getting the
integer output
select convert(INT,substring(INTEGRATION_ID,3,10))
from <tablename>
where LEFT(INTEGRATION_ID,2)='SR' and X_CREATE_SOURCE ='Sales'

but when I run the below query it is throwing an error

select max(convert(INT,substring(INTEGRATION_ID,3,10)))
from <tablename>
where LEFT(INTEGRATION_ID,2)='SR' and X_CREATE_SOURCE ='Sales'

error, for example:
Conversion failed when converting the varchar value '0092.5011' to
data type int.

It seems that max() is working on the whole dataset and the filter is
not working.

Any suggestion is this regrd would be appreciated.

Thanks
Mukut
From: Moby on 2 Jul 2008 04:05
On 2 Jul., 09:25, Mukut <hidev...(a)gmail.com> wrote:
> Hi,
>
> In a table I have the value like following:
>
> X_CREATE_SOURCE INTEGRATION_ID
> Sales                   SR0000000082
> Sales                   SR0000000083
> Sales                   SR0000000084
> Sales                   SR0000000085
> Sales                   SR0000000086
> Sales                   SR0000000087
> Sales                   23000.0000084
> Sales                   220000.000084
> Sales                   13000000.0084
> Sales                   5600000.00084
>
> when I run the below query it is working fine and I am getting the
> integer output
> select convert(INT,substring(INTEGRATION_ID,3,10))
> from <tablename>
> where LEFT(INTEGRATION_ID,2)='SR' and X_CREATE_SOURCE ='Sales'
>
> but when I run the below query it is throwing an error
>
> select max(convert(INT,substring(INTEGRATION_ID,3,10)))
> from <tablename>
> where LEFT(INTEGRATION_ID,2)='SR' and X_CREATE_SOURCE ='Sales'
>
> error, for example:
> Conversion failed when converting the varchar value '0092.5011' to
> data type int.
>
> It seems that max() is working on the whole dataset and the filter is
> not working.
>
> Any suggestion is this regrd would be appreciated.
>
> Thanks
> Mukut

hi kukut
tested out the same and it worked with X_CREATE_SOURCE and
INTEGRATION_ID as varchar(50)
SQL2000 SP4
take probably also a look on the execution plan

if you realy need the result try

select max(value) from (
--your first statement plus alias
select convert(INT,substring(INTEGRATION_ID,3,10)) as value
from <tablename>
where LEFT(INTEGRATION_ID,2)='SR' and X_CREATE_SOURCE ='Sales'
) T1

kr moby
From: Tom Cooper on 2 Jul 2008 13:06
Although what you are doing should not give you the error (because the WHERE
clause is supposed to be processed before the SELECT clause), the only way
to guarentee the order is to use a CASE in your SELECT. The reason for this
is that SQL is allowed to rearrange the order if it determines that doing
the process in some other order will both generate the same result and is
expected to be more efficient. In your example it doesn't generate the same
result (you get an error when you shouldn't), so SQL shouldn't be doing
this. However, it is my understanding the the Microsoft SQL Server group
believes that having the query optimizer test for this kind of problem would
be too expensive for the benefit that is gained, so they don't. And even if
you find a way to rewrite the query without using CASE that works today, it
may fail later with a different SQL Server release or if the distribution of
data in your table changes. So I would rewrite both of these queries as

select CASE WHEN LEFT(INTEGRATION_ID,2)='SR' THEN
convert(INT,substring(INTEGRATION_ID,3,10)) ELSE NULL END
from <tablename>
where LEFT(INTEGRATION_ID,2)='SR' and X_CREATE_SOURCE ='Sales'

and

select max(CASE WHEN LEFT(INTEGRATION_ID,2)='SR' THEN
convert(INT,substring(INTEGRATION_ID,3,10)) ELSE NULL END)
from <tablename>
where LEFT(INTEGRATION_ID,2)='SR' and X_CREATE_SOURCE ='Sales'

That will bullet-proof your code from this problem.

Tom

"Mukut" <hidevraj(a)gmail.com> wrote in message
news:f8e95b76-3dc1-4c6f-a28c-82137fa0eb32(a)l28g2000prd.googlegroups.com...
> Hi,
>
> In a table I have the value like following:
>
> X_CREATE_SOURCE INTEGRATION_ID
> Sales SR0000000082
> Sales SR0000000083
> Sales SR0000000084
> Sales SR0000000085
> Sales SR0000000086
> Sales SR0000000087
> Sales 23000.0000084
> Sales 220000.000084
> Sales 13000000.0084
> Sales 5600000.00084
>
> when I run the below query it is working fine and I am getting the
> integer output
> select convert(INT,substring(INTEGRATION_ID,3,10))
> from <tablename>
> where LEFT(INTEGRATION_ID,2)='SR' and X_CREATE_SOURCE ='Sales'
>
> but when I run the below query it is throwing an error
>
> select max(convert(INT,substring(INTEGRATION_ID,3,10)))
> from <tablename>
> where LEFT(INTEGRATION_ID,2)='SR' and X_CREATE_SOURCE ='Sales'
>
> error, for example:
> Conversion failed when converting the varchar value '0092.5011' to
> data type int.
>
> It seems that max() is working on the whole dataset and the filter is
> not working.
>
> Any suggestion is this regrd would be appreciated.
>
> Thanks
> Mukut


 | 
Pages: 1
Prev: jobangebote hannover stellenangebote stellenanzeigen Chemielaborant Chemielaborantin stellenangebote wien stellenangebote lehrstellen stellenausschreibung deutschland stellenangebote verkauf
Next: SQL Wishes , Correct path