3703: SYNTAX ERROR, EXPECTED SOMETHING LIKE A NAME BETWEEN ')' AND THE

[natncm70]

Hello,
This is the query.

SELECT
IDD
, DT
, ATT
, DA
, DP
FROM
(
( SELECT
a.DOMICILE_CHANNEL_ID AS IDD
, COALESCE(SUM(
CASE
WHEN B.OPERATIONAL_PRODUCT_ID In ('CB', 'CC', 'CD', 'CE', 'CF', 'LP', 'LQ','LR','CP','CR','EC', 'ED', 'EK', 'EM', 'EO', 'EQ','ER' , 'ET' ,'DA','DD','DE','DF', 'DO', 'DQ', 'DR', 'DT', 'DU', 'DI', 'DJ', 'DK','EG', 'FL','FM','DG','DH','D1','D2','D3' ,'GA' )
THEN ENDING_LEDGER_BALANCE_AMT
END),0) AS DT
, COALESCE(SUM(
CASE
WHEN A.ACCOUNT_NBR IN (973400,888700,971120,971300,973800,970900,970912,970910, 971100,970301,970302,970303,970300,973000,900071,900072,971000)
THEN ENDING_LEDGER_BALANCE_AMT
END),0) AS ATT
, NULL AS DP
, NULL AS AP

FROM
dd_tedw.t0300_account a
Inner Join dd_tedw.b0200_product_map b
On a.product_id = b.mapped_id
Inner Join dd_tedw.t0303_account_status_history c
On a.account_nbr = c.account_nbr
Inner Join dd_tedw.t0305_account_summary_derive d
On a.account_nbr = d.account_nbr
INNER JOIN DD_TEDW.B0800_CHANNEL_MAP E
On E.mapped_id = a.domicile_channel_id
WHERE c.acct_status_type_code = 1
GROUP BY 1
)

UNION
(
SELECT

a.DOMICILE_CHANNEL_ID AS IDD
, NULL AS DT
, NULL AS ATT
, COALESCE(SUM(
CASE
WHEN B.OPERATIONAL_PRODUCT_ID In ('CB', 'CC', 'CD', 'CE', 'CF', 'LP', 'LQ','LR','CP','CR','EC', 'ED', 'EK', 'EM', 'EO', 'EQ','ER' , 'ET' ,'DA','DD','DE','DF', 'DO', 'DQ', 'DR', 'DT', 'DU', 'DI', 'DJ', 'DK','EG', 'FL','FM','DG','DH','D1','D2','D3' ,'GA' )
THEN ENDING_LEDGER_BALANCE_AMT
END ),0) AS DP
, COALESCE(SUM(
CASE
WHEN A.ACCOUNT_NBR IN (973400,888700,971120,971300,973800,970900,970912,970910, 971100,970301,970302,970303,970300,973000,900071,900072,971000)
THEN ENDING_LEDGER_BALANCE_AMT
END ),0) AS AP

FROM
dd_tedw.t0300_account a
Inner Join dd_tedw.b0200_product_map b
On a.product_id = b.mapped_id
Inner Join dd_tedw.t0303_account_status_history c
On a.account_nbr = c.account_nbr
Inner Join dd_tedw.t0305_account_summary_derive d
On a.account_nbr = d.account_nbr
INNER JOIN DD_TEDW.B0800_CHANNEL_MAP E
On E.mapped_id = a.domicile_channel_id
WHERE c.acct_status_type_code = 1
AND d.account_summary_date = '2002-10-30'
GROUP BY 1
)

) ORDER BY 1;

please help me

 

[BillDHS]

I don't think you need to use COALEASE if your case statemnt were SUM(CASE WHEN xxxx IN yyyy THEN zzz ELSE 0 END). The ELSE part would accomplish the same as COALEASE. This would clear some parenthesis and may help discover the error.

Also, can you run each select independently before doing the UNION join?

 

[dnoeth]

You're using a derived table without naming it, so just add a name:
SELECT
IDD
, DT
, ATT
, DA
, DP
FROM
(derived table
) AS dt ORDER BY 1;

Or just remove the outer select, it's unnecessary.

Dieter