“在期望的位置找不到FROM关键字"； [英] "FROM keyword not found where expected"

问题描述

 SELECT distinct
   REPLACE(CM_NAME, '/', ' ') as CM_NAME,  
   TO_CHAR(Booking_Date,'MM/DD/YYYY') AS Booking_Date where Booking_Date = 
   '03/20/2018',
   sum(Air_Revenue) as TTL_AIRFARE,
   sum(Room_Revenue) as TTL_ROOM,
   sum(Car_Revenue) AS TTL_CAR,
   sum(Activity_Revenue) as TTL_ACTIVITY,
   0 as TTL_CRUISE

所以我想做的是选择昨天的日期，但是我一直收到此错误，用to_char语句执行此操作的最有效方法是

so what im trying to do is select yesterdays date but i keep getting this error, what is the most efficient way of doing this with the to_char statement

推荐答案

您发布的代码没有from子句，但是您说只是没有包含它.但是，它确实有一个where子句-放在错误的位置.它还正在将日期与字符串进行比较，这不是一个好主意，因为它依赖于隐式转换和会话NLS设置. (也许您认为它可以将固定字符串与您刚刚转换为字符串的日期进行比较-但这不是这种情况，至少在相同级别的查询中，无论如何在这种情况下都是效率低下的.)使用聚合函数时，您需要一个group-by子句...

The code you posted doesn't have a from clause, but you said you just hadn't included it. It does, however, have a where clause - which is in the wrong place. It is also comparing a date with a string, which isn't a good idea as it relies on implicit conversion and session NLS settings. (Perhaps you think it can compare the fixed string with the date you just converted to a string - but that isn't the case, at least in the same level of query, and would be inefficient anyway in this case.) And as you are using aggregate function you need a group-by clause...

您似乎想要:

SELECT REPLACE(CM_NAME, '/', ' ') as CM_NAME,  
   TO_CHAR(Booking_Date,'MM/DD/YYYY') AS Booking_Date,
   sum(Air_Revenue) as TTL_AIRFARE,
   sum(Room_Revenue) as TTL_ROOM,
   sum(Car_Revenue) AS TTL_CAR,
   sum(Activity_Revenue) as TTL_ACTIVITY,
   0 as TTL_CRUISE
FROM your_table
WHERE Booking_Date = DATE '2018-03-20'
GROUP BY REPLACE(CM_NAME, '/', ' '),
   Booking_Date

或者如果您想要昨天的日期而不必指定日期，则可以使用:

or if you want yesterday's date without having to specify it you can use:

WHERE Booking_Date = TRUNC(sysdate - 1)
GROUP BY REPLACE(CM_NAME, '/', ' '),
   Booking_Date

这将仅匹配Booking_Date恰好在午夜的行.如果确实包含其他时间，则可以执行以下操作:

That will only match rows where the Booking_Date is at exactly midnight. If it actually includes other times then you can do:

WHERE Booking_Date >= TRUNC(sysdate - 1)
AND Booking_Date < TRUNC(sysdate)
GROUP BY REPLACE(CM_NAME, '/', ' '),
   TO_CHAR(Booking_Date,'MM/DD/YYYY')

其中将包含一整天的数据.

which will include a single full day of data.

这篇关于“在期望的位置找不到FROM关键字"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！