Skip to Content
0

How to run this query on in sap business one?

Jun 14, 2017 at 01:48 PM

118

avatar image

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

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

3 Answers

Best Answer
datta phulse Jun 19, 2017 at 02:41 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Danilo Kasparian Jun 14, 2017 at 02:15 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Jun 15, 2017 at 12:02 AM
0

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

Show 1 Share
10 |10000 characters needed characters left 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)
0