SQL0104N Error

[vkalvakota]

Hi,

I am getting the following error when i am trying to create a view. But if i run the select statement independently it is working fine. Please help me it is very urgent.

15:06:08.043 DBMS FFIDWD -- Error: [IBM][CLI Driver][DB2/SUN] SQL0104N An unexpected token "DW" was found following "SAN' FOR CREATE VIEW". Expected tokens may include: "JOIN". SQLSTATE=42601

Please find the SQL for creating the view. Please tell me what the error is....


CREATE VIEW DW.GC_CLIENT_HQ_SUMMARY AS
SELECT * FROM
(SELECT
CURRENT_MONTH_VALUE, PREVIOUS_MONTH_VALUE, PREVIOUS_YEAR,CURRENT_YEAR,Team_Name AS "Global Cash Sales Team",
Salesperson_Name,col_order,
SUM("Spot Assets") AS "Spot Assets",
SUM("Current Spot Assets") AS "Current Spot Assets",
SUM("Current Spot Assets") - SUM("Spot Assets") AS "Spot Assets YTD Change",
SUM("Prior Year Avg Assets") AS "Prior Year Avg Assets",
SUM("YTD Avg Assets") AS "YTD Avg Assets",
SUM("YTD Avg Assets") - SUM("Prior Year Avg Assets") AS "YTD Average Change"
FROM
(SELECT
CURRENT_MONTH_VALUE, PREVIOUS_MONTH_VALUE, PREVIOUS_YEAR,CURRENT_YEAR,Team_Name , Salesperson_Name,col_order,
SUM(FACT_PREV_YEAR.closing_balance_USD) AS "Spot Assets",
cast(0.0 as decimal(19,4)) AS "Current Spot Assets",
cast(0.0 as decimal(19,4)) AS "Spot Assets YTD Change",
SUM(FACT_PREV_YEAR.average_balance_USD) AS "Prior Year Avg Assets",
cast(0.0 as decimal(19,4)) AS "YTD Avg Assets",
cast(0.0 as decimal(19,4)) AS "YTD Average Change"
FROM
CONTROL.DATA_WAREHOUSE_CURRENT_DATE CONTROL_DATE,
DW.DIMENSION_MUTUAL_FUND DIM_MF,
(
SELECT
(Case
when y.avenue_client_id = 'M305610' then y.Name
when y.avenue_client_id != 'M305610' and coalesce(y.sub_channel,'') = 'State Pools' then y.sub_channel
when s.team_name = 'US Corporate and Public Sector' or s.team_name = 'US Financial Institutions'
then 'US Sales'
when s.team_name = 'Europe Corporate and Financial Institutions' then 'Europe Sales'
when s.team_name = 'Asia Corporate and Financial Institutions' then 'Asia Sales'
else
s.team_name
end) as Team_Name ,Salesperson_Name, Fund_account_DK,
(case
when y.avenue_client_id = 'M305610' or y.avenue_client_id != 'M305610'
and coalesce(y.sub_channel,'') = 'State Pools' then 2
else
1
end) as Col_order
FROM dw.dimension_salesperson s,
dw.dimension_mutual_fund_account x,
dw.dimension_avenue_client y
WHERE salesperson_id = x.HQ_salesperson_ID
AND x.avenue_client_id = y.avenue_client_id
AND Team_Name IS NOT NULL AND s.END_DATE IS NULL
AND FUND_ACCOUNT_DK NOT in (SELECT FUND_ACCOUNT_DK FROM DW.FILTER_REPORT_VW FR
WHERE FR.FILTER_CODE ='GBLCASHVW'
AND FUND_ACCOUNT_DK IS NOT NULL
)
) AS ACCT_SALESPEOPLE INNER JOIN DW.FACT_YEARLY_MF_AUM FACT_PREV_YEAR on
FACT_PREV_YEAR.fund_account_dk = ACCT_SALESPEOPLE.Fund_account_DK
WHERE DIM_MF.MUTUAL_FUND_DK NOT IN (SELECT MUTUAL_FUND_DK FROM DW.FILTER_REPORT_VW FR
WHERE FR.FILTER_CODE ='GBLCASHVW'
AND MUTUAL_FUND_DK IS NOT NULL
)
AND FACT_PREV_YEAR.period_dk = CONTROL_DATE.PREVIOUS_YEAR_DK
AND FACT_PREV_YEAR.MUTUAL_FUND_DK = DIM_MF.MUTUAL_FUND_DK
GROUP BY CURRENT_MONTH_VALUE, PREVIOUS_MONTH_VALUE, PREVIOUS_YEAR,CURRENT_YEAR,Team_Name , Salesperson_Name,col_order ) AS QUERY
GROUP BY CURRENT_MONTH_VALUE, PREVIOUS_MONTH_VALUE, PREVIOUS_YEAR,CURRENT_YEAR,Team_Name , Salesperson_Name,col_order
order by col_order) as query1;


Thanks in advance.

Venkatesh

[vkalvakota]

Can anybody tell me the error even i am getting the same kind of error...