ora-00933 sql command not properly ended..getting when i use scott.emp table query [message #21030] |
Mon, 08 July 2002 06:17 |
ksr
Messages: 112 Registered: January 2002
|
Senior Member |
|
|
Hi,
I am using the scott.emp table for the below query
I am getting the error
ora-00933 sql command not properly ended
I am not getting where is the error..can anyone give me the correct code..
select r.hiredate ,sum(r.empno) from (
SELECT count(r.empno) , to_char(r.hiredate, 'dd.mm.yyyy') as hiredate
FROM emp r
WHERE (r.hiredate >= to_date('20.02.1981','dd.mm.yyyy'))
AND (r.hiredate <= to_date('25.02.1981','dd.mm.yyyy'))
GROUP BY to_char(r.hiredate, 'dd.mm.yyyy') )
ORDER BY to_char(r.hiredate, 'dd.mm.yyyy')
union
select count(0) ,to_char(to_date('01.07.2002','dd.mm.yyyy'),'dd.mm.yyyy')+(rownum-1) dt_req
from all_objects
where rownum < to_date('07.07.2002','dd.mm.yyyy') - to_date('01.07.2002','dd.mm.yyyy')
)
group by HIREDATE
|
|
|
Re: ora-00933 sql command not properly ended..getting when i use scott.emp table query [message #21033 is a reply to message #21030] |
Mon, 08 July 2002 07:03 |
Saga
Messages: 51 Registered: April 2002
|
Member |
|
|
Here is your modified query
SELECT hiredate ,SUM(empno)
FROM (
SELECT COUNT(r.empno) empno,
TO_CHAR(r.hiredate, 'dd.mm.yyyy') AS hiredate
FROM EMP r
WHERE (r.hiredate >= TO_DATE('20.02.1981','dd.mm.yyyy'))
AND (r.hiredate <= TO_DATE('25.02.1981','dd.mm.yyyy'))
GROUP BY TO_CHAR(r.hiredate, 'dd.mm.yyyy') )
ORDER BY hiredate
UNION
SELECT COUNT(0) ,
TO_DATE('01.07.2002','dd.mm.yyyy') +(ROWNUM-1) dt_req1
FROM ALL_OBJECTS
WHERE ROWNUM < TO_DATE('07.07.2002','dd.mm.yyyy') - TO_DATE('01.07.2002','dd.mm.yyyy'))
GROUP BY HIREDATE
SELECT hiredate ,SUM(empno) FROM (
SELECT COUNT(r.empno) empno,
TO_CHAR(r.hiredate, 'dd.mm.yyyy') AS hiredate
FROM EMP r
WHERE (r.hiredate >= TO_DATE('20.02.1981','dd.mm.yyyy'))
AND (r.hiredate <= TO_DATE('25.02.1981','dd.mm.yyyy'))
GROUP BY TO_CHAR(r.hiredate, 'dd.mm.yyyy'))
GROUP BY hiredate
UNION
SELECT TO_CHAR((TO_DATE('01.07.2002','dd.mm.yyyy')+(ROWNUM-1)),'dd.mm.yyyy') hiredate,COUNT(0)
FROM ALL_OBJECTS
WHERE ROWNUM < TO_DATE('07.07.2002','dd.mm.yyyy') - TO_DATE('01.07.2002','dd.mm.yyyy')
GROUP BY ROWNUM
ORDER BY hiredate
|
|
|