| Need Help In Query [message #613982] |
Wed, 14 May 2014 02:34  |
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
|
Senior Member |
 
|
|
Dear Experts
I am stuck in one query and i need your expert help. To make criteria first of all create table in scott/tiger schema ,
create table r_date (
sno number,
t_date date);
-----
insert into r_date (sno,t_date) values (1,'17-may-74');
insert into r_date (sno,t_date) values (2,'01-jul-81');
insert into r_date (sno,t_date) values (3,'20-apr-87');
commit;
now check the result i need in query is like below
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO T_DATE
7369 SMITH CLERK 7902 17-Dec-80 800 0 20 17-May-74
7499 ALLEN SALESMAN 7698 20-Feb-81 1600 300 30 17-May-74
7521 WARD SALESMAN 7698 22-Feb-81 1250 500 30 17-May-74
7566 JONES MANAGER 7839 2-Apr-81 2975 0 20 17-May-74
7698 BLAKE MANAGER 7839 1-May-81 2850 0 30 17-May-74
7782 CLARK MANAGER 7839 9-Jun-81 2450 0 10 17-May-74
7844 TURNER SALESMAN 7698 8-Sep-81 1500 0 30 1-Jul-81
7654 MARTIN SALESMAN 7698 28-Sep-81 1250 1400 30 1-Jul-81
7839 KING PRESIDENT 0 17-Nov-81 5000 0 10 1-Jul-81
7900 JAMES CLERK 7698 3-Dec-81 950 0 30 1-Jul-81
7902 FORD ANALYST 7566 3-Dec-81 3000 0 20 1-Jul-81
7934 MILLER CLERK 7782 23-Jan-82 1300 0 10 1-Jul-81
7788 SCOTT ANALYST 7566 19-Apr-87 3000 0 20 1-Jul-81
7876 ADAMS CLERK 7788 23-May-87 1100 0 20 20-Apr-87
as you see that the last column in this result set is belong to the table r_date, where the t_date is shown till the next t_date is greater then hire_date, then it continue till the next t_date comes.
I have tried allot but cant have any clue how to make this query. Could you please help me.
regards
Anwer
|
|
|
|
|
|
|
|
|
|
| Re: Need Help In Query [message #613991 is a reply to message #613989] |
Wed, 14 May 2014 03:06  |
cookiemonster
Messages: 13919
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Thanks for test case by the way, but when posting insert statements with dates you should always wrap the date in a to_Date with appropriate format mask, just because it works for your date settings doesn't mean it'll work for anyone else:
SQL> create table r_date (
2 sno number,
3 t_date date);
Table created
SQL> -----
SQL> insert into r_date (sno,t_date) values (1,'17-may-74');
insert into r_date (sno,t_date) values (1,'17-may-74')
ORA-01858: a non-numeric character was found where a numeric was expected
SQL> insert into r_date (sno,t_date) values (2,'01-jul-81');
insert into r_date (sno,t_date) values (2,'01-jul-81')
ORA-01858: a non-numeric character was found where a numeric was expected
SQL> insert into r_date (sno,t_date) values (3,'20-apr-87');
insert into r_date (sno,t_date) values (3,'20-apr-87')
ORA-01858: a non-numeric character was found where a numeric was expected
SQL>
The correct way:
SQL> insert into r_date (sno,t_date) values (3,to_date('20-apr-1987', 'DD-MON-YYYY'));
1 row inserted
And always use 4 digit years. The y2k bug was a real bug and we should learn to avoid variations of it.
|
|
|
|
| Re: Need Help In Query [message #614002 is a reply to message #613991] |
Wed, 14 May 2014 05:41 |
 |
Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
And I'd say use month number instead of month name as we don't all speak the same language.
... or specify your language in the statement.
SQL> select to_date('20-apr-1987', 'DD-MON-YYYY') from dual;
select to_date('20-apr-1987', 'DD-MON-YYYY') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
SQL> select to_date('20-apr-1987', 'DD-MON-YYYY','nls_date_language=american') from dual;
TO_DATE('20-APR-198
-------------------
20/04/1987 00:00:00
1 row selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
