0

i have problem here, when im trying to select with query below, i got error message

Error Code : 1248 Every derived table must have its own alias

SELECT B.BRANCH_CODE, B.BRANCH_NAME, C.COMPANY_CODE, C.NAME, TSK.DATE_CREATE,
  CASE TB.BULK_TYPE
    WHEN 1 THEN 'Bulk Transfer'
    WHEN 2 THEN 'Bulk Payment'
    ELSE 'Payroll'
  END AS TRX_METHOD_E, 
  CASE TB.BULK_TYPE
    WHEN 1 THEN 'Bulk Transfer'
    WHEN 2 THEN 'Bulk Pembayaran Tagihan'
    ELSE 'Pembayaran Gaji'
  END AS TRX_METHOD_I,
  TB.TOTAL_RECORD,
  TB.ACCOUNT_NO,
  TSK.TRX_TOTAL,
  TC.TOTAL_CHARGE,
  DATE(TSK.DATE_TRX) AS DATE_TRX,
  TB.REF_ID,
  CASE
    WHEN TSK.TRX_COUNT_SUCCESS = TSK.TRX_COUNT_ALL THEN 'All Success'
    WHEN TSK.TRX_COUNT_FAIL = TSK.TRX_COUNT_ALL THEN 'All Failed'
    WHEN TSK.TRX_COUNT_SUCCESS > 0 AND TSK.TRX_COUNT_FAIL > 0 THEN 'Partial Success (' || TSK.TRX_COUNT_SUCCESS || '/' || TSK.TRX_COUNT_ALL || ')'
    ELSE 'Pending Execution'
  END AS TRX_STATUS_E,
  CASE
    WHEN TSK.TRX_COUNT_SUCCESS = TSK.TRX_COUNT_ALL THEN 'Berhasil Semua'
    WHEN TSK.TRX_COUNT_FAIL = TSK.TRX_COUNT_ALL THEN 'Gagal Semua'
    WHEN TSK.TRX_COUNT_SUCCESS > 0 AND TSK.TRX_COUNT_FAIL > 0 THEN 'Berhasil Sebagian (' || TSK.TRX_COUNT_SUCCESS || '/' || TSK.TRX_COUNT_ALL || ')'
    ELSE 'Tunggu Eksekusi'
  END AS TRX_STATUS_I  
FROM CB_TASK_BULKS TB
  JOIN CB_TASKS TSK ON TSK.REF_ID = TB.REF_ID
  JOIN CB_COMPANIES C ON C.COMPANY_ID = TSK.COMPANY_ID
  JOIN CB_BRANCHES B ON B.BRANCH_CODE = C.BRANCH_CODE,
(
  SELECT REF_ID, SUM(CHARGE) AS TOTAL_CHARGE
  FROM
  (
    SELECT XTB.REF_ID, SUM(CHARGE) AS CHARGE
    FROM CB_TRANSFERS XT
    JOIN CB_TASK_BULK_DETAILS XTBD ON XTBD.BULK_DETAIL_ID = XT.BULK_DETAIL_ID
    JOIN CB_TASK_BULKS XTB ON XTB.REF_ID = XTBD.REF_ID
    GROUP BY XTB.REF_ID

    UNION

    SELECT XTB2.REF_ID, SUM(CHARGE) AS CHARGE
    FROM CB_TRANSFERS_DOM XTD
    JOIN CB_TASK_BULK_DETAILS XTBD2 ON XTBD2.BULK_DETAIL_ID = XTD.BULK_DETAIL_ID
    JOIN CB_TASK_BULKS XTB2 ON XTB2.REF_ID = XTBD.REF_ID
    GROUP BY XTB.REF_ID

    UNION

    SELECT XTB3.REF_ID, SUM(CHARGE) AS CHARGE
    FROM CB_PAYMENTS XP
    JOIN CB_TASK_BULK_DETAILS XTBD3 ON XTBD3.BULK_DETAIL_ID = XP.BULK_DETAIL_ID
    JOIN CB_TASK_BULKS XTB3 ON XTB3.REF_ID = XTBD.REF_ID
    GROUP BY XTB.REF_ID
  )
  GROUP BY REF_ID
) TC
WHERE TC.REF_ID = TSK.REF_ID
AND (TSK.TRX_COUNT_SUCCESS > 0 OR TSK.TRX_COUNT_FAIL > 0);

can somebody help me to find my problem here, i think i've given all tables with his own "name" like TB for CB_BULKS_DATA (example)

2 Answers 2

1

You need to mention an alias for all your inline views / derived tables in MySQL, even when they are nested. In your code you are using a derived table inside another derived table but you seem to have forgotten to mention an alias for your inner derived table. Put an alias before the 4th line from the bottom which is GROUP BY REF_ID. Put an alias before this group by clause and you will be fine.

0

i've solve my problems,

its just my fault to forgot the alias on union table

    (
  SELECT REF_ID, SUM(CHARGE) AS TOTAL_CHARGE
  FROM
  (
    SELECT XTB.REF_ID, SUM(CHARGE) AS CHARGE
    FROM CB_TRANSFERS XT
    JOIN CB_TASK_BULK_DETAILS XTBD ON XTBD.BULK_DETAIL_ID = XT.BULK_DETAIL_ID
    JOIN CB_TASK_BULKS XTB ON XTB.REF_ID = XTBD.REF_ID
    GROUP BY XTB.REF_ID

    UNION

    SELECT XTB2.REF_ID, SUM(CHARGE) AS CHARGE
    FROM CB_TRANSFERS_DOM XTD
    JOIN CB_TASK_BULK_DETAILS XTBD2 ON XTBD2.BULK_DETAIL_ID = XTD.BULK_DETAIL_ID
    JOIN CB_TASK_BULKS XTB2 ON XTB2.REF_ID = XTBD.REF_ID
    GROUP BY XTB.REF_ID

    UNION

    SELECT XTB3.REF_ID, SUM(CHARGE) AS CHARGE
    FROM CB_PAYMENTS XP
    JOIN CB_TASK_BULK_DETAILS XTBD3 ON XTBD3.BULK_DETAIL_ID = XP.BULK_DETAIL_ID
    JOIN CB_TASK_BULKS XTB3 ON XTB3.REF_ID = XTBD.REF_ID
    GROUP BY XTB.REF_ID
  ) TTTT
  GROUP BY REF_ID
) TC

see the Alias

TTTT above GROUP BY REF_ID

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.