Can somebody smarter than I am tell me why this query doesn't work?

  • SELECT Substring(J.MAP_NUM, 1, 4) as MAP_NUM_LIMITED,

    (SELECT Count(DISTINCT K.GPS_ID) FROM JUT K

    WHERE Substring(K.MAP_NUM, 1, 4) = Substring(J.MAP_NUM, 1, 4))

    as ATTCH_COUNT FROM JUT J GROUP BY Substring(J.MAP_NUM, 1, 4)

  • Why.... ????

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • What happens to make it "not work"? Looks to me that it's valid, although it looks like it should be rewritten as

    select Substring(J.MAP_NUM, 1, 4) as MAP_NUM_LIMITED,

    Count(DISTINCT K.GPS_ID) as ATTCH_COUNT

    FROM JUT J

    GROUP BY Substring(J.MAP_NUM, 1, 4)

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • I get an "Internal SQL Error".

  • I'd tell you why if I knew the question. Well I was a little late on the submit. Anyway Matt is right it looks like it should work, although his query is cleaner.

  • Matt Miller (2/26/2008)


    What happens to make it "not work"? Looks to me that it's valid, although it looks like it should be rewritten as

    select Substring(J.MAP_NUM, 1, 4) as MAP_NUM_LIMITED,

    Count(DISTINCT K.GPS_ID) as ATTCH_COUNT

    FROM JUT J

    GROUP BY Substring(J.MAP_NUM, 1, 4)

    That way works though, thank you.

  • Good! glad that worked....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • The reason for the error is that it couldn't resolve the inline sub-query without doing the aggregate, but couldn't do the aggregate without resolving the sub-query. Mainly seems to happen on self-referent sub-queries. The solution is to just query and aggregate the table directly, as the suggested query did.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • When you are posting a question, it helps to include the question.

  • Michael Valentine Jones (2/26/2008)


    When you are posting a question, it helps to include the question.

    The question was in the title.

  • All that shows in the title is:

    Can somebody smarter than I am tell me why...

  • Michael Valentine Jones (2/26/2008)


    All that shows in the title is:

    Can somebody smarter than I am tell me why...

    Oh, well if you look at it in the actual list you can see the whole question.

  • Sean Grebey (2/26/2008)


    Michael Valentine Jones (2/26/2008)


    All that shows in the title is:

    Can somebody smarter than I am tell me why...

    Oh, well if you look at it in the actual list you can see the whole question.

    Yes, but it is easier to just ignore the post and move on to one that is easier to read.

Viewing 13 posts - 1 through 12 (of 12 total)

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