CODEWITHSUNDEEP
sqloracle-database
Niinii
Niinii

Reputation: 83

Select column join with other table

SELECT   PB.BUKTI_ID,
           PB.BUKTI_DATE,
           CBD.DISBURSE_DATE,
           PB.CB_ID,
           PB.BUKTI_RECEIVED_BY,
           PB.NPWP,
           (SELECT   SUM (TOTAL_AMOUNT)
              FROM      TRX_PPH23_BUKTI PB
                     LEFT JOIN
                        TRX_PPH23_BUKTI_DETAIL PBD
                     ON PB.BUKTI_ID = PBD.REF_BUKTI_ID)
              AS TOTAL_AMOUNT,
           PB.BRANCH_ID,
           DECODE (PB.BUKTI_STATUS, 'O', 'Open', 'B', 'Cancel', 'Close')
              BUKTI_STATUS
    FROM      TRX_PPH23_BUKTI PB
           LEFT JOIN
              ( (SELECT   DISBURSE_NO,
                          DISBURSE_DATE,
                          RECEIVED_BY,
                          TOTAL_AMOUNT,
                          DISBURSE_STATUS
                   FROM   TRX_CASH_DISBURSE_HEADER)
               UNION
               (SELECT   DISBURSE_NO,
                         DISBURSE_DATE,
                         RECEIVED_BY,
                         TOTAL_AMOUNT,
                         DISBURSE_STATUS
                  FROM   TRX_BANK_DISBURSE_HEADER)) CBD
           ON PB.CB_ID = CBD.DISBURSE_NO
ORDER BY   PB.BUKTI_ID DESC;

I want to select field sum(total_amount) from joined from other table(TRX_PPH23_BUKTI_DETAIL) but i have got same values for this column,

enter image description here

i know the problem coz i didnt specificaly said the key(bukti_id).. But i dont know how to loop this query without store procedure+parameter

Upvotes: 1

Views: 92

Answers (3)

tsohtan
tsohtan

Reputation: 850

is this work for you?

SELECT * FROM (
    SELECT   PB.BUKTI_ID,
           PB.BUKTI_DATE,
           CBD.DISBURSE_DATE,
           PB.CB_ID,
           PB.BUKTI_RECEIVED_BY,
           PB.NPWP,
           (SELECT   SUM (TOTAL_AMOUNT)
              FROM      TRX_PPH23_BUKTI PB
                     LEFT JOIN
                        TRX_PPH23_BUKTI_DETAIL PBD
                     ON PB.BUKTI_ID = PBD.REF_BUKTI_ID)
              AS TOTAL_AMOUNT,
           PB.BRANCH_ID,
           DECODE (PB.BUKTI_STATUS, 'O', 'Open', 'B', 'Cancel', 'Close')
              BUKTI_STATUS
    FROM      TRX_PPH23_BUKTI PB
           LEFT JOIN
              ( (SELECT   DISBURSE_NO,
                          DISBURSE_DATE,
                          RECEIVED_BY,
                          TOTAL_AMOUNT,
                          DISBURSE_STATUS
                   FROM   TRX_CASH_DISBURSE_HEADER)
               UNION
               (SELECT   DISBURSE_NO,
                         DISBURSE_DATE,
                         RECEIVED_BY,
                         TOTAL_AMOUNT,
                         DISBURSE_STATUS
                  FROM   TRX_BANK_DISBURSE_HEADER)) CBD
           ON PB.CB_ID = CBD.DISBURSE_NO
) TRX INNER JOIN TRX_PPH23_BUKTI_DETAIL TRXb ON Trx.BUKTI_ID =TRXb.BUKTI_ID

Upvotes: 0

Felquir
Felquir

Reputation: 441

This might work but should include the query with the sum in the join,

    SELECT   PB.BUKTI_ID,
               PB.BUKTI_DATE,
               CBD.DISBURSE_DATE,
               PB.CB_ID,
               PB.BUKTI_RECEIVED_BY,
               PB.NPWP,
               (SELECT   SUM (TOTAL_AMOUNT)
                  FROM      TRX_PPH23_BUKTI PB2
                         LEFT JOIN
                            TRX_PPH23_BUKTI_DETAIL PBD
                         ON PB2.BUKTI_ID = PBD.REF_BUKTI_ID and PB.BUKTI_ID = PB2.BUKTI_ID)
                  AS TOTAL_AMOUNT,
               PB.BRANCH_ID,
               DECODE (PB.BUKTI_STATUS, 'O', 'Open', 'B', 'Cancel', 'Close')
                  BUKTI_STATUS
        FROM      TRX_PPH23_BUKTI PB
               LEFT JOIN
                  ( (SELECT   DISBURSE_NO,
                              DISBURSE_DATE,
                              RECEIVED_BY,
                              TOTAL_AMOUNT,
                              DISBURSE_STATUS
                       FROM   TRX_CASH_DISBURSE_HEADER)
                   UNION
                   (SELECT   DISBURSE_NO,
                             DISBURSE_DATE,
                             RECEIVED_BY,
                             TOTAL_AMOUNT,
                             DISBURSE_STATUS
                      FROM   TRX_BANK_DISBURSE_HEADER)) CBD
               ON PB.CB_ID = CBD.DISBURSE_NO
join ( (SELECT   SUM (TOTAL_AMOUNT)
                  FROM      TRX_PPH23_BUKTI PB2
                         LEFT JOIN
                            TRX_PPH23_BUKTI_DETAIL PBD
                         ON PB2.BUKTI_ID = PBD.REF_BUKTI_ID group by PB2.BUKTI_ID  )) as X_2 on X_2.BUKTI_ID = PB.BUKTI_ID
where 
    ORDER BY   PB.BUKTI_ID DESC;

thanks

Upvotes: 1

Philippe Banwarth
Philippe Banwarth

Reputation: 17755

You have to correlate the inner query with the outer table instead of declaring it again

  ...
  PB.BUKTI_RECEIVED_BY,
   PB.NPWP,
   (SELECT SUM (TOTAL_AMOUNT)
    FROM TRX_PPH23_BUKTI_DETAIL PBD
    WHERE PBD.REF_BUKTI_ID = PB.BUKTI_ID)
      AS TOTAL_AMOUNT,
   PB.BRANCH_ID,
  ...

Upvotes: 1

Related Questions