single_row 가져올때

*single_row 가져올때

**Max인 값으로 서브쿼리 하지말고

**row_number로 채번하여 가져오도록 해보자

 

WITH tmp AS

(SELECT 1 AS seq_no

        ,10 AS unit_cost

        ,'a' AS item

  FROM   dual

  UNION ALL

  SELECT 2 AS seq_no

        ,5 AS unit_cost

        ,'a' AS item

  FROM   dual),

iii AS

(SELECT 'a' AS item FROM dual)

SELECT (SELECT unit_cost

        FROM   ((SELECT unit_cost

                       ,row_number() over(ORDER BY seq_no DESC) rn

                 FROM   tmp x

                 WHERE  x.item = t.item))

        WHERE  rn = 1)

FROM   iii t;

 

CASE 1 : 컴파일시 오류남..ㅠㅠ

 

,( SELECT MAX(h.unit_cost) KEEP(DENSE RANK FIRST ORDER BY h.seq DESC, h.creation_date DESC)  

          FROM xpos_po_unit_price_history h

         WHERE h.organization_id     = xpuph.organization_id

           AND h.inventory_item_id   = xpuph.inventory_item_id

           AND h.vendor_id           = xpuph.vendor_id

           AND h.vendor_site_id      = xpuph.vendor_site_id

           AND h.po_biz_channel      = xpuph.po_biz_channel

           AND h.price_type          = '1'

           AND h.enabled_flag        = 'Y'

           AND xpuph.effective_start_date - 15 BETWEEN h.effective_start_date

                                                   AND nvl(h.effective_end_date, xpuph.effective_start_date - 15)

           AND xpuph.price_type      = '1' 

  ) AS pre_period_unit_cost 

 

 

CASE 2 : 

     , (SELECT unit_cost

        FROM (SELECT h.unit_cost,row_number() OVER(ORDER BY h.seq DESC) rn

                FROM xpos_po_unit_price_history h

               WHERE h.organization_id     = xpuph.organization_id

                 AND h.inventory_item_id   = xpuph.inventory_item_id

                 AND h.vendor_id           = xpuph.vendor_id

                 AND h.vendor_site_id      = xpuph.vendor_site_id

                 AND h.po_biz_channel      = xpuph.po_biz_channel

                 AND h.price_type          = '1'

                 AND h.enabled_flag        = 'Y'

                 AND xpuph.effective_start_date - 15 BETWEEN h.effective_start_date

                                                       AND nvl(h.effective_end_date, xpuph.effective_start_date - 15)

                 AND xpuph.price_type      = '1' 

               )

         WHERE rn = 1

       ) AS pre_period_unit_cost