Subquery Help

J

jbphoenix

Registered User.
Local time
Today, 06:23
Joined
Jan 25, 2007
Messages
98
Here is my select statement, I'm using sql server 2005. When I run it, I get Incorrect syntax near ')' on line 17 - (the last line). I new to SQL and trying to create a report in reporting services.

I'm used to using MS Access. Basically, like in Access, when you can choose a query to the be record source for another query. I'm trying to do something like that.

Select [Part Number], [Invt GLG], Sum(PYUsage), Sum(CYUsage)
from
(SELECT [Part Number], [Invt GLG], Qty, [Trans Dt],
'PYUsage'=
case
When Year([Trans Dt]) = '2007' Then [Qty]
Else '0'
End,
'CYUsage'=
case
When Year([Trans Dt]) = '2008' Then [Qty]
Else '0'
End
FROM [tblEMG_LIVE_INVTRAIL-2]
where [Trans Dt] >= N'1/1/2007' and [Qty] < '0')
 
jbphoenix said:
Here is my select statement, I'm using sql server 2005. When I run it, I get Incorrect syntax near ')' on line 17 - (the last line). I new to SQL and trying to create a report in reporting services.

I'm used to using MS Access. Basically, like in Access, when you can choose a query to the be record source for another query. I'm trying to do something like that.

Select [Part Number], [Invt GLG], Sum(PYUsage), Sum(CYUsage)
from
(SELECT [Part Number], [Invt GLG], Qty, [Trans Dt],
'PYUsage'=
case
When Year([Trans Dt]) = '2007' Then [Qty]
Else '0'
End,
'CYUsage'=
case
When Year([Trans Dt]) = '2008' Then [Qty]
Else '0'
End
FROM [tblEMG_LIVE_INVTRAIL-2]
where [Trans Dt] >= N'1/1/2007' and [Qty] < '0')
Click to expand...

I think the End Statements might need to be End Case Statements
 
I tried switching them to End Case but then I just get "Incorrect syntax near the keywork 'case'."

If I run only this part -

(SELECT [Part Number], [Invt GLG], Qty, [Trans Dt],
'PYUsage'=
case
When Year([Trans Dt]) = '2007' Then [Qty]
Else '0'
End,
'CYUsage'=
case
When Year([Trans Dt]) = '2008' Then [Qty]
Else '0'
End
FROM [tblEMG_LIVE_INVTRAIL-2]
where [Trans Dt] >= N'1/1/2007' and [Qty] < '0')

It works fine, but when I add the following select statement I get the error I originally said

Select [Part Number], [Invt GLG], Sum(PYUsage), Sum(CYUsage)
from
 
Try

Case...End AS PYUsage

instead of how you have it. I didn't realize you could alias a field that way, but perhaps it's the cause of the problem.
 
Paul, I tried your suggestion and I still get the same error. I know subqueries are possible. I thought this would be an easy report to try in Reporting Services but apparently I was incorrect. I appreciate everyone's suggestions. They always help get the wheels movin.
 
Subqueries are no problem. After a brief test (SQL Server 2000) I got a similar error until I aliased the subquery:

...where [Trans Dt] >= N'1/1/2007' and [Qty] < '0') AS Whatever

Then I got an error I expected, due to not having a GROUP BY clause. I suspect you'll need to add one.
 
jbphoenix said:
Paul, I tried your suggestion and I still get the same error. I know subqueries are possible. I thought this would be an easy report to try in Reporting Services but apparently I was incorrect. I appreciate everyone's suggestions. They always help get the wheels movin.
Click to expand...

I have a piece of code in a query related to my tblPeople, that has a similar need for an alias. Perhaps it will work here.

Code: 
[FONT=Courier New]    [COLOR=gray]([/COLOR][/FONT]
[FONT=Courier New]       [COLOR=blue]CASE[/COLOR] [/FONT]
[FONT=Courier New]          [COLOR=blue]WHEN[/COLOR] Title [COLOR=gray]IS[/COLOR] [COLOR=gray]NULL[/COLOR] [COLOR=blue]THEN[/COLOR] [COLOR=red]''[/COLOR][/FONT]
[FONT=Courier New]          [COLOR=blue]ELSE[/COLOR] Title [COLOR=gray]+[/COLOR] [COLOR=red]' '[/COLOR][/FONT]
[FONT=Courier New]       [COLOR=blue]END[/COLOR][COLOR=gray])[/COLOR] [COLOR=gray]+[/COLOR] [COLOR=gray]([/COLOR][/FONT]
[FONT=Courier New]       [COLOR=blue]CASE[/COLOR] [/FONT]
[FONT=Courier New]          [COLOR=blue]WHEN[/COLOR] F_Name [COLOR=gray]IS[/COLOR] [COLOR=gray]NULL[/COLOR] [COLOR=blue]THEN[/COLOR] [COLOR=red]''[/COLOR][/FONT]
[FONT=Courier New]          [COLOR=blue]ELSE[/COLOR] F_Name [COLOR=gray]+[/COLOR] [COLOR=red]' '[/COLOR][/FONT]
[FONT=Courier New]       [COLOR=blue]END[/COLOR][/FONT]
[FONT=Courier New][COLOR=gray]     )[/COLOR] [COLOR=gray]+[/COLOR] L_Name [COLOR=blue]AS[/COLOR] FormalName[COLOR=gray],[/COLOR] [/FONT]
 
[FONT=Courier New]Which would interpret to something like:[/FONT]
 
[FONT=Courier New][FONT=Courier New]    [COLOR=gray]([/COLOR][/FONT]
[FONT=Courier New]       [COLOR=blue]CASE[/COLOR] [/FONT]
[FONT=Courier New]          [COLOR=blue]WHEN[/COLOR] Year([Trans Dt]) = '2007' Then [Qty][/FONT]
[FONT=Courier New]          [COLOR=blue]ELSE[/COLOR] [COLOR=red]'0'[/COLOR][/FONT]
[FONT=Courier New]       [COLOR=blue]END[/COLOR][/FONT]
[FONT=Courier New][COLOR=gray]     )[/COLOR] [/FONT][FONT=Courier New][COLOR=blue]AS[/COLOR] 'PYUsage'[COLOR=#808080],[/COLOR][/FONT]
[/FONT]


See if that works.
 
pbaldy said:
Subqueries are no problem. After a brief test (SQL Server 2000) I got a similar error until I aliased the subquery:

...where [Trans Dt] >= N'1/1/2007' and [Qty] < '0') AS Whatever

Then I got an error I expected, due to not having a GROUP BY clause. I suspect you'll need to add one.
Click to expand...

I ran a crude experiment on my SQL Server and was surprised to find that a GROUP BY Statement was not required. I wonder what it decides to GROUP BY when you do not give it instructions?
 
To the best of my knowledge, fields in the SELECT clause must either be part of an aggregate function or included in the GROUP BY clause. I would also be surprised if it didn't require one. What did your SELECT clause look like?
 
Re#1: Subquery Help (WRT Syntax)

WRT syntax:
jbphoenix said:
'PYUsage'=
case
When Year([Trans Dt]) = '2007' Then [Qty]
Else '0'
End,
'CYUsage'=
case
When Year([Trans Dt]) = '2008' Then [Qty]
Else '0'
End
Click to expand...

try
case
When Year([Trans Dt]) = '2007' Then [Qty]
Else '0'
End as PYUsage,

case
When Year([Trans Dt]) = '2008' Then [Qty]
Else '0'
End as CYUsage
 
pbaldy said:
To the best of my knowledge, fields in the SELECT clause must either be part of an aggregate function or included in the GROUP BY clause. I would also be surprised if it didn't require one. What did your SELECT clause look like?
Click to expand...


I was testing the N'<DateString>' Format that jbphoenix described, to see if that was an issue. I have never used that format, and did not know what to expect. I executed the following statement and it succeeded.
Code: 
[COLOR=blue][FONT=Courier New]Select[/FONT][/COLOR]
[FONT=Courier New]  [COLOR=fuchsia]sum[/COLOR][COLOR=gray]([/COLOR]Institution_Count[COLOR=gray]),[/COLOR] [/FONT]
[FONT=Courier New]  N[COLOR=red]'1/1/2007'[/COLOR] As TheDate[/FONT]
[FONT=Courier New][COLOR=blue]From[/COLOR] dbo[COLOR=gray].[/COLOR]Institutions[COLOR=gray];[/COLOR][/FONT]
 
Last edited:
I think that works because

N'1/1/2007' As TheDate

is not a field in the table. The OP's fields are, and I strongly suspect this error will be next:

Column 'FieldName' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

which I got when I changed from that hardcoded field to a field in the table.

tokoloshi, I already suggested that and the OP said it didn't help.
 
Re#2: Subquery Help (WRT Date formats)

jbphoenix said:
When Year([Trans Dt]) = '2007'
When Year([Trans Dt]) = '2008'
where [Trans Dt] >= N'1/1/2007'
Click to expand...

Does the year() function you are using correctly return a string value and does the [trans Dt] field contain the expected string values that you are filtering by?

Should you not be CASTING or CONVERTING these fields to string values?
 
pbaldy said:
I think that works because

N'1/1/2007' As TheDate

is not a field in the table. The OP's fields are, and I strongly suspect this error will be next:

Column 'FieldName' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

which I got when I changed from that hardcoded field to a field in the table.

tokoloshi, I already suggested that and the OP said it didn't help.
Click to expand...


Spot on

I falied to notice that. Adding the Institution name gets the error you refer to. Adding Group By makles it go away, but returns smaller sums.
 
where convert(char(10),[Trans Dt],101) > N'01/01/2007' should give you a better result than where [Trans Dt] >= N'1/1/2007'
 
I have not recreated your table in my system, just did a quick syntax check.

This is how I would probably construct the query.

-- ********************
Select
-- I have prefixed the fields with the aliased table (p) here
p.[Part Number],
p.[Invt GLG],
Sum(p.PYUsage),
Sum(p.CYUsage)
from

(
SELECT
[Part Number],
[Invt GLG],
Qty,
[Trans Dt],
case
-- the year() function returns a numeric value, not a string
When year([Trans Dt]) = 2007 Then [Qty]
Else 0
End as PYUsage,
case
-- the year() function returns a numeric value, not a string
When Year([Trans Dt]) = 2008 Then [Qty]
Else 0
End as CYUsage
FROM [tblEMG_LIVE_INVTRAIL-2]
where
[Trans Dt] > N'01/01/2007' and
[Qty] < 0
) as p -- SQL Server often complains if you do not alias this subquery
 
@PBaldy DEAD Right my man

The SUM() will not work without the group by clause. Haven't test it yet, but makes absolute sense.
 
You must log in or register to reply here.

Similar threads

S
Solved Getting "No Current Record" on query that previously worked
Replies
6
Views
453
Gasman
Gasman
D
Return 0 for SUM if no join was possible
Replies
3
Views
378
Pat Hartman
P
R
Access SQL IIf statement with GROUP BY (no alias utilized)
Replies
2
Views
880
plog
P
A
Filter Records
Replies
1
Views
366
June7
June7
T
Solved IIf returning error
Replies
3
Views
427
xavier.batlle
X

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom