ようこそ (Jyokoso) MY BLOG

いらっしゃいませ (hajimemashite) สำหรับผู้เข้าใหม่นะค่ะ ^^V

29 ตุลาคม 2553

sql union + left join

select 'ou' OU
,A.MACHINE_CODE||'-'||A.MACHINE_NAME MACHINE_NAME
,AC.FAC_NAME
,A.MACHINE_PLACE
,A.DIV_CODE
,A.MACHINE_BRAND
,A.MACHINE_MODEL
,A.MACHINE_SERILS_NO
,A.MACHINE_VENDOR
,A.MACHINE_VENDOR_ADDR
,A.MACHINE_TECH1
,A.MACHINE_TECH2
,A.MACHINE_TECH3
,A.MACHINE_TECH4
,A.MACHINE_TECH5
,A.MACHINE_TECH6
,A.MACHINE_INS_DATE
,AA.ASSET_AMT
,AA.BOOK_VALUE

,S.PROBLEM
,S.CAUSE
,S.EMP_CODE
,S.SUB_ATV_CODE
,S.ATV_SDATE
,S.ATV_EDATE
,S.ATV_STIME
,S.ATV_ETIME
,S.ITEM_CODE
,S.QTY
,S.AMT

FROM   MA_MACHINE A
                                                               
 LEFT OUTER JOIN FA_ASSET AA ON
A.ASSET_ID = AA. ASSET_ID
LEFT OUTER JOIN FA_PLACE AB ON
AA.PLACE_CODE = AB.PLACE_CODE
 LEFT OUTER JOIN FA_FACTORY AC ON
AB.FAC_CODE = AC. FAC_CODE

,
(select '1'                 type_report
,A.MACHINE_CODE  machine_code
,A.PROBLEM            problem
,C.CAUSE                cause
,C.EMP_CODE          emp_code
,D.SUB_ATV_CODE  SUB_ATV_CODE
,D.ATV_SDATE        ATV_SDATE
,D.ATV_EDATE        ATV_EDATE
,D.ATV_STIME        ATV_STIME
,D.ATV_ETIME        ATV_ETIME
,F.ITEM_CODE        ITEM_CODE
,F.TRAN_QTY         QTY
,F.TRAN_AMT         AMT
                                             
FROM    MA_REQUEST      A                                             
,           MA_REPAIR          C
,           MA_REPAIR_ATV  D
LEFT JOIN  IN_TRAN_HEAD E ON
D.OU_CODE = E.OU_CODE AND D.SPO_NO = E.DOC_NO AND E.DOC_TYPE = 'SPO01'
LEFT JOIN  IN_TRAN_DETAIL F ON
E.OU_CODE = F.OU_CODE AND E.DOC_NO = F.DOC_NO AND E.DOC_TYPE = F.DOC_TYPE
                                                      
WHERE   A.OU_CODE        = :OU_CODE
AND     A.OU_CODE          = C.OU_CODE 
AND     A.REQ_NO            = C.REQ_NO
AND     A.REQ_NO_SEQ    = C.REQ_NO_SEQ
                                                               
AND     C.OU_CODE          = D.OU_CODE
AND     C.REQ_NO            = D.REQ_NO
AND     C.REQ_NO_SEQ    = D.REQ_NO_SEQ
AND     C.REPAIR_SEQ     = D.REPAIR_SEQ

union

select   '2'                  type_report
,A.MACHINE_CODE      machine_code
,'-'                             problem
,'-'                             cause
,A.PM_EMP_CODE       emp_code
,D.SUB_ATV_CODE      SUB_ATV_CODE
,A.PM_ACTUal_DATE   ATV_SDATE
,D.ATV_EDATE            ATV_EDATE
,0                              ATV_STIME
,0                              ATV_ETIME
,E.ITEM_CODE            ITEM_CODE
,E.QTY                       QTY
,0                             AMT
                                             
FROM    MA_PM               A     
,           MA_PLAN_ATV     D
,           MA_PLAN_SP       E                                                   
WHERE A.plan_seq            = D.plan_seq
AND     A.atv_SEQ             = D.atv_SEQ

AND     A.plan_seq            = E.plan_seq
AND     A.atv_SEQ            = E.atv_SEQ
) S

WHERE  S.MACHINE_CODE = A.MACHINE_CODE
AND      S.type_report        like '%'
order by A.MACHINE_CODE,S.ATV_SDATE

1 ความคิดเห็น:

  1. ไม่ระบุชื่อ11 มกราคม 2555 17:50

    ความคิดเห็นนี้ถูกลบโดยผู้ดูแลระบบของบล็อก

    ตอบลบ