| Query to Display date [message #684149] |
Fri, 09 April 2021 06:31  |
 |
glmjoy
Messages: 184
Registered: September 2011
Location: KR
|
Senior Member |
|
|
I want to display date of DT Table joining Attendance table but it says no row selected
select Dt.MDATE from DT DT,
ATTENDANCE t
Where
Dt.MDATE ='08-APR-2021'
and Dt.MDATE =T.Att_Date(+)
and t.att_emp_code = '000001'
no rows selected
CREATE TABLE DT
(
MDATE DATE
)
insert into Dt (MDATE) VALUES('06-APR-2021');
insert into Dt (MDATE) VALUES('07-APR-2021');
insert into Dt (MDATE) VALUES('08-APR-2021');
insert into Dt (MDATE) VALUES('09-APR-2021');
CREATE TABLE ATTENDANCE
(
EMP_CODE VARCHAR2(6) ,
ATT_DATE DATE )
insert into ATTENDANCE (EMP_CODE,ATT_DATE) VALUES('000001','06-APR-2021');
insert into ATTENDANCE (EMP_CODE,ATT_DATE) VALUES('000001','07-APR-2021');
insert into ATTENDANCE (EMP_CODE,ATT_DATE) VALUES('000001','09-APR-2021');
|
|
|
|
| Re: Query to Display date [message #684150 is a reply to message #684149] |
Fri, 09 April 2021 06:55  |
Solomon Yakobson
Messages: 3213
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
First of all '08-APR-2021' is a string and not a date so your code will fail for many non-english clients. For example:
SQL> alter session set nls_date_language=french;
Session altered.
SQL> insert into Dt (MDATE) VALUES('06-APR-2021');
insert into Dt (MDATE) VALUES('06-APR-2021')
*
ERROR at line 1:
ORA-01843: not a valid month
SQL> alter session set nls_date_language=american;
Session altered.
SQL> insert into Dt (MDATE) VALUES('06-APR-2021');
1 row created.
SQL>
SQL> alter session set nls_date_language=french;
Session altered.
SQL> insert into Dt (MDATE) VALUES(TO_DATE('06-APR-2021','DD-MON-YYYY','nls_date_language=american'));
1 row created.
SQL> insert into Dt (MDATE) VALUES(DATE '2020-04-06');
1 row created.
SQL>
SQL> select Dt.MDATE from DT DT,
2 ATTENDANCE t
3 Where
4 Dt.MDATE = DATE '2021-04-08'
5 and Dt.MDATE =T.Att_Date(+)
6 and t.emp_code = '000001'
7 /
no rows selected
SQL> select Dt.MDATE from DT DT,
2 ATTENDANCE t
3 Where
4 Dt.MDATE = DATE '2021-04-08'
5 and Dt.MDATE =T.Att_Date(+)
6 and t.emp_code(+) = '000001'
7 /
MDATE
---------
08-APR-21
SQL>
SQL> SELECT DT.MDATE
2 FROM DT DT
3 LEFT JOIN ATTENDANCE T
4 ON DT.MDATE = T.ATT_DATE
5 AND
6 T.EMP_CODE = '000001'
7 WHERE DT.MDATE = DATE '2021-04-08'
8 /
MDATE
---------
08-APR-21
SQL>
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
