ORA-00937不是从SQL Server转换的单组组函数

Question

运行下面的ORACLE语句时，我得到了一个ORA-00937错误，我对Oracle非常陌生(T-SQL更适合我)。我已经转换了以前用SQL编写的select语句，我不明白为什么它需要group by ...这是转换后的select语句(我目前正在使用SQLPLUS运行/测试这些语句)：

select 
round(COALESCE(
(COALESCE((select SUM((F_BOOK_DATES.BKD_ADATE_ENDS - F_BOOK_DATES.BKD_ADATE_START) * (60 * 24)) FROM F_BOOK_DATES 
INNER JOIN F_BOOK_HEADER ON F_BOOK_DATES.BKD_FKEY_BK_SEQ = F_BOOK_HEADER.BK_SEQ 
and EXTRACT (YEAR from F_BOOK_DATES.BKD_DATE_START ) = EXTRACT (YEAR from SYSDATE )
and EXTRACT (MONTH from F_BOOK_DATES.BKD_DATE_START ) = EXTRACT (MONTH from SYSDATE )
and EXTRACT (DAY from F_BOOK_DATES.BKD_DATE_START ) = EXTRACT (DAY from SYSDATE )
AND F_BOOK_HEADER.BK_STATUS NOT IN ('CX','TP')
AND F_BOOK_HEADER.Deleted <> 1
AND F_BOOK_DATES.Deleted <> 1),9999999999.99,0)
/sum(case when FAREALO.AllDayBooking = 1 then 1400 else
((FAREALO.LO_TIME_END - FAREALO.LO_TIME_START) * (60 * 24)) end) * 100),0),2) as "PercentUtilised"
from FAREALO
inner join F_LO_TYPE on FAREALO.LO_FKEY_LOT_SEQ = F_LO_TYPE.LOT_SEQ
where FAREALO.LO_BK_LOCATION = 1
and LOT_CBS = 1;

这是我在上面尝试转换的SQL语句(可以工作)：

select 
round(isnull(
(isnull(convert (DECIMAL(10,2),(select SUM(datediff(n,BKD_ADATE_START, BKD_ADATE_ENDS)) FROM F_BOOK_DATES with (NOLOCK) 
INNER JOIN F_BOOK_HEADER with (NOLOCK) ON BKD_FKEY_BK_SEQ = BK_SEQ 
AND DATEPART(YYYY,BKD_DATE_START) = DATEPART(YYYY,GETDATE())
AND DATEPART(MM,BKD_DATE_START) = DATEPART(MM,GETDATE())
AND DATEPART(DD,BKD_DATE_START) = DATEPART(DD,GETDATE())
AND BK_STATUS NOT IN ('CX','TP')
AND F_BOOK_HEADER.Deleted <> 1
AND F_BOOK_DATES.Deleted <> 1)),0)
/
sum(case when AllDayBooking = 1 then 1400 else
datediff(n,LO_TIME_START, LO_TIME_END) end) * 100),0),2) as PercentUtilised
from FAREALO with (NOLOCK)
inner join F_LO_TYPE with (NOLOCK) on LO_FKEY_LOT_SEQ = LOT_SEQ
where LO_BK_LOCATION = 1
and LOT_CBS = 1

有人看到我错过了什么吗？

谢谢

J

Answer 1

下面是修改后的查询，在添加MIN函数后运行正常：

select 
round(COALESCE(
(COALESCE(MIN((select SUM((BKD_ADATE_ENDS - BKD_ADATE_START) * (60 * 24)) FROM F_BOOK_DATES 
INNER JOIN F_BOOK_HEADER ON BKD_FKEY_BK_SEQ = BK_SEQ 
and EXTRACT (YEAR from BKD_DATE_START ) = EXTRACT (YEAR from SYSDATE )
and EXTRACT (MONTH from BKD_DATE_START ) = EXTRACT (MONTH from SYSDATE )
and EXTRACT (DAY from BKD_DATE_START ) = EXTRACT (DAY from SYSDATE )
AND BK_STATUS NOT IN ('CX','TP')
AND F_BOOK_HEADER.Deleted <> 1
AND F_BOOK_DATES.Deleted <> 1)),9999999999.99,0)
/sum(case when AllDayBooking = 1 then 1400 else
((LO_TIME_END - LO_TIME_START) * (60 * 24)) end) * 100),0),2) as "PercentUtilised"
from FAREALO
inner join F_LO_TYPE on LO_FKEY_LOT_SEQ = LOT_SEQ
where LO_BK_LOCATION = 1
and LOT_CBS = 1;