SAP B1 Query declare variable problem

[former_member239601]

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

[former_member233854]

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

[former_member239601]

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!