Skip to Content
avatar image
Former Member

SAP B1 QUERY GENERATOR VARIABLES

Hi

I am trying to bring a query that uses variables into SAP B1 from SSMS, but am getting stuck on the 'must specify table to select from' error.

I have read through a number of resources on this site reference this error, and used the guidance from the post 'working with SQL Query Generator Variables' to try and address but I am still getting the error.

I was hoping someone could review my syntax and point out where I am going wrong.

Syntax as below

/** Select from [INV1] A **/
declare @startdate datetime 
/* WHERE */
set @startdate = /*A.DOCDATE*/ '[%0]'


/** SELECT FROM [INV1] A **/
declare @enddate datetime 
/* WHERE */
set @enddate = /*A.DOCDATE*/ '[%1]' 


declare @reviewperiod int 
set @reviewperiod = '[%2]'


declare @itmgrpnam varchar(30) 
set @itmgrpnam = '[%3]'


; with invs as
(
select a.itemcode, sum(a.quantity) as 'IQty' from inv1 a where a.docdate between @startdate and @enddate group by a.ItemCode
)
,
crs as
(
select b.itemcode, sum(b.quantity) as 'Cqty' from rin1 b where b.docdate between @startdate and @enddate group by b.ItemCode
)
,
nett as
(
select aa.itemcode, aa.iqty, bb.Cqty, aa.iqty - bb.cqty as 'Nett' from invs aa inner join crs bb on bb.itemcode = aa.itemcode group by aa.itemcode, aa.iqty, bb.cqty
)
,
mthavg as
(
select
ab.itemcode,
ab.iqty,
ab.cqty,
ab.nett,
ab.nett / @reviewperiod as 'Mthavg'


from nett ab
)
,
wdavg as
(
select  
ac.ItemCode,
ac.iqty,
ac.cqty,
ac.nett,
ac.mthavg,
ac.mthavg / 20 'WDavg'


from mthavg ac
)


select
oitm.cardcode,
ad.itemcode,
oitb.itmsgrpnam,
ad.iqty,
ad.cqty,
ad.nett,
(ad.mthavg) 'MonthAvg',
(ad.wdavg) 'WorkDayAvg',
round ((ad.wdavg * oitm.leadtime),0) 'prop_min_stock',
oitm.MinOrdrQty as 'Supl.MinOrdQty',
oitm.MinLevel as 'MinLevel',
oitm.iscommited as 'CommittedVal',
(select sum(onhand) from oitw where oitw.ItemCode = ad.ItemCode and oitw.WhsCode = '01') 'onhand',
isnull ((select sum (openqty) from por1 where por1.ItemCode = ad.ItemCode),0) '#onPO',
oitm.InvntItem as 'StockY/N',
oitm.VALIDFOR as 'ActiveY/N'


from wdavg ad inner join oitm on oitm.itemcode = ad.itemcode inner join oitb on oitm.itmsgrpcod = oitb.itmsgrpcod


where oitm.cardcode is not null and oitm.CardCode <> 'des02_s' and round ((ad.wdavg * oitm.leadtime),0) <> oitm.MinLevel and oitb.ItmsGrpNam = @itmgrpnam


order by
oitm.cardcode, oitb.itmsgrpnam, ad.itemcode, oitm.MinOrdrQty, oitm.MinLevel, oitm.iscommited, oitm.InvntItem, oitm.VALIDFOR
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Oct 21, 2016 at 10:04 PM

    Hi,

    Where did you got value for below declared variable,

    declare @reviewperiod Varchar(max) set @reviewperiod = '[%3]'

    Also try below format;

    Declare @startdate as datetime Declare @enddate as datetime Declare @itmgrpnam as varchar(30) set @startdate = ( select min(Ta.[DocDate]) from oINV ta where Ta.[DocDate] >= [%0]) set @enddate = ( select max(Tb.[DocDate]) from oINV tb where Tb.[DocDate] <= '[%1]') set @itmgrpnam = (select max(Tc.[ItmsGrpNam]) from OITB tC where Tc.[ItmsGrpNam] = '[%2]')

    Thanks

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 07, 2017 at 09:57 PM

    Hi

    Sorry I forgot I had asked this question!

    This solution works perfectly, thanks very much.

    Add comment
    10|10000 characters needed characters exceeded