Using variables in SQL queries for SAP Business One is straight forward for simple select statements but what about the more complex variables or if you want to declare a variable for your SQL?
Answer: You have to get a bit more creative! See the example below.
To select a from and to date as a variable in a declare statement you have to add a nifty bit of extra code.
/*
/SELECT FROM [dbo].[VPM1] T0/
declare @fromdate as datetime
/* WHERE */
set @fromdate = /* T0.DueDate */ ‘[%0]’
/SELECT FROM [dbo].[VPM1] T1/
declare @tilldate as datetime
/* WHERE */
set @tilldate = /* T0.DueDate */ ‘[%1]’
select OVPM.docnum, VPM1.dueDate,VPM1.CheckNum, VPM1.Branch,
VPM1.BankCode, VPM1.CheckSum, OVPM.CardName,
case
when OPCH.docnum is null then VPM2.docEntry
else OPCH.docnum
end as ‘docnum’
from OVPM INNER join VPM1 on OVPM.docnum = VPM1.docnum
left join VPM2 LEFT JOIN OPCH ON VPM2.DocEntry = OPCH.DocEntry
on OVPM.docnum = VPM2.docnum
where OVPM.Canceled = ‘N’ AND VPM1.DueDate >=@fromdate AND
VPM1.DueDate <=@tilldate
*/
1 Dash 1 can provide you and your SAP Business One users with excellent training of the usage of SQL and Crystal Reports. If you would like to find out more simply enter your details in the boxes below or call us.
