Skip to Content
0
Former Member
Sep 15, 2011 at 12:07 PM

how to bring the BP's country of origin

163 Views

hello all

i have done the following query and the problem i have is that i cannot bring the BP's country of origin

SELECT

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

T0.DOCDATE,

max(T1.[ItemCode]),

T0.NUMATCARD,

T2.U_INTCODE,

T1.[Quantity],

T1.[Price],

T1.[LineTotal],

SUM(T2.[BWeight1]*T1.[Quantity]) AS 'u0392άu03C1u03BFu03C2 u0393u03C1u03B1u03BCu03BCήu03C2',

T3.[CardName]

FROM OINV T0

full outer JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

full outer JOIN OITM T2 ON T1.ItemCode = T2.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

WHERE T0.[DocNum] = '1403408'

GROUP BY

T3.[CardName],

T1.[ItemCode],

T1.[Quantity], T1.[Price], T1.[LineTotal],T0.DOCNUM,T0.DOCDATE,T2.U_INTCODE,t1.linenum,T0.NUMATCARD

ORDER BY T1.[ItemCode]

the actual problem is that some BP's have many addresses and some of them have adrresses in different countries.

so i want to get the last invoice's country of origin of the BP

i have done many efforts such as

...

...

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',

..

..

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

inner join pch1 s1 on t1.itemcode = s1.itemcode

inner join opch s2 on s1.docentry=s2.docentry

but the results are not the expected. it does not bring me all the recorns from the invoice

do you have any idea?