Home » SQL & PL/SQL » SQL & PL/SQL » Query to Display date (11g )
Query to Display date [message #684149] Fri, 09 April 2021 06:31 Go to next message
glmjoy
Messages: 187
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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>
Either use explicit TO_DATE, or better use date literals:

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>
Sedcondly, your query is referencing column ATT_EMP_CODE while actual column name is EMP_CODE. Now about your issue. You must use (+) in every reference to outer joined table:

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>
Or, using ANSI join syntax:

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>
SY.
Re: Query to Display date [message #684151 is a reply to message #684150] Fri, 09 April 2021 07:14 Go to previous message
glmjoy
Messages: 187
Registered: September 2011
Location: KR
Senior Member
Thanks a lot
Previous Topic: Mark Only Selected Portion of a Column Data
Next Topic: ORA-00904: "SUPPID": invalid identifier
Goto Forum:
  


Current Time: Thu Mar 28 23:48:55 CDT 2024