Skip to Content
0

Sub query working in SQL and SAP B1 without parameter but when parameters error in Sap B1

Mar 07, 2017 at 02:49 PM

313

avatar image

Hello,

The subquery in the following query works fine in SAPB1 and SQL without parameters but when parameters are added the subquery in the query does not work in SAP B! but still works in SQL

SELECT T0.[DocEntry], T0.[DocNum] 'SO NO',

T0.[DocDate] 'Doc Date', T0.[CardCode] 'Customer Code',

T0.[CardName] 'Customer Name',

T1.[SlpName] 'Account Manager',

T0.[DocTotal] 'Total'

,T2.u_name 'Created By',

(SELECT top 1 o.U_NAME

from adoc A inner join OUSR O on a.UserSign2 = o.Userid

where a.DocEntry = t0.DocEntry and a.objtype = 17

order by a.UpdateDate desc)'Last Updated by'

FROM ORDR T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

Left Join OUSR T2 on T2.userid = T0.Usersign

WHERE T0.[DocDate] BETWEEN '2016-03-06' AND '2017-03-06' AND T0.[DocStatus]='O'

Can you tell me the reason to make it working in both cases

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Johan Hakkesteegt Mar 08, 2017 at 06:27 AM
1

Hi,

You may use a special syntax that can be interpreted by B1's sql parser:

/* select * from ORDR x */
DECLARE @FROM AS DATETIME
DECLARE @TO AS DATETIME
SET @FROM = /* x.DocDate */ [%0]
SET @TO = /* x.DocDate */ [%1]

SELECT T0.[DocEntry]
      ,T0.[DocNum] 'SO NO'
      ,T0.[DocDate] 'Doc Date'
      ,T0.[CardCode] 'Customer Code'
      ,T0.[CardName] 'Customer Name'
      ,T1.[SlpName] 'Account Manager'
      ,T0.[DocTotal] 'Total'
      ,T2.u_name 'Created By'
      ,(select top 1 o.U_NAME
        from adoc A 
            inner join OUSR O on a.UserSign2 = o.Userid
        where a.DocEntry = t0.DocEntry 
          and a.objtype = 17
        order by a.UpdateDate desc)'Last Updated by'
FROM ORDR T0 
     INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
     LEFT JOIN OUSR T2 on T2.userid = T0.Usersign
WHERE T0.[DocDate] BETWEEN @FROM AND @TO
  AND T0.[DocStatus]='O'

This is an example, but you can use parameters like this anywhere in the query.

Regards,

Johan

Show 4 Share
10 |10000 characters needed characters left characters exceeded

Many many thanks

Regards,

Datta Phulse

0

You're welcome.

Please close this thread by selecting best answer.

Regards,

Johan

0

Hello Sir,

Again same issue.

The subquery in the following query works fine in SAPB1 and SQL without parameters but when parameters are added the subquery in the query does not work in SAP B! but still works in SQL

This new Query.

SELECT T1.[U_NAME]'Created BY', T0.[DocNum],

T0.[DocDate], T0.[Cardname]'Customer Name',

T2.[SlpName], T0.[DocTotal],

T0.[GrosProfit],T0.comments,

(select U_NAME from Ousr where Ousr.UserId in(select top 1 A.UserSign2 from ADOC A where A.Docentry = T0.Docentry and A.ObjType = 23 ORDER BY A.UpdateDate DESC))'Updated By',

(select top 1 A.UpdateDate from ADOC A where A.Docentry = T0.Docentry and A.ObjType = 23 ORDER BY A.UpdateDate DESC)'Updated Date' FROM OQUT T0

INNER JOIN OUSR T1 ON T0.[UserSign] = T1.[USERID]

INNER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode]

inner join ADOC A on T0.docentry = A.DocEntry

WHERE a.updatedate between '2017-05-02' and '2017-06-06'

Order by T1.u_name ,T0.docdate

Regards

Datta Phulse

0

Hi,

Pleas apply the principle as demonstrated in the answer to this question:

/* select * from ADOC x */
DECLARE @FROM AS DATETIME
DECLARE @TO AS DATETIME
SET @FROM = /* x.DocDate */ [%0]
SET @TO = /* x.DocDate */ [%1]

/* your query with parameters @FROM and @TO here */

Regards,

Johan

0