problem with a query

[Former Member]

brought succesfully the origin of the BP according to the last invoce

the problem i have is that in the following query it does not appear records that do not have specified a cardcode at the field that is placed under

Item Master Data->Purchasing Data(Tab)->suggested BP

i use the following code

this code brings me 88 records(duplicate each one)

SELECT

T0.DOCNUM AS 'u03A4u03B9u03BCu03BFu03BBόu03B3u03B9u03BF u0395u03B9u03C3u03B1u03B3u03C9u03B3ήu03C2',

T3.CardName,

(Select s5.name from ocry s5 inner join ocrd s6 on s5.code=s6.country inner join opch s7 on s6.cardcode=s7.cardcode where s7.docentry=max(s2.docentry)) as 'u03A7ώu03C1u03B1 u03A0u03C1u03BFu03BCu03B7u03B8u03B5u03C5u03C4ή u03A4u03B9u03BCu03BFu03BBu03BFu03B3ίu03BFu03C5',

T0.DOCDATE,

T1.ItemCode,

(Select s2.numatcard from opch s2 where s2.docentry=max(s1.docentry)) as 'u0391u03C1u03B9u03B8u03BCόu03C2 u0391u03BDu03B1u03C6u03BFu03C1άu03C2 u03A0u03C1u03BFu03BCu03B7u03B8u03B5u03C5u03C4ή',

T2.U_INTCODE,

T1.Quantity,

T1.Price,

T1.LineTotal,

sum(T2.BWeight1*T1.Quantity) AS 'u0392άu03C1u03BFu03C2 u0393u03C1u03B1u03BCu03BCήu03C2'

--case when

--(Select T2.CARDCODE from OITM t2 where t2.ITEMCODE=(t9.ITEMCODE)) is not null then

--(Select t2.CARDCODE from oITM t2 where t2.ITEMCODE=(t9.ITEMCODE)) else 0 end as '@@'

from oinv t0

full outer join inv1 t1 on T1.DocEntry = T0.DocEntry

full outer JOIN OITM T2 on T2.ItemCode = T1.ItemCode

inner JOIN ITM1 T9 on T9.ItemCode = T1.ItemCode

full outer JOIN OCRD T3 on T2.CardCode = T3.CardCode

INNER JOIN CRD1 T4 ON T0.CardCode = T4.CardCode

inner join ocry t8 on t8.code=t4.country

full outer join pch1 s1 on t1.itemcode = s1.itemcode

full outer join opch s2 on s1.docentry=s2.docentry

WHERE T0.DocNum = '1403408'

GROUP BY

T3.CardName,

T1.ItemCode,

T1.Quantity, T1.Price, T1.LineTotal,T0.DOCNUM,T0.DOCDATE, t1.linenum,T0.NUMATCARD, T8.Name,T2.U_INTCODE

this code brings me 43 records. one record in the invoice is twice and it does not come up only one record comes up from the query

the same query as above with the only difference at the beginning

SELECT distinct

T0.DOCNUM AS 'u03A4u03B9u03BCu03BFu03BBόu03B3u03B9u03BF u0395u03B9u03C3u03B1u03B3u03C9u03B3ήu03C2',

T3.CardName,.......

[former_member204969 · Accepted solution]

You probably need something like this:

SELECT
T0.DOCNUM AS 'u03A4u03B9u03BCu03BFu03BBu03CCu03B3u03B9u03BF u0395u03B9u03C3u03B1u03B3u03C9u03B3u03AEu03C2',
T3.CardName,
(Select s5.name from ocry s5 inner join ocrd s6 on s5.code=s6.country inner join opch s7 on s6.cardcode=s7.cardcode where s7.docentry=
(select max(s1.docentry) from pch1 s1 where t1.itemcode = s1.itemcode)) as 'u03A7u03CEu03C1u03B1 u03A0u03C1u03BFu03BCu03B7u03B8u03B5u03C5u03C4u03AE u03A4u03B9u03BCu03BFu03BBu03BFu03B3u03AFu03BFu03C5',
T0.DOCDATE,
T1.ItemCode,
(Select s2.numatcard from opch s2 where s2.docentry=
 (select max(s1.docentry) from pch1 s1 where t1.itemcode = s1.itemcode)) as 'u0391u03C1u03B9u03B8u03BCu03CCu03C2 u0391u03BDu03B1u03C6u03BFu03C1u03ACu03C2 u03A0u03C1u03BFu03BCu03B7u03B8u03B5u03C5u03C4u03AE',
