Skip to Content
0

SAP B1 QUERY GENERATOR VARIABLES

Oct 21, 2016 at 07:18 PM

208

avatar image
Former Member

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
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Nagarajan K Oct 21, 2016 at 10:04 PM
1

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 07, 2017 at 09:57 PM
0

Hi

Sorry I forgot I had asked this question!

This solution works perfectly, thanks very much.

Share
10 |10000 characters needed characters left characters exceeded