Purchase report

[Former Member]

Hi Experts,

I written the query in query generator,

In selection criteria, Posting Date From and To Date and Profit center,

This is the query;

SELECT T0.[DocNum], T0.[DocDate], T0.[CardName], (T0.[DocTotal] -T0.[VatSum]) AS 'Document Value', T0.[VatSum], T0.[DocTotal] FROM OPCH T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocDate] >= [%0] AND T0.[DocDate] <= [%1] AND T1.[OcrCode] =[%2]

error:

No data was found in the selection criteria.

wat can i do....

can u suggest me....

thanks in advance.

[Former Member · Accepted solution]

Try This

SELECT T0.DocNum, T0.DocDate, T0.CardName, (T0.DocTotal -T0.VatSum) AS 'Document Value', T0.VatSum, T0.DocTotal FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.DocDate >= %0 AND T0.DocDate <= %1 AND T1.OcrCode =%2

Regards

[Former Member · Accepted solution]

Hi,

i got the error like this....

1)(Microsoft)SQL Native client)(SQL Server)Conversion failed when converting datetime from character string.

query:

SELECT T0.DocNum, T0.DocDate, T0.CardName, (T0.DocTotal -T0.VatSum) AS 'Document Value', T0.VatSum, T0.DocTotal FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.DocDate >= '%0' AND T0.DocDate <= '%1' AND T1.OcrCode ='%2'

[Former Member · Accepted solution]

Try This

SELECT T0.DocNum, T0.DocDate, T0.CardName, (T0.DocTotal -T0.VatSum) AS 'Document Value', T0.VatSum, 
T0.DocTotal FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.DocDate >= '[%0]' AND T0.DocDate <= '[%1]' AND T1.OcrCode ='[%2]'

[Former Member · Accepted solution]

Hi,

same error is coming again....

[Former Member · Accepted solution]

Which SAP Version you are using?

Tell me the situation what's your exact requirement?

Regards

[Former Member · Accepted solution]

SAP B1 2007A PL 10,

i need purchase report profit center wise, if i give profit center code, all the invoice details should come in the report.

thanks,

[Former Member · Accepted solution]

Dear

You once again check the above query. Its working fine on my database.......

Regards,

[Former Member · Accepted solution]

Hi!

where do u running the query ?

Run it on SAP Query Manager not in SQL Window.

>>'[%0]' will not work in SQL Query Window

[Former Member]

Your original query has correct syntax. If you got no result, you have to check your data. Run a simple query to check it out h records you may get or not:

SELECT * FROM PCH1 T1

WHERE T1.DocDate >= '[%0\]' AND T1.DocDate <= '[%1\]' AND T1.OcrCode ='[%2\]'

Thanks,

Gordon

[former_member325312]

hello

pls try this query

SELECT T0.[DocNum], T0.[DocDate], T0.[CardName], (T0.[DocTotal]-T0.[VatSum]) , T1.[OcrCode] as[Document Value], T0.[VatSum], T0.[DocTotal] FROM OPCH T0 , INV1 T1 WHERE T0.DocEntry = T1.DocEntry and T0.[DocDate] =[%0] and T0.[DocDate] =[%1] and T1.[OcrCode] =[%12] GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardName], T0.[DocTotal], T0.[VatSum], T0.[VatSum], T1.[OcrCode], T0.[DocTotal]

Edited by: Jennifer Anderson on Jul 29, 2009 11:18 AM