SQL compilation error: syntax error line 1 at position 212 unexpected '('-CodePudding

I am getting the SQL compilation error: syntax error line 1 at position 212 unexpected '('.

SQL query is :

select distinct 
    ACAP_ID,
    first_value(Account) over (partition by ACAP_ID order by Account desc) as Account,
    Bank,
    first_value(Type) over (partition by ACAP_ID order by Type desc) as Type 
from 
    (select distinct 
         a.RCL_APPLICATION_ID as ACAP_ID,
         zeroifnull(try_cast(a.RCL_ACCT_NUMBER as int)) as Account,
         case b.APP_BANK_NUMBER
             when 1 then 'Bank of Omaha'
             when 4 then 'Bank of Colorado'
             when 20 then 'Bank Illinois'
             when 30 then 'Bank Fremont'
             when 42 then 'Bank Texas'
             when 70 then 'Bank Platte Valley'
             when 76 then 'Bank Kansas'
             when 78 then 'Bank Columbus'
             when 79 then 'Bank North Platte'
             when 83 then 'Bank South Dakota'
         end as Bank,
         a.RCL_OPEN_END_LINE_OF_CRD as Type
     from 
         VW_ACP_QACAPI58 as a
     join 
         VW_ACP_MCDSMG3I as b on a.RCL_APPLICATION_ID = b.APPLICATION_ID 
     where 
         a.RCL_APPLICATION_ID in (20220901251080)
         and a.IS_CUR_FL = 'Y'
         and b.IS_CUR_FL = 'Y')

The failure comes up on the '(' by:

Type from (select

Please help, not sure what is going on here.

CodePudding user response：

Each statement should be terminated by ; and table alias is recommended.

SELECT ...
FROM (
     ....
     )  AS t;

To extend, it is possible to have a query without a table alias though providing one is a good practice:

CodePudding user response：

After ')' at the end, table alias is needed.

...'Y') AS A