Skip to Content

How to run this query on in sap business one?

Hi Team,

How to run this query when using parameter {using the last updated filed rather than the created by field } ?

SELECT T1.[U_NAME]'Created BY', T0.[DocNum], T0.[DocDate], T0.[Cardname]'Customer Name', T2.[SlpName], T0.[DocTotal], T0.[GrosProfit],T0.comments FROM OQUT T0 INNER JOIN OUSR T1 ON T0.[UserSign] = T1.[USERID] INNER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode] WHERE T0.[DocDate] between [%0] and [%1] Order by T1.u_name ,T0.docdate

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Jun 19, 2017 at 02:41 PM

    thanks for help me for query i want below output,

    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 a.[Created BY],

    A.[DocNum],

    A.[DocDate],

    A.[Customer Name],

    A.[SlpName],

    A.[DocTotal],

    A.[GrosProfit],

    A.Comments,

    A.[Updated Date] as 'Last update date' from

    ( 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,UpdateTS DESC)) as 'updated By',

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

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

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

    A where a.[Updated Date] between @FRDATE and @TODATE

    Order by A.[Created BY] ,A.docdate

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 14, 2017 at 02:15 PM

    I didn`t understand what you want to do.

    There is a similar thread you opened some time ago

    https://answers.sap.com/questions/216337/query-worked-on-sql-but-not-on-sap-business-onehel.html?childToView=216508#answer-216508

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 15, 2017 at 12:02 AM

    Hi,

    Try this,

    SELECT T1.[U_NAME]'Created BY', T0.[DocNum], T0.[DocDate], T0.[Cardname]'Customer Name', T2.[SlpName], T0.[DocTotal], T0.[GrosProfit],T0.comments FROM OQUT T0 INNER JOIN OUSR T1 ON T0.[UserSign2] = T1.[USERID] INNER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode] WHERE T0.[DocDate] between [%0] and [%1] Order by T1.u_name ,T0.docdate

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

    • thanks sir,

      i want below requirement.

      when i have try this Query also as per requirement. below query.

      query not working properly ? Help me for same.

      /* select * from OQUT x */ DECLARE @FROM AS DATETIME DECLARE @TO AS DATETIME SET @FROM = /* x.updatedate */ [%0] SET @TO = /* x.updatedate */ [%1] SELECT B.[Created BY], B.[DocNum], B.[DocDate], B.[Customer Name], B.[SlpName], B.[GrosProfit], B.[Comments], B.[Updated By], B.[Updated Date] from ( 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 AND ( A.[updatedate] BETWEEN @FROM AND @TO) ORDER BY A.UpdateDate DESC))'Updated By', (select top 1 A.UpdateDate from ADOC A where A.Docentry = T0.Docentry and A.ObjType = 23 AND ( A.[updatedate] BETWEEN @FROM AND @TO) 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 @FROM AND @TO) group by T1.u_name ,T0.docdate,T0.[DocNum],T0.[Cardname],T2.[SlpName],T0.[DocTotal],T0.[GrosProfit],T0.comments, t0.DocEntry )B where B.[Updated Date] is not NULL

      Regards,

      Datta P.

      1.png (96.1 kB)