Emp Code Searching Issue [message #680084] |
Tue, 21 April 2020 09:08 |
|
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 #680089 is a reply to message #680084] |
Tue, 21 April 2020 10:05 |
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 #680100 is a reply to message #680099] |
Tue, 21 April 2020 11:52 |
|
Michel Cadot
Messages: 68694 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?
|
|
|