Home » SQL & PL/SQL » SQL & PL/SQL » Emp Code Searching Issue (Oracle DB 12C)
Emp Code Searching Issue [message #680084] Tue, 21 April 2020 09:08 Go to next message
wasimK
Messages: 2
Registered: April 2020
Location: Islamabad
Junior Member

Hi Everyone,

I have a table EMP which have many columns, I want to extract all of the rows but with some clauses, which i don't know how to do it. Please help me.
Query Should search the EMP_CODE and if it found the EMP_CODE with "gl", then it should display its email, if query didn't found the gl then it should search "ce" and display its email, but only one row. I have tried with DECODE Option but it was unsuccessful. Kindly help.
Below is the sample data



EMP_ID EMP_CODE EMP_EMAIL

A324 10gl gl@gmail.com
A324 101ce ce@gmail.com
A324 102ae ae@yahoo.com
A324 17qe qe@hotmail.com


Regards
Waseem Khan
Re: Emp Code Searching Issue [message #680086 is a reply to message #680084] Tue, 21 April 2020 09:31 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Emp Code Searching Issue [message #680089 is a reply to message #680084] Tue, 21 April 2020 10:05 Go to previous messageGo to next message
Bhushan.Mahajan
Messages: 4
Registered: April 2020
Location: Mumbai India
Junior Member


Hi,
You can try below code

SQL> SELECT EMAIL_ID
2 FROM
3 (
4 SELECT CASE WHEN EMPCODE LIKE '%GL' THEN EMAIL_ID
5 WHEN EMPCODE LIKE '%CE' THEN EMAIL_ID
6 WHEN EMPCODE LIKE '%AE' THEN EMAIL_ID
7 WHEN EMPCODE LIKE '%QE' THEN EMAIL_ID
8 END EMAIL_ID,
9 CASE WHEN EMPCODE LIKE '%GL' THEN 1
10 WHEN EMPCODE LIKE '%CE' THEN 2
11 WHEN EMPCODE LIKE '%AE' THEN 3
12 WHEN EMPCODE LIKE '%QE' THEN 4
13 END TEMP_ORDER
14 FROM EMP
15 ORDER BY TEMP_ORDER
16 ) A
17 WHERE ROWNUM < 2
18 ;

EMAIL_ID
------------------------------------------------------------------------------

GL@GMAIL.COM

SQL>
Re: Emp Code Searching Issue [message #680096 is a reply to message #680089] Tue, 21 April 2020 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And you try to format your code.
As said in your previous topic:

Michel Cadot wrote on Tue, 14 April 2020 18:24

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
...
In addition, this forum does not provide solution to homework but gives hints to help the student to find it by him/herself.

Now:
1/ Your query is over-complicated:
1a) What is the purpose of the first CASE? why not just EMAIL_ID?
2b) there is no need of your TEMP_ORDER
2/ How do you do it with several EMP_ID?

Re: Emp Code Searching Issue [message #680097 is a reply to message #680084] Tue, 21 April 2020 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

@wasimK,

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Emp Code Searching Issue [message #680099 is a reply to message #680096] Tue, 21 April 2020 11:37 Go to previous messageGo to next message
Bhushan.Mahajan
Messages: 4
Registered: April 2020
Location: Mumbai India
Junior Member
Razz Hi Michel,

Of course there is no need for first case, it should have been removed by me.
Also Why do you feel there is no need of Temp_order, In his statement he clearly mentioned that query should first look for EMP CODE with gl if that is not present then he should look for ce.
Also in his sample entries there are no multiple employees.
Re: Emp Code Searching Issue [message #680100 is a reply to message #680099] Tue, 21 April 2020 11:52 Go to previous message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Also Why do you feel there is no need of Temp_order, In his statement he clearly mentioned that query should first look for EMP CODE with gl if that is not present then he should look for ce.

Try to find a solution without it.
Hint: how do you use TEMP_ORDER? How could then remove it?

Quote:
Also in his sample entries there are no multiple employees.

But solution should be general as in real life there is more than one employee, don't you think?

Previous Topic: Extracting specific portion of a string using REGEX
Next Topic: Duplicate returns from list of Values
Goto Forum:
  


Current Time: Fri Sep 18 03:51:49 CDT 2020