Q53: How to report a single line where multiple lines are reported

[Former Member]

Dear All,

I simply want to report one line per customer to leave me with a list of customers and their email addresses................

SELECT

t0.docnum,

t0.docdate,

t1.itemcode,

t1.quantity,

t0.docstatus,

t0.cardname,

t2.e_maill

FROM oinv t0

inner join inv1 t1 on t0.docentry = t1.docentry

inner join ocpr t2 on t0.cardcode = t2.cardcode

WHERE t0.docdate >= '[%0]' and t0.docdate <= '[%1]' and t1.itemcode >= '[%2]' and t1.itemcode <= '[%3]'

ORDER BY t1.itemcode

Many thanks,

Robin

[Former Member · Accepted solution]

Hi Robin,

If this post is about SAP B1 then I'm afraid you have entered it in the wrong forum.

The reason you are getting multiple lines per customer is because you are selecting transaction level fields such as document number, date, item code, quantity etc. When these are including you will get a row for each item line on each transaction for each BP.

If you wanted to return one row per customer then limit the columns you are selecting and add a DISTINCT section in to ensure one row. For example, in your SELECT statement make it:

SELECT DISTINCT

t0.CardName,

t2.e_mail

You should then only get one row returned per BP who purchased the selected items within the selected date range.

If this hasn't answered your question, it still may be better to mark it as answered and re-log it in the SAP B1 forum so that the other experts can help, however hopefully this has solved your issue.

Regards,

Adrian