cancel
Showing results for 
Search instead for 
Did you mean: 

Invalid Column Name query error

former_member264311
Participant
0 Kudos

Good Day Experts,

Can you help me to my code. The only wrong in my code was when i put parameters of ItemgroupName. Please see my codes below

declare @column as varchar(max)
declare @Query as varchar(max)
DECLARE @To varchar(15)
Declare @GroupNam NvarChar(20) = /* SELECT FROM OITB X0 WHERE X0.ItmsGrpNam =*/ '[%1]'
Set @To =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate <= */ convert(varchar(15),'[%0]',112)
set @column= STUFF((Select '],['+whscode from OWHS FOR XML PATH('')),1,2,'') +']'
set @Query='select * from (select w.ItemCode,m.Itemname,z.ItmsgrpCode,s.Whscode ,sum(w.Inqty-w.Outqty) Stock
from oinm w
inner join oitm m on m.itemcode=w.itemcode
inner join OWHS s on s.WhsCode=w.Warehouse
inner join oitb z on z.ItmsgrpCod=m.ItmsGrpCod
Where convert(varchar(15),w.Docdate,112) <= convert(varchar(15),'+@To+',112) and convert (NvarChar(20),z.ItmsgrpNam)= convert(NvarChar(20),'+@GroupNam+')
group by w.ItemCode,z.ItmsgrpNam,m.itemname,s.Whscode) fg
Pivot (Sum(Stock) for
Whscode in ('+@column+')) AS pvtb'
EXECUTE (@Query)

Accepted Solutions (0)

Answers (2)

Answers (2)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this,

declare @column as varchar(max)

declare @Query as varchar(max)

DECLARE @To varchar(15)

Declare @GroupNam NvarChar(20)

Set @GroupNam = /* SELECT FROM OITB X0 WHERE X0.ItmsGrpNam =*/ '[%1]'

Set @To =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate <= */ convert(varchar(15),'[%0]',112) set @column= STUFF((Select '],['+whscode from OWHS FOR XML PATH('')),1,2,'') +']' set @Query='select * from (select w.ItemCode,m.Itemname,z.ItmsgrpCode,s.Whscode ,sum(w.Inqty-w.Outqty) Stock

from oinm w inner join oitm m on m.itemcode=w.itemcode inner join OWHS s on s.WhsCode=w.Warehouse inner join oitb z on z.ItmsgrpCod=m.ItmsGrpCod Where convert(varchar(15),w.Docdate,112) <= convert(varchar(15),'+@To+',112) and convert (NvarChar(20),z.ItmsgrpNam)= convert(NvarChar(20),'+@GroupNam+')

group by w.ItemCode,z.ItmsgrpNam,m.itemname,s.Whscode) fg Pivot (Sum(Stock) for Whscode in ('+@column+')) AS pvtb' EXECUTE (@Query)

Johan_H
Active Contributor
0 Kudos

Hi,

It used to be that B1 query parameters had to be used in the correct order. So [%0] before [%1], [%1] before [%2], etc. You added the new parameter at the top, and that way [%1] comes before [%0].

Maybe that is the issue.

Regards,

Johan