Prompting for user input in nested select statements

[Former Member]

I recently rewrote a query to use a nested select statement instead of specifying every SELECT field on the GROUP BY line. Here's the query which works perfectly with hard-coded values of '030', '01/01/11', and '12/31/11'.

SELECT T0.[CardName] AS Customer, T0.[CardCode] as 'Cust ID', T0.[Phone1] as Phone, T0.[CntctPrsn] as 'Contact Person', T0.[Address], T0.[City], T0.[State1] as State, T0.[ZipCode] as 'Zip Code', T0.[Country], T1.[TotalSales]

FROM OCRD T0

INNER JOIN

(SELECT I.[CardCode] AS CardCode, SUM(I.[DocTotal]) AS TotalSales

FROM OINV I

WHERE left (I.[CardCode], 3) = '030' AND (I.[DocDate] >= '01/01/11' AND I.[DocDate] <= '12/31/11')

GROUP BY I.[CardCode]) T1

ON T0.[CardCode] = T1.[CardCode]

ORDER BY T0.[CardName]

When I try to prompt for the left 3 characters of the CardCode (or the dates), ie.

WHERE left (I.[CardCode], 3) = [%0] AND (I.[DocDate] >= '01/01/11' AND I.[DocDate] <= '12/31/11')

I get an error "Column 'OCRD.CardName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause".

It's like putting a user variable in the inner SELECT made it part of the outer SELECT which is exactly what I was trying to avoid by re-writing this query with the inner SELECT.

Can anyone explain what SQL Server is doing here and how to fix it?

[Former Member · Accepted solution]

Hi,

Try:

SELECT T0.CardName AS Customer, T0.CardCode as 'Cust ID', T0.Phone1 as Phone, T0.CntctPrsn as 'Contact Person', T0.Address, T0.City, T0.State1 as State, T0.ZipCode as 'Zip Code', T0.Country, SUM(T1.DocTotal) TotalSales

FROM OCRD T0

LEFT JOIN OINV T1 ON T0.CardCode = T1.CardCode

WHERE left (T1.CardCode, 3) = '[%0\]' AND T1.DocDate >= '01/01/11' AND T1.DocDate <= '12/31/11'

GROUP BY T0.CardCode,T0.CardName,T0.Phone1, T0.CntctPrsn, T0.Address, T0.City, T0.State1, T0.ZipCode, T0.Country

ORDER BY T0.CardName

Thanks,

Gordon

[Former Member]

Thanks Gordon. That's how I originally wrote the query and it works fine. But I was disturbed that I had to GROUP BY every field in my SELECT statement when I really only wanted to group by CardCode. So I did some research and came up with this where the inner select still groups by only CardCode and still takes user input. I don't really understand why you need the commented SELECT statements in the SET lines, but you do. Something about using real table fields for variables.

DECLARE @startDate datetime

DECLARE @endDate datetime

DECLARE @rep varchar(10)

SET @rep /* SELECT T0.[CardCode] FROM ORDR T0 WHERE T0.[CardCode] */ = '[%0]'

SET @startDate /* SELECT T0.[DocDate] FROM OINV T0 WHERE T0.[DocDate] */ = '[%1]'

SET @endDate /* SELECT T0.[DocDate] FROM OINV T0 WHERE T0.[DocDate] */ = '[%2]'

SELECT T0.[CardName] AS Customer, T0.[CardCode] as 'Cust ID', T0.[Phone1] as Phone, T0.[CntctPrsn] as 'Contact Person', T0.[Address], T0.[City], T0.[State1] as State, T0.[ZipCode] as 'Zip Code', T0.[Country], T1.[TotalSales]

FROM OCRD T0

INNER JOIN

(SELECT I.[CardCode] AS CardCode, SUM(I.[DocTotal]) AS TotalSales

FROM OINV I

WHERE left (I.[CardCode], 3) = @rep AND (I.[DocDate] >= @startDate AND I.[DocDate] <= @endDate)

GROUP BY I.[CardCode]) T1

ON T0.[CardCode] = T1.[CardCode]

ORDER BY T0.[CardName]

FOR BROWSE