Weird Query output

  • Hi,

    There is a strange happening in one of my queries :

    SELECT TOP 50 * FROM Activity Where SRID in (SELECT SRID FROM ServiceRequest  WHERE SRNumber = '2-795764856') ORDER BY Created ASC

    When I run this query, I get the output.

    There should not be any output as there is no SRID in the ServiceRequest View(its a view, not a table). It should result in a error.

    However, when I am using the table aliases as under, then it gives me error :

    Msg 207, Level 16, State 1, Line 2

    Invalid column name 'SRID'.

    SELECT TOP 50 * FROM Activity a Where a.SRID in (SELECT b.SRID FROM ServiceRequest b WHERE b.SRNumber = '2-795764856') ORDER BY Created ASC

    Any thoughts ?

    Kishore

     

  • Check the access path :

    Maybe it's trying to generate the same path like the one for this query :

    SELECT

    TOP 50 *

    FROM

    Activity A

    Where

    exists (SELECT *

    FROM ServiceRequest SR

    WHERE SR.SRNumber = '2-795764856'

    and SR.SRID = A.SRID)   -- i hope you have an index for SRID on ServiceReq.

    ORDER

    BY Created ASC

    It might be an optimizer issue

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply