Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-00936: missing expression. while using Outer Join with trunc(date)

Hassan R. KhanMay 23 2007 — edited May 23 2007
Dear All:

I want to write a query which should generate days from First Day to Last Day of the Given Month.
I retrieved the desired result from following query.
/*****************************************************************************************/
SELECT to_date('&P_Month','MON-YY')+rownum-1 dt
FROM dual
CONNECT BY LEVEL <= to_number(to_char(last_day(
to_date('&P_Month','MON-RR')),'DD'))
/*****************************************************************************************/
After that I use this query as an inline query and joined it with my database table to calculate the sum of Tues by matching the Date by inline query:
I wrote following query:

/*****************************************************************************************/
SELECT qes.line_id, Days.dt, sum(nvl(c_20,0) + nvl(c_40,0) * 2 + nvl(c_45,0) * 2) "Tues"
FROM empty_stock qes, (
SELECT to_date('&P_Month','MON-YY')+rownum-1 dt
FROM dual
CONNECT BY LEVEL <= to_number(to_char(last_day(
to_date('&P_Month','MON-RR')),'DD'))
) Days
WHERE Days.dt = trunc(qes.performed) (+)
AND qes.line_id=upper('&P_Line_Id')
GROUP BY qes.line_id, Days.dt
/*****************************************************************************************/
Database table has a Date column named as PERFORMED. When I exeduted the above query it gives me error ORA-00936: missing expression

When I remove the outer join from query like following condition
WHERE Days.dt = trunc(qes.performed)

It gave me the result but according to the parameters values the data is only for one Day, and query returened only one row with Date
LINE_ID DT Tues
====== == ====
BAY 21-May-07 1

While I want the result in following way (thats why I used outer join)
Rerturned all the dates and Tues value if not found then return 0 (Zero)

LINE_ID DT Tues
====== == ====
BAY 01-May-07 0
BAY 02-May-07 0
BAY 03-May-07 0
..
..
..
..
BAY 21-May-07 1
BAY 22-May-07 0
...
...
...
BAY 31-May-07 0

I used trunc because the time stamp also exist in PERFORMED filed.
I tried to use conditions as follows but Result is same with Outer Join

WHERE Days.dt = to_char(qes.performed,'DD-MON-RR')(+)

WHERE Days.dt = to_date(qes.performed,'DD-MON-RR')(+)

I also tried to use following condition and it also gave me one row of that date for which the data is available
WHERE Days.dt(+) = trunc(qes.performed)

Please note that the time is neccessary in PERFORMED for other reports

Thanks in advance for your help

Regards,

Hassan
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 20 2007
Added on May 23 2007
2 comments
996 views