Results 1 to 10 of 10

Thread: Query behaviour

  1. #1
    Join Date
    Dec 2005
    Posts
    3

    Unanswered: Query behaviour

    Hi there,
    I wonder if one of you worthy folks can help me out with some strange behaviour exhibited by a piece of SQL. Its my first post here , so please be gentle.

    Here is my simple example :-

    <my test table>

    create table test
    (ind int,
    message varchar(255))

    insert into test (ind, message) values
    (1,'date=01/06/2006')

    insert into test (ind, message) values
    (1,'date=20/12/2005')
    insert into test (ind, message) values
    (2,'test')


    The first query is

    select * from test t1
    where t1.ind in (select max(ind) from test t2
    where t2.ind = t1.ind
    and t2.message like 'date=%' )


    fine.... 2 rows

    second query

    select * from test t1
    where t1.ind =1
    and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10)),103) > getdate()


    fine same 2 rows...

    but If I try to combine the 2 clauses in


    select * from test t1
    where t1.ind in (select max(ind) from test t2
    where t2.ind = t1.ind
    and t2.message like 'date=%' )
    and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10)),103) > getdate()



    I get a
    Server: Msg 241, Level 16, State 1, Line 1
    Syntax error converting datetime from character string.

    Please can anyone help me on this?

    thanks

    Simon

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    problem is you try to convert MESSAGE (varchar) to datetime datatype but you have 'test' inserted in your table
    so 'test' string can not be converted to datetime datatype

    Code:
    select SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) from test
    
    01/06/2006
    20/12/2005
    test
    then this fails:
    Code:
    select convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10)),103) from test
    
    Server: Msg 241, Level 16, State 1, Line 1
    Syntax error converting datetime from character string.

  3. #3
    Join Date
    Dec 2005
    Posts
    3
    Hi there,

    thanks for the prompt reply. I've already considered this...

    the second query eliminates the bad data row...

    select * from test t1
    where t1.ind =1
    and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10)),103) > getdate()



    The nested query in query 1 is trying to do the same thing - but doesn't work for some reason

    thanks

    Simon

  4. #4
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Hmmm,
    I played with it but it's mystery to me. Who can explain this:

    table and data:
    Code:
    create table test
    (ind int,
    message varchar(255))
    
    insert into test (ind, message) values
    (1,'date=01/06/2006')
    insert into test (ind, message) values
    (1,'date=20/12/2005')
    insert into test (ind, message) values
    (2,'test')
    now I have 2 statements which returns the same data:
    Code:
     select * 
     from test
     where message like 'date=%'
    
    ind	message
    -----------------------
    1	date=01/06/2006
    1	date=20/12/2005
    Code:
     select * from test t1
     where t1.ind in (select max(ind) from test t2
    		  where t2.ind = t1.ind
    		  and t2.message like 'date=%')
    
    ind	message
    -----------------------
    1	date=01/06/2006
    1	date=20/12/2005
    when I use first select as subquery (temp table) it runs fine:
    Code:
    select * 
    from 
    (
     select * 
     from test
     where message like 'date=%'
    ) test
    where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103)  > getdate()
    
    ind	message
    -----------------------
    1	date=01/06/2006
    1	date=20/12/2005
    when I use secnd select as subquery it fails:
    Code:
    select * 
    from 
    (
     select * from test t1
     where t1.ind in (select max(ind) from test t2
    		  where t2.ind = t1.ind
    		  and t2.message like 'date=%')
    ) test
    where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103)  > getdate()
    
    Server: Msg 241, Level 16, State 1, Line 1
    Syntax error converting datetime from character string.
    problem is in where clause:
    Code:
    ...
    where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103)  > getdate()
    but why?

  5. #5
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    her we go----------
    Code:
    select *
    from test t1
    where 
    message like 'date=%'
    and t1.ind=(select max(ind) from test t2
    where t2.ind = t1.ind
    and t2.message like 'date=%' ) 
    and CONVERT(datetime,(SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)),103)>getdate()
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    that's nice but you missed my point. it worked also with 3rd select I posted. What I'd like to know is why 4th statement fails if select statement is the same:

    select * from
    (
    ...
    ) where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103)

    only difference (comparing to 3rd) is subquery. but it returns the same result... for both (3rd and 4th statement)
    so I'd say: I run same query against same data but result is not the same.

  7. #7
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    see this two codes first
    Code:
    --this will work
    select *
    from test t1
    where 
    message like 'date=%'
    and  t1.ind=(select max(ind) from test t2
    where t2.ind = t1.ind
    and t2.message like 'date=%' ) 
    and CONVERT(datetime,(SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)),103)>getdate()
    
    ---this will not work
    select *
    from test t1
    where 
     t1.ind=(select max(ind) from test t2
    where t2.ind = t1.ind
    and t2.message like 'date=%' ) 
    and CONVERT(datetime,(SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)),103)>getdate()
    and message like 'date=%'
    the reason is sql engine filter data based on first condition(ie messege like 'date=%') then it process 'convert' clause in the where clause

    Insecond case it doing the opposite
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    ok so what's happening in this statement?
    Code:
    select * 
    from 
    (
     select * from test t1
     where t1.ind in (select max(ind) from test t2
    		  where t2.ind = t1.ind
    		  and t2.message like 'date=%')
    ) test
    where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103)
    I assume first of all it convert Message to datetime. But it should convert just
    01/06/2006
    20/12/2005
    as subquery returns just those two records. there's no reason for failure or am I wrong?

  9. #9
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Quote Originally Posted by madafaka
    ok so what's happening in this statement?
    Code:
    select * 
    from 
    (
    select * from test t1
    where t1.ind in (select max(ind) from test t2
    		 where t2.ind = t1.ind
    		 and t2.message like 'date=%')
    ) test
    where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103)
    I assume first of all it convert Message to datetime. But it should convert just
    01/06/2006


    20/12/2005
    as subquery returns just those two records. there's no reason for failure or am I wrong?

    take the execution plan of that query(Ctrl+L) and see the Argument: in table scan.U can see how sql server query is processing.

    I welcome more comments from SQL server gurus
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  10. #10
    Join Date
    Dec 2005
    Posts
    3

    resolution

    I finally found the answer to this. I got it from MS (via MSDN)

    "The query engine is free to evaluate predicates in whatever order it deems fit. If you need to control the order, you can do so via a CASE expression (but this may slow things down a bit)... try:

    select * from (
    select * from test t1
    where t1.seq in (select max(seq) from test t2
    where t2.ind = t1.ind
    and t2.message like 'date=%'
    group by t2.ind)) test
    where
    CASE WHEN message like 'date=%' THEN
    CASE WHEN convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
    10)),103) > getdate()
    THEN 1
    ELSE 0
    END
    ELSE 0
    END = 1
    "

    While not a solution as such a good work around. The key to the above is query predicates - the workaround forces this. So, good enough for me.

    thanks

    Simon

    Hope this helps others out

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •