Skip to Content
0

Regarding a table

Mar 08, 2017 at 01:02 PM

172

avatar image

hello,

can someone tell me how to make the following table

I want supplier name, supplier number, manufacturer, spend (net invoice value) and date.

Can someone put a query how to make it

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

2 Answers

Nagarajan K Mar 08, 2017 at 01:46 PM
0

Hi,

Try this query,

SELECT T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode],T3.[FirmName], T0.[DocTotal] FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] INNER JOIN OMRC T3 ON T2.[FirmCode] = T3.[FirmCode] WHERE T0.[DocDate] between [%0] and [%1]

Regards,

Nagarajan

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

Hi Sir,

To get spend (net invoice) value can we subtract values between OPCH table & ORPC table.

Also, Can you suggest what joint to put on OMRC table to get values for manufacturer

Thankful for your replies.

0
Nagarajan K Mar 09, 2017 at 01:47 PM
0

Try this,

SELECT T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode],T3.[FirmName], T0.[DocTotal] FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] INNER JOIN OMRC T3 ON T2.[FirmCode] = T3.[FirmCode] WHERE T0.[DocDate] between [%0] and [%1]

UNION ALL

SELECT T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode],T3.[FirmName], -T0.[DocTotal] FROM ORPC T0 INNER JOIN RPC1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] INNER JOIN OMRC T3 ON T2.[FirmCode] = T3.[FirmCode] WHERE T0.[DocDate] between [%0] and [%1]

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

Hi

When entered dates and a supplier number and which has the following columns:

  • supplier name
  • Supplier number

  • manufacturer
  • total spend = Total spend as in total net invoiced value.
  • date
  • 0

    thanks but both query not woring

    0

    Could you share your query and its result? What you mean supplier number?

    0

    Thanks for reply

    T0.[CardCode] 'supplier number'....

    Hi

    Can you create a new report which allows you to enter dates and a supplier number and which has the following columns:

  • supplier name
  • Supplier number

  • manufacturer
  • total spend
  • date
  • Thanks


    0

    Hi Sir,

    The below query is working fine in SQL but some of the values are coming in negative. Can you suggest why? Also can you suggest how to make this query work in SAPB1

    SELECT T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode],T3.[FirmName], T0.[DocTotal] FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] LEFT JOIN OMRC T3 ON T2.[FirmCode] = T3.[FirmCode] WHERE T0.[DocDate] between '2006-01-01' and '2017-01-01' and T0.[CardCode]= 'V1010' UNION ALL SELECT T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode],T3.[FirmName], -T0.[DocTotal] FROM ORPC T0 INNER JOIN RPC1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] LEFT JOIN OMRC T3 ON T2.[FirmCode] = T3.[FirmCode] WHERE T0.[DocDate] between '2006-01-01' and '2017-01-01' and T0.[CardCode]= 'V1010'

    0

    Any error message while running above query in Query generator? Could you share your query result here?

    0