Skip to Content
0

SAP B1 Query declare variable problem

Jun 06, 2017 at 01:11 PM

68

avatar image
Former Member

Hi everyone

I've written a query in SQL Server Management Studio that works fine, however I need to run it in SAP B1 (9.0 PL11) Query Generator which has the well known issue of not handling variables.

I've adapted the query as per below but still I get an error, in SAP, of 'must specify table to select from'.

Could someone take a moment to look at my code and point out where I am going wrong with my code?

-- select t0.docdate
-- from ordr t0
-- where t0.docdate = [%0]

declare @timevar date
set @timevar = cast ('[%0]' as date)

select
ordr.doctime,
rdr1.docentry 'SO 500..',
rdr1.basecard,
ocrd.cardname,
rdr1.linenum,
rdr1.itemcode,
rdr1.dscription,
oitb.itmsgrpnam,
rdr1.Quantity,
convert (date, ordr.docdate,3) 'docdate',
convert (date, rdr1.shipdate,3) 'line del date',
oslp.slpname,
CONVERT (NUMERIC (8), ordr.u_iis_intsenderid) 'PORTALREF?',
convert (time, convert(time, convert(varchar(10), ordr.doctime / 100)+ ':' + convert(varchar(10), ordr.doctime % 100)),8) doctm

from rdr1 inner join ordr on rdr1.docentry = ordr.docentry inner join ocrd on rdr1.basecard = ocrd.cardcode inner join oslp on ocrd.SlpCode = oslp.SlpCode inner join oitm on rdr1.itemcode = oitm.itemcode inner join oitb on oitb.itmsgrpcod = oitm.ItmsGrpCod

where
ordr.docdate = @timevar
and
oitm.itmsgrpcod in ('242', '243')

order by oitm.itmsgrpcod
SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Danilo Kasparian Jun 06, 2017 at 01:32 PM
0

I don`t think SAP works with Convert TIME, if you use your logic but without converting to time it works then you can convert to time in the next step if there is any, a report for example.

convert(varchar(10), ordr.doctime / 100)+ ':' + convert(varchar(10), ordr.doctime % 100) doctm
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jun 06, 2017 at 02:13 PM
0

Hi Danilo

Thank you very much indeed for your help, this has worked perfectly now.. I assumed my code was wrong on the variable declaration, not actually one of the values I wanted to return.

Thanks once again!

Share
10 |10000 characters needed characters left characters exceeded