Home » SQL & PL/SQL » SQL & PL/SQL » query to display employee whose code is 03 is not inserted (11g )
query to display employee whose code is 03 is not inserted [message #684828] Sat, 04 September 2021 02:38 Go to next message
glmjoy
Messages: 182
Registered: September 2011
Location: KR
Senior Member
I want to display employees whose code 03 is not inserted

CREATE TABLE TBAL
(
EMP_CODE VARCHAR2(6),
LV_DATE DATE ,
LV_CODE VARCHAR2(4) ,
LV_YEAR NUMBER(4))

insert into TBAL values('121027','01-JAN-2021','01',2021);
insert into TBAL values('121027','01-JAN-2021','02',2021);
insert into TBAL values('121027','01-JAN-2021','03',2021);

insert into TBAL values('121028','01-JAN-2021','01',2021);
insert into TBAL values('121028','01-JAN-2021','02',2021);

insert into TBAL values('121029','01-JAN-2021','01',2021);
insert into TBAL values('121029','01-JAN-2021','02',2021);

insert into TBAL values('121026','01-JAN-2021','01',2021);
insert into TBAL values('121026','01-JAN-2021','02',2021);
insert into TBAL values('121026','01-JAN-2021','03',2021);
Re: query to display employee whose code is 03 is not inserted [message #684829 is a reply to message #684828] Sat, 04 September 2021 02:50 Go to previous messageGo to next message
John Watson
Messages: 8644
Registered: January 2010
Location: Global Village
Senior Member
You have been asked, more than once, to follow the Forum rules: Format your posts correctly using [code] tags for code, and use type casting functions such as TO_DATE when working with dates and strings.

It us extremely rude of you to ignore these requests.
Re: query to display employee whose code is 03 is not inserted [message #684830 is a reply to message #684829] Sat, 04 September 2021 03:05 Go to previous messageGo to next message
glmjoy
Messages: 182
Registered: September 2011
Location: KR
Senior Member
sorry for that
Re: query to display employee whose code is 03 is not inserted [message #684831 is a reply to message #684830] Sat, 04 September 2021 08:17 Go to previous messageGo to next message
glmjoy
Messages: 182
Registered: September 2011
Location: KR
Senior Member
Thanks to all my problem is solved

select distinct t.emp_code from tbal t
where t.emp_code not in (select a.emp_code from tbal a where a.lv_code = 03)
/
Re: query to display employee whose code is 03 is not inserted [message #684832 is a reply to message #684828] Sat, 04 September 2021 09:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68052
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 28 July 2021 10:38

Please format your post as explained in How to use [code] tags and make your code easier to read, and align the column in result.

And test your test case before posting it:
...
SQL> CREATE TABLE TBAL
  2  (
  3  EMP_CODE VARCHAR2(6),
  4  LV_DATE DATE ,
  5  LV_CODE VARCHAR2(4) ,
  6  LV_YEAR NUMBER(4))
  7  /

Table created.

SQL> insert into TBAL values('121027','01-JAN-2021','01',2021);
insert into TBAL values('121027','01-JAN-2021','01',2021)
                                 *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
Re: query to display employee whose code is 03 is not inserted [message #684833 is a reply to message #684828] Sat, 04 September 2021 09:45 Go to previous messageGo to next message
EdStevens
Messages: 1351
Registered: September 2013
Senior Member
You are trying to insert a character string ('01-JAN-2021') into a column that is defined as a DATE datatype. This will force an implied TO_DATE operation to convert that string into the internal, binary structure of a DATE. This may or may not succeed, depending on the controlling setting of NLS_DATE_FORMAT. Better to leave nothing to chance and explcitly use TO_DATE:

insert into TBAL values('121028',TO_DATE('01-JAN-2021',DD-MON-YYYY'),'01',2021);
Re: query to display employee whose code is 03 is not inserted [message #684834 is a reply to message #684833] Sat, 04 September 2021 13:46 Go to previous message
glmjoy
Messages: 182
Registered: September 2011
Location: KR
Senior Member
Thanks for the correction
Previous Topic: Write an SQL query to report the students (student_id, student_name) being “quiet” in ALL exams. A “
Next Topic: Function Finding multiple occurrences of a string
Goto Forum:
  


Current Time: Tue Dec 07 10:22:36 CST 2021