Skip to Content
0
Former Member
Oct 17, 2011 at 03:22 PM

Prompting for user input in nested select statements

13 Views

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?