Tricky query

[Former Member]

Dear Expert,

Can you pls explain this below query because i want to use this query by doing some changes. It is tricky query.

For me, the tricky part is from "Recent Vendor" .

SELECT 'Delivery Note' as 'Doc Type',T0.docdate as 'Srat date', T0.[DocDate] as'Postingdate', T2.[Name] as'Posting period', T0.[DocNum] as'Doc No.', T0.[CardCode]as 'Carcode',

T0.[CardName] as'Card Name',T0.numatcard as 'Contract number', T1.[ItemCode] as'Item code', T1.[Dscription]as'Item Description',

(case when t1.currency='RMB' then T1.[price] else (T1.[Price]* t0.docrate)end)*  T1.[Quantity]  as 'Sles TO',

T1.[StockPrice] *  T1.[Quantity]  as 'COGS',

T1.[Quantity] as'Quantity', T0.[Comments], T3.[SlpName] 

,t6.ItmsGrpNam,T1.WHSCODE, isnull(a5.cc,t5.cardcode)as 'Vendor',

t8.Name

FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OFPR T2 ON T0.FinncPriod = T2.AbsEntry

INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode

left join OITM t5 on t5.ItemCode=t1.itemcode

LEFT JOIN OITB T6 ON T6.ItmsGrpCod=t5.ItmsGrpCod

LEFT JOIN Ohem T7 ON T7.middleName=t3.Memo

LEFT JOIN Oubr T8 ON T8.Code=t7.branch

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% recent vendor

left join (

select MAX(t4.cardcode)as 'CC',t4.ItemCode

from (

select max(t3.DocDate) as 'sd',

(t1.itemcode),

t3.cardcode

from POR1 t1

inner join OPOR t3 on t1.DocEntry=t3.DocEntry and t3.CANCELED='N'

inner join (

select max(c1.DocDate) as 'md',b1.itemcode from POR1 b1 inner join OPOR c1 on c1.DocEntry=b1.DocEntry where c1.CANCELED='N' and (b1.ItemCode is not null or b1.ItemCode<>'')

group by b1.ItemCode

) t2 on t2.md=t3.docDate and t2.ItemCode=t1.ItemCode

where t1.ItemCode is not null or t1.ItemCode<>''

group by t1.ItemCode,t3.cardcode) t4

group by t4.ItemCode) a5 on a5.ItemCode=t1.ItemCode

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

where t0.doctype='I' and T0.[docdate]>='[%0]' and T0.[docdate]<='[%1]'  and T0.[CardCode] not like 'sc%' and T0.[CardCode] not like 'sc%'/*DN infor*/

Thanks in advance,

Regards,

Dwaraka

[Johan_H]

Hi Dwaraka,

The 'recent vendor' part of the query is a sub query (or embedded query), inside a sub query, inside a sub query. It has been left joined, so it will only show results that can be linked to the DLN1 table, by the ItemCode field.

It appears to give you the largest possible CardCode from the OPOR (purchase order header) table, not counting purchase orders that were canceled.

Is there anything more specific that you need to know ?

Regards,

Johan

[Former Member]

Hi Johan,

Since am Zero in the Sub query in the Join and where conditions.

Will you be able to explain that sub query part in detail, that how it is looped actually ? so that i can understand and i can create it myself in future.

Thanks in advance,

Regards,

Dwaraka

[frank_wang6]

It is better to use WITH statement to do this, much more clear.

Basically, you use WITH to generate two in-memory tables, and and join them.

Check https://msdn.microsoft.com/en-us/library/ms175972.aspx

[Former Member]

I too like CTEs, they are awesome. They help clear up a lot of clutter and make the code much easier to read and understand.

[Former Member]

Hi Frank Wang

Could you pls help me by convert that sub query into WITH statement . so, that i can use that as example to reproduce my other queries.

thanks in advance,

regards,

Dwaraka

[Former Member]

hi Dwaraknath,

        I have some doubts , can u follow me..So that I can text u in direct message

Regards,

K.Ramasamy

[JL23]

What is the purpose of taking discussions offline and exclude the majority who are trying to help you?

The purpose of SCN longterm is to act as a knowledge base where people can learn from other people issues or solve their own issues based on existing SCN content. This can't happen if discussions are taken offline.

[frank_wang6]

WITH A5 AS (

select MAX(t4.cardcode)as 'CC',t4.ItemCode

from (

select max(t3.DocDate) as 'sd',

(t1.itemcode),

t3.cardcode

from POR1 t1

inner join OPOR t3 on t1.DocEntry=t3.DocEntry and t3.CANCELED='N'

inner join (

select max(c1.DocDate) as 'md',b1.itemcode from POR1 b1 inner join OPOR c1 on c1.DocEntry=b1.DocEntry where c1.CANCELED='N' and (b1.ItemCode is not null or b1.ItemCode<>'')

group by b1.ItemCode

) t2 on t2.md=t3.docDate and t2.ItemCode=t1.ItemCode

where t1.ItemCode is not null or t1.ItemCode<>''

group by t1.ItemCode,t3.cardcode) t4

group by t4.ItemCode)

SELECT 'Delivery Note' as 'Doc Type',T0.docdate as 'Srat date', T0.[DocDate] as'Postingdate', T2.[Name] as'Posting period', T0.[DocNum] as'Doc No.', T0.[CardCode]as 'Carcode',

T0.[CardName] as'Card Name',T0.numatcard as 'Contract number', T1.[ItemCode] as'Item code', T1.[Dscription]as'Item Description',

(case when t1.currency='RMB' then T1.[price] else (T1.[Price]* t0.docrate)end)*  T1.[Quantity]  as 'Sles TO',

T1.[StockPrice] *  T1.[Quantity]  as 'COGS',

T1.[Quantity] as'Quantity', T0.[Comments], T3.[SlpName] 

,t6.ItmsGrpNam,T1.WHSCODE, isnull(a5.cc,t5.cardcode)as 'Vendor',

t8.Name

FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OFPR T2 ON T0.FinncPriod = T2.AbsEntry

INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode

left join OITM t5 on t5.ItemCode=t1.itemcode

LEFT JOIN OITB T6 ON T6.ItmsGrpCod=t5.ItmsGrpCod

LEFT JOIN Ohem T7 ON T7.middleName=t3.Memo

LEFT JOIN Oubr T8 ON T8.Code=t7.branch

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% recent vendor

left join A5 ON  a5.ItemCode=t1.ItemCode
where t0.doctype='I' and T0.[docdate]>='[%0]' and T0.[docdate]<='[%1]'  and T0.[CardCode] not like 'sc%' and T0.[CardCode] not like 'sc%'/*DN infor*/

[frank_wang6]

in order to find last vendor, the query is not very optimized, but it is not hard to explain.

1. it finds the last PO date for an given item.

2. and then it based on this last date, it searches again to generate last PO date for each item per CardCode.

3. and it searches again to find the last vendor code per item.

Frank