T2.U_INTCODE,
T1.Quantity,
T1.Price,
T1.LineTotal,
sum(T2.BWeight1*T1.Quantity) AS 'u0392u03ACu03C1u03BFu03C2 u0393u03C1u03B1u03BCu03BCu03AEu03C2'
from oinv t0
inner join inv1 t1 on T1.DocEntry = T0.DocEntry
inner JOIN OITM T2 on T2.ItemCode = T1.ItemCode
inner JOIN OCRD T3 on T0.CardCode = T3.CardCode
INNER JOIN CRD1 T4 ON T0.CardCode = T4.CardCode and T4.Adrestype='B'and T4.Linenum=0
inner join ocry t8 on t8.code=t4.country
WHERE T0.DocNum = '1403408'
GROUP BY
T3.CardName,
T1.ItemCode,
T1.Quantity, T1.Price, T1.LineTotal,T0.DOCNUM,T0.DOCDATE, t1.linenum,T0.NUMATCARD, T8.Name,T2.U_INTCODE

[Former Member · Accepted solution]

definetely istvan!

thank a lot both of you!

[jitin_chawla]

Hi,

The case query where you are checking for the cardcode in OITM table (Item master data > Purchasing tab > preferred BP in your query has been commented.

Check this if this is what you are expecting as a result :

SELECT distinct
T0.DOCNUM AS 'u03A4u03B9u03BCu03BFu03BBu03CCu03B3u03B9u03BF u0395u03B9u03C3u03B1u03B3u03C9u03B3u03AEu03C2',
T3.CardName,
(Select s5.name from ocry s5 inner join ocrd s6 on s5.code=s6.country inner join opch s7 on s6.cardcode=s7.cardcode where s7.docentry=max(s2.docentry)) as 'u03A7u03CEu03C1u03B1 u03A0u03C1u03BFu03BCu03B7u03B8u03B5u03C5u03C4u03AE u03A4u03B9u03BCu03BFu03BBu03BFu03B3u03AFu03BFu03C5',
T0.DOCDATE,
T1.ItemCode,
(Select s2.numatcard from opch s2 where s2.docentry=max(s1.docentry)) as 'u0391u03C1u03B9u03B8u03BCu03CCu03C2 u0391u03BDu03B1u03C6u03BFu03C1u03ACu03C2 u03A0u03C1u03BFu03BCu03B7u03B8u03B5u03C5u03C4u03AE',
T2.U_INTCODE,
T1.Quantity,
T1.Price,
T1.LineTotal,
sum(T2.BWeight1*T1.Quantity) AS 'u0392u03ACu03C1u03BFu03C2 u0393u03C1u03B1u03BCu03BCu03AEu03C2',
case when
(Select T2.CARDCODE from OITM t2 where t2.ITEMCODE=(t9.ITEMCODE)) is not null 
or (select T2.Cardcode from OITM t2 where t2.itemcode = (t9.itemcode)) <> '' then
T2.CARDCODE ELSE 0 END as '@@'
from oinv t0
full outer join inv1 t1 on T1.DocEntry = T0.DocEntry
full outer JOIN OITM T2 on T2.ItemCode = T1.ItemCode
inner JOIN ITM1 T9 on T9.ItemCode = T1.ItemCode
full outer JOIN OCRD T3 on T2.CardCode = T3.CardCode
INNER JOIN CRD1 T4 ON T0.CardCode = T4.CardCode
inner join ocry t8 on t8.code=t4.country
full outer join pch1 s1 on t1.itemcode = s1.itemcode
full outer join opch s2 on s1.docentry=s2.docentry
WHERE T0.DocNum = '1403408'
GROUP BY t9.itemcode, t2.cardcode,
T3.CardName,
T1.ItemCode,
T1.Quantity, T1.Price, T1.LineTotal,T0.DOCNUM,T0.DOCDATE, t1.linenum,T0.NUMATCARD, T8.Name,T2.U_INTCODE

Kind Regards,

Jitin

SAP Business One Forum Team

[Former Member]

Hi Jitin

unfortunatelly, the query now brings me again only once the data...

it means that in an Invoice, i have twice the same product but with this query it comes up only with one record