Skip to Content

query worked on sql but not on sap business one.Help me for This 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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

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

    Hi,

    In my opinion it should work this way you did buut din`t work here as well... it is weird, anyway I managed to do this way and it worked

    DECLARE @FRDATE DATE = /* SELECT FROM dbo.ADOC T0 WHERE T0.updatedate >= */  '[%0]'
    DECLARE @TODATE DATE = /* SELECT FROM dbo.ADOC T0 WHERE T0.updatedate <= */  '[%1]' 
    
    
    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 @FRDATE and @TODATE
    
    
    Order by T1.u_name ,T0.docdate
    
    Add comment
    10|10000 characters needed characters exceeded

  • Jun 06, 2017 at 10:49 AM

    Hi,

    Is the error coming while running the query?

    Or the error is in the results?

    Regards,

    Jitin

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 06, 2017 at 11:14 AM

    hi Jitin sir,

    below attachment

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 06, 2017 at 02:04 PM

    Hello Datta,

    Which B1 and SQL Server version are you using?

    I use your initial query (only change the date interval) on my test environment and I did not face any issue.

    Regards,
    Eduardo Rezende
    SAP B1 support

    Add comment
    10|10000 characters needed characters exceeded