ORA-00936: missing expression. while using Outer Join with trunc(date)
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