sql - My sql statement does not like my partion over statement

Question

I have this simple query in DB2. I'm getting an error at the 2nd line from the top. For someone reason, it does not want to run my over(partition) line?

with core as(
select  *,row_number() over(partition by asgnd_to_pin order by stdt asc) as rank
from mhal_rep.stushh
where stus_cd in ('DWRT', 'FINL', 'DWFL', 'DWR', 'DWSR', 'DWPC')
AND STDT BETWEEN '2009-02-28' AND '2019-02-28'
UNION
select  *,row_number() over(partition by asgnd_to_pin order by stdt asc) as rank
from mhal_rep.stusha
where stus_cd in ('DWRT', 'FINL', 'DWFL', 'DWR', 'DWSR', 'DWPC')
AND STDT BETWEEN '2009-02-28' AND '2019-02-28'
 ),

 core1 as(
select asgnd_to_pin, stus_cd, stdt, rank, (('2019-02-28'-stdt)/365) as     
lngth_srvc
from core
where rank=1 and 
asgnd_to_pin in (
'788387',
'271562',
'155851')

select *
from core 1;

The error I'm getting says:

ERROR [42601] [IBM][DB2] SQL0104N An unexpected token "," was found    
following "". Expected tokens may include: "FROM INTO".

标签： sqldb2

Answer 1

利用

select  t.*, row_number() over(partition by asgnd_to_pin order by stdt asc) as rank
from mhal_rep.stushX t
...

代替

select  *, row_number() over(partition by asgnd_to_pin order by stdt asc) as rank
from mhal_rep.stushX
...

但实际上你还有更多问题：
- core1subselect 不是由-外部 select 语句)
之间core和中的空格1