Skip to Content
0
Former Member
Sep 16, 2011 at 07:48 AM

problem with a query

22 Views

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