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

[datta1990]

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

[former_member233854 · Accepted solution]

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

[datta1990 · Accepted solution]

Thanks Sir.

[datta1990 · Accepted solution]

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.

[former_member233854 · Accepted solution]

I didn`t understand

[former_member184473]

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

[datta1990]

Thank for reply.

[datta1990]

hi Jitin sir,

below attachment

[datta1990]

please check error

[jitin_chawla]

Hi,

Is the error coming while running the query?

Or the error is in the results?

Regards,

Jitin

[datta1990]

hi Sir,

When Enter the parameter like

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

error came.