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

[datta1990]

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

[Johan_H · Accepted solution]

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

[datta1990 · Accepted solution]

Many many thanks

Regards,

Datta Phulse

[Johan_H · Accepted solution]

You're welcome.

Please close this thread by selecting best answer.

Regards,

Johan

[datta1990 · Accepted solution]

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

[Johan_H · Accepted solution]

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