Skip to Content
0

query worked on sql but not on sap business one.Help me for This Query.

Jun 06, 2017 at 10:39 AM

86

avatar image

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

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

4 Answers

Best Answer
Danilo Kasparian Jun 06, 2017 at 01:16 PM
0

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
Show 3 Share
10 |10000 characters needed characters left characters exceeded

Thanks Sir.

0

but when enter the date 12.06 2017 to 13 06 2017

output: 12 06.2017 and 13 06 2017 show data .......but

available data

15.06.2017 last update date for document number.

0

I didn`t understand

0
Jitin Chawla
Jun 06, 2017 at 10:49 AM
0

Hi,

Is the error coming while running the query?

Or the error is in the results?

Regards,

Jitin

Show 1 Share
10 |10000 characters needed characters left characters exceeded

hi Sir,

When Enter the parameter like

WHERE a.updatedate between [%0] and [%1]

error came.

0
datta phulse Jun 06, 2017 at 11:14 AM
0

hi Jitin sir,

below attachment


updatedate.png (36.3 kB)
Show 1 Share
10 |10000 characters needed characters left characters exceeded

please check error

updatedate.png (36.3 kB)
0
Eduardo Rezende
Jun 06, 2017 at 02:04 PM
0

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


query.png (24.5 kB)
Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thank for reply.

1