February 26, 2008 at 11:05 am
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)
February 26, 2008 at 11:20 am
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
February 26, 2008 at 11:25 am
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?
February 26, 2008 at 11:27 am
I get an "Internal SQL Error".
February 26, 2008 at 11:28 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 26, 2008 at 11:28 am
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.
February 26, 2008 at 11:50 am
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?
February 26, 2008 at 11:55 am
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
February 26, 2008 at 1:18 pm
When you are posting a question, it helps to include the question.
February 26, 2008 at 1:20 pm
Michael Valentine Jones (2/26/2008)
When you are posting a question, it helps to include the question.
The question was in the title.
February 26, 2008 at 2:11 pm
All that shows in the title is:
Can somebody smarter than I am tell me why...
February 26, 2008 at 2:17 pm
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.
February 26, 2008 at 3:22 pm
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