Skip to Content
avatar image
Former Member

SAP B1 Query declare variable problem

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
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jun 06, 2017 at 01:32 PM

    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
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 06, 2017 at 02:13 PM

    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!

    Add comment
    10|10000 characters needed characters exceeded