plsql decode的用法

SELECT ceil(rownum / :p_num) page_num
      ,pol.po_header_id
      ,to_number(pol.line_num) line_num
      ,msi.segment1 item_code
      ,pol.item_description
      ,msi.primary_uom_code uom
      ,pol.quantity po_quantity
      ,pol.attribute1 material_code
      ,pol.attribute2 spec
      ,nvl(pll.price_override
          ,pol.unit_price) unit_price
      ,to_char(pll.need_by_date
              ,'YYYY-MM-DD') need_by_date
      /*,pol.quantity * nvl(nvl(pll.price_override
                             ,pol.unit_price)
                         ,0) amount*/
      ,pol.quantity * nvl(nvl(pll.price_override
                             ,pol.unit_price)
                         ,0) *
       decode(pol.attribute5
             ,NULL
             ,1
             ,decode((1 - (substr(pol.attribute5
                           ,1
                           ,instr(pol.attribute5
                                 ,'%') - 1) / 100))
                    ,NULL
                    ,1
                    ,(1 - (substr(pol.attribute5
                           ,1
                           ,instr(pol.attribute5
                                 ,'%') - 1) / 100)))) amount
      ,pol.note_to_vendor
      ,pol.vendor_product_num
      ,pol.attribute3 color
      ,pol.attribute4 specifi
      ,pol.attribute5 line_discount
      ,pol.unit_meas_lookup_code line_uom
      ,to_char(pll.promised_date,'YYYY-MM-DD') del_date
      ,pol.attribute8 article
      ,pol.attribute9 grade
  FROM po_lines_all          pol
      ,po_line_locations_all pll
      ,mtl_system_items_b    msi
 WHERE pol.po_line_id = pll.po_line_id(+)
   AND pol.po_header_id = pll.po_header_id(+)
   AND pol.item_id = msi.inventory_item_id(+)
   AND nvl(pol.cancel_flag, 'N') = 'N'
   AND nvl(pol.closed_code, 'OPEN') != 'FINALLY CLOSED'
   AND msi.organization_id = nvl(:p_org_id
                                ,msi.organization_id);

 

 

 

其中：decode的用法很好

decode(pol.attribute5
             ,NULL
             ,1
             ,decode((1 - (substr(pol.attribute5
                           ,1
                           ,instr(pol.attribute5
                                 ,'%') - 1) / 100))
                    ,NULL
                    ,1
                    ,(1 - (substr(pol.attribute5
                           ,1
                           ,instr(pol.attribute5
                                 ,'%') - 1) / 100)))) amount