Skip to Content
0

Invalid Column Name query error

Jan 27, 2017 at 07:53 AM

39

avatar image

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)

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Johan Hakkesteegt Jan 27, 2017 at 09:08 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Jan 27, 2017 at 09:13 AM
0

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)

Share
10 |10000 characters needed characters left characters exceeded