HELP ON QUERY

Former Member — Tue, 04 May 2021 12:39:09 GMT

Hello expert

One query with some items on ORDR table

Some items are linked to a BOM some not

I want to make a summary of all items committed and this is not possible except making a query

So i want to see some lines on RDR1 WITH some lines LINKED TO ITT1 and some lines not linked

I also want to see only one lines on ITT1 and not all (with the query below I am not able to fliter)

The line I want to view is only item name starting with 'Velo'

SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[Quantity], T8.[OnHand] as 'En stock', T1.[ItemCode], T1.[Dscription], (select Descr from ufd1 where TableID = 'ORDR' AND Fldvalue=T0.[U_ZDEPART]) as 'FICHE DEPART IMPRIMEE',T3.e_mail,T6.[Code] FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] and T1.[Price]>1000 INNER JOIN OCRD T3 ON T0.[CardCode]=T3.[CardCode] left outer join OITT T5 ON T5.Code=T1.ItemCode left outer join ITT1 T6 ON T5.[Code] = T6.[Father] left outer join OITM T7 ON T6.[Code]=T7.ItemCode left join OITW T8 ON T8.ITEMCode=T6.Code and T8.[WhsCode]='01' WHERE T0.[DocStatus] ='O'

Thanks a lot if you can help to simplify this query

Re: HELP ON QUERY

Former Member — Tue, 04 May 2021 13:00:11 GMT

I found solution

SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[Quantity], T8.[OnHand] as 'En stock', T1.[ItemCode], T1.[Dscription], (select Descr from ufd1 where TableID = 'ORDR' AND Fldvalue=T0.[U_ZDEPART]) as 'FICHE DEPART IMPRIMEE',T3.e_mail,T6.[Code] FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] and T1.[Price]>1000 INNER JOIN OCRD T3 ON T0.[CardCode]=T3.[CardCode] left outer join OITT T5 ON T5.Code=T1.ItemCode left outer join ITT1 T6 ON T5.[Code] = T6.[Father] left join OITM T7 ON T6.[Code]=T7.ItemCode left join OITW T8 ON T8.ITEMCode=T6.Code and T8.[WhsCode]='01' WHERE T0.[DocStatus] ='O' and (T7.[ItemName] like 'Velo%%' or T6.[Code] is Null)

topic Re: HELP ON QUERY in Application Development and Automation Discussions

HELP ON QUERY

Re: HELP ON QUERY