Parameters in SAP B1 SQL Query

[krishnam_mathur]

Hi Freinds,

I need help to add parameters in pivot.

Check the below query:

declare @month integer

declare @month_2 integer

declare @startdate datetime

declare @enddate datetime

declare @costcenter varchar(20)

set @month_2= 3--/* t3.finncPriod=*/ '[%0]'

set @month=@month_2-1

set @costcenter='88115100'

set @startdate= '03/01/2019'--/*t0.docdate from oinm t0 where t0.docdate>=*/'[%1]'

set @enddate= '03/31/2019'--/*t0.docdate from oinm t0 where t0.docdate>=*/'[%2]'

select * from

( select t0.name+'-Budget' as 'toPivot', t4.AcctCode as 'GLAccount',t4.AcctName,isnull(t2.DebLTotal,0) as 'value' from obgs t0 inner join obgt t1 on t0.AbsId=t1.Instance inner join bgt1 t2 on t2.Instance=t1.Instance and t2.AcctCode=t1.AcctCode and t2.Line_ID=@month inner join oact t4 on t4.AcctCode=t2.AcctCode where t0.name=@costcenter union all select t0.name+'-Actual' as 't0Pivot',t4.AcctCode as 'GLAccount',t4.AcctName,isnull(sum(debit-credit),0) as 'value' from jdt1 t3 left join oact t4 on t4.AcctCode=t3.Account left join obgs t0 on t0.Name=t3.ProfitCode left join obgt t1 on t1.Instance=t0.AbsId and t4.AcctCode=t1.AcctCode left join bgt1 t2 on t1.Instance=t2.Instance and t2.AcctCode=t1.AcctCode and t2.line_ID=2 where t3.refdate between @startdate and @enddate and t4.ExportCode is not null and t0.name=@costcenter group by t0.name,t4.AcctCode,t4.AcctName

) a pivot (sum(a.value) for a.topivot in

([88115100-Budget], [88115100-Actual])) as pivottable

---Here rather than 88115100-Budget, I want @costcenter-Budget, @costcenter-Actual...

Please guys help me out

[Johan_Hakkesteegt]

Hi,

You can do it by creating the entire query as a string. Something like this:

--all your variables here
declare @costcenter varchar(20) = '88115100'
DECLARE @SQL AS NVARCHAR(MAX) = 'build your query here in such a manner that it remains a string and that final column name would work something like this: (' + @costcenter + '-Budget)'
EXEC(@SQL)

This probably won't work in B1, but you may be able to make it work if you put this code in a stored procedure, and then call that stored procedure in B1.

Regards,

Johan