"ORA-00923: FROM关键字未在预期中找到“

Question

我收到以下错误

执行查询时，"ORA-00923: FROM关键字未在预期位置找到“

SELECT 
    count(pf.pref_selection) filter ( WHERE pc.collection = "players")      OVER (partition BY pc.collection,pc.user_id )  AS Avg_players        
    count(pf.pref_selection) filter ( WHERE pc.collection = "teams")        OVER (partition BY pc.collection,pc.user_id )  AS Avg_teams  
FROM NBA_OWNER.PREFERENCES pf 
inner join NBA_OWNER.PREF_COLLECTIONS pc on pc.ID=pf.COLLECTION_ID 
where pc.LAST_UPDATE_DATE>"30-SEP-16'`

Answer 1

你有几个错误。正如@cricket_007指出的，在两个计数表达式之间缺少一个逗号。但是，filter子句的语法也是无效的--至少在Oracle中是不存在的；您在字符串文本中使用了双引号，而不是单引号；日期字符串周围的引号更奇怪；日期格式依赖于NLS设置。

这更接近你的需要，如果我理解你的尝试：

SELECT 
    count(case when pc.collection = 'players' then pf.pref_selection end)
      OVER (partition BY pc.user_id) AS Avg_players, 
    count(case when pc.collection = 'teams' then pf.pref_selection end)
      OVER (partition BY pc.user_id) AS Avg_teams  
FROM NBA_OWNER.PREFERENCES pf 
inner join NBA_OWNER.PREF_COLLECTIONS pc on pc.ID=pf.COLLECTION_ID 
where pc.LAST_UPDATE_DATE > date '2016-09-30'

不过，输出可能不是很有用。对于每个用户ID，您可能会得到多个行，每个行都显示总计数(由于某种原因您称之为avg )。您可能只想查看每个ID的计数一次，并查看它们与哪个ID相关，因此我认为您需要的是一个聚合计数，而不是分析计数：

SELECT 
    pc.user_id,
    count(case when pc.collection = 'players' then pf.pref_selection end)
      AS Avg_players,      
    count(case when pc.collection = 'teams' then pf.pref_selection end)
      AS Avg_teams  
FROM NBA_OWNER.PREFERENCES pf 
inner join NBA_OWNER.PREF_COLLECTIONS pc on pc.ID=pf.COLLECTION_ID 
where pc.LAST_UPDATE_DATE > date '2016-09-30'
group by pc.user_id