Sheldon Livingston
asked on
CAST issue with SQL 2000
SQL = "select somast.fsono, somast.fduedate, soitem.fdesc, sum(fquantity) as doorCount " & _
"from somast left outer join soitem on somast.fsono = soitem.fsono " & _
"where fstatus = 'Open' " & _
"and ((upper(substring(fdesc, 1,2)) = 'SE')) " & _
"group by somast.fsono, somast.fduedate, cast(soitem.fdesc as nvarchar(16)) " & _
"order by somast.fduedate"
The code above gives me the error:
Column 'soitem.fdesc' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
soitem.fdesc is a text field... thus trying to work around "text" limitations.
Thoughts?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sorry as first SQL in comment ID: 42114842 will not work, use the second SQL in same comment instead.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ryan... you state "why do you need group by cast(soitem.fdesc as nvarchar(16)) but you didn't select this field?" I did select it... it is the third field selected.
I'm having to cast as SQL 2000 won't group by a text field.
I'm having to cast as SQL 2000 won't group by a text field.
so is there any of the suggestions above work for you?
ASKER
Thank you! I swore I tried that... apparently not though.
i think you have selected some other comment that actually i have posted the same thing earlier as the answer?
Looks like Ryan Chong was racing to answer and posted incorrect code. Mine was both correct and provided additional explanation. At best Ryan Chong should get assist points.
was racing to answer and posted incorrect codemay i know which part is incorrect?
The part:
"group by somast.fsono, somast.fduedate, cast(soitem.fdesc as nvarchar(16)) " & _
in ID: 42114843 and ID: 42114848 is the same.if you mean the use of Inner join and Left outer join, that's based on the logic /requirement by asker. definitely that may affect the end results but to solve the original issue, the "group by" mentioned above is enough.
You acknowledged the error in your third post: "sorry as first SQL in comment ID: 42114842 will not work, use the second SQL in same comment instead. "
well, I'm referring to ID: 42114843 not ID: 42114842
You posted 3 times very quickly. Mine came in just afterwards. My comment was correct, explained the need to match values in the select and group by clauses and offered a note on the join. Due to the initial incorrect solution, you probably created confusion. I don't know why this is such a big deal to you.
I was trying to look for supporting guidelines for this scenario but apparently there's no such article to describe such scenario (I was surprised as I may have seen it before somewhere)
to conclude, this is not about the point... but in general, the first whom post the solution should be accepted. despite yours and mine got difference in the joining condition, but as explained, comment ID: 42114843 did resolve the original error of:
which obviously is a grouping issue.
I think someone post the right thing first deserves the credits (at least some!)
Due to the initial incorrect solution, you probably created confusionit's because I have posted the 2nd comment hence i can't edit the first one.
to conclude, this is not about the point... but in general, the first whom post the solution should be accepted. despite yours and mine got difference in the joining condition, but as explained, comment ID: 42114843 did resolve the original error of:
Column 'soitem.fdesc' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
which obviously is a grouping issue.
I think someone post the right thing first deserves the credits (at least some!)
I don't know why this is such a big deal to you.not really a big deal, but i have explained it as above. good day all.
try this instead:
Open in new window
or
Open in new window