December 17, 2021 at 2:07 am
Hi
Error - Column '@IND_RLS1.U_VendCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
T1.U_VendCode as "Vendor Code",T1.U_CabNo,T1.U_VendName as "Vendor Name",
T1.U_CustCode as "Cust Code",T1.U_CustName as "Cust Name",Max(T0.DocNum) as "LogBook No",T1.U_ItemCode,Max(T1.U_ItemName),
Max(T4.Docnum) as "A/P Doc No",Max(T4.DocDate) as "A/P Doc Date",
(SELECT Name FROM OCST WHERE Code = (Select BpStateCod from PCH12 T where T.Docentry = T1.U_APDE) and Country = 'IN' ) as "Place",
(Select sum(Quantity) from PCH1 T where T.Docentry = T1.U_APDE and T.ItemCode = T1.U_ItemCode and T.U_BPCode = T1.U_CustCode) as "A/P Qty"
,(Select U_CustGrp from ocrd where cardcode = t1.U_CustCode)
FROM [@IND_ORLS] T0
inner join [@IND_RLS1] T1 on T0.Docentry = T1.DocEntry
Left Join OPCH T4 on T4.DocEntry = T1.U_APDE where T0.U_Date between @_frdate and @_toDate
----------------------------
ALTER PROCEDURE [dbo].[test]
@frDate date,
@toDate date,
@VCode nvarchar(15) = null,
@VName nvarchar(100) = null,
@CCode nvarchar(15) = null,
@CName nvarchar(100) = null,
@CGroup nvarchar(15) = null
as
begin
DECLARE @sSQL NVARCHAR(2000), @Where NVARCHAR(1000) = ''
SET @sSQL =
'SELECT
T1.U_VendCode as "Vendor Code",T1.U_CabNo,T1.U_VendName as "Vendor Name",
T1.U_CustCode as "Cust Code",T1.U_CustName as "Cust Name",Max(T0.DocNum) as "LogBook No",T1.U_ItemCode,Max(T1.U_ItemName),
Max(T4.Docnum) as "A/P Doc No",Max(T4.DocDate) as "A/P Doc Date",
(SELECT Name FROM OCST WHERE Code = (Select BpStateCod from PCH12 T where T.Docentry = T1.U_APDE) and Country = ''IN'' ) as "Place",
(Select sum(Quantity) from PCH1 T where T.Docentry = T1.U_APDE and T.ItemCode = T1.U_ItemCode and T.U_BPCode = T1.U_CustCode) as "A/P Qty"
,(Select U_CustGrp from ocrd where cardcode = t1.U_CustCode)
FROM [@IND_ORLS] T0
inner join [@IND_RLS1] T1 on T0.Docentry = T1.DocEntry
Left Join OPCH T4 on T4.DocEntry = T1.U_APDE where T0.U_Date between @_frdate and @_toDate '
IF @vCode is not null
SET @Where = @Where + 'AND T1.U_VendCode = @_VCode '
IF @vName is not null
SET @Where = @Where + 'AND T1.U_VendName = @_VName '
IF @CCode is not null
SET @Where = @Where + 'AND T1.U_CustCode = @_CCode '
IF @CName is not null
SET @Where = @Where + 'AND T1.U_CustName = @_CName '
--IF @CGroup is not null
--SET @Where = @Where + 'AND T5.U_CustGrp = @_CGroup '
IF LEN(@Where) > 0
SET @sSQL = @sSQL + RIGHT(@Where, LEN(@Where)-3) +
N' group by T1.U_VendCode,T1.U_CabNo,T1.U_VendName,T1.U_Custcode,T1.U_Custname,T1.U_APDE,T1.U_ARDE,T1.U_ItemCode order by T1.U_VendName,T1.U_CustName'
print @sSql
EXEC sp_executesql @sSQL,
N'@_frDate date,@_toDate date,@_VCode nvarchar(15),@_VName nvarchar(100), @_CCode nvarchar(15),@_CName nvarchar(100),@_CGroup nvarchar(15) ',
@_frDate = @frDate,@_todate=@toDate,@_VCode = @VCode, @_VName = @VName, @_CCode = @CCode, @_CName = @CName,@_CGroup = @CGroup
END
Thanks
December 17, 2021 at 2:21 am
If it's not working when it's crazy complex, why not simplify and built it a piece at a time?
The error message is pretty clear. Any time you create a totals/aggregate query, any column that is not an aggregate must appear in the GROUP BY clause.
help us out a little. at least format your code so it's readable. (insert/edit code sample), then paste your code into that window. As it is, it's crazy hard to read.
December 17, 2021 at 4:35 pm
When I run into problems like the one's you're having, I go back to the basics.
The first step is to get out of the dynamic SQL world and get some regular code to work properly first. Then, you can work on making it dynamic and you'll be much more successful.
In fact... I lied a bit. I don't run into problems like the one's you're having because I always start with working code first. Then I convert it to dynamic SQL one parameter at a time.
--Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply