Skip to Content

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Mar 08, 2017 at 06:27 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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