Home » SQL & PL/SQL » SQL & PL/SQL » SQL CASE or IF Else Condition (DB 12C)
icon8.gif  SQL CASE or IF Else Condition [message #681705] Mon, 17 August 2020 12:20 Go to next message
wasimK
Messages: 2
Registered: April 2020
Location: Islamabad
Junior Member

Hi Everyone,

I am stuck in query making and spend my whole day but didn't succeeded yet.
My Requirement is to extract the data of employees from EMP Table only. Each Employee have different type of Emails like (Yahoo, Gmail and Hotmail) and each mail type have different code like (Yahoo have 1, Gmail have 2 and Hotmail have 3) Some employees have all three and some have two of them and some have only one.
Now i want a query which extract the data WHEN an employee have 1(Yahoo) and 2(Gmail) mail then query should prioritize 1(Yahoo) and show only one row with code 1(Yahoo), But when Employee have all three mails(1,2,3) then query should show all the rows for that specific employee.
When employee have only two mails then it should show only one row which have code 1.

Please help, as i have tried with CASE and DECODE but it didn't work. Using UNION ALL Worked but that created a lag on system.
Re: SQL CASE or IF Else Condition [message #681706 is a reply to message #681705] Mon, 17 August 2020 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68055
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

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.

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

In the end, we expect you feedback in your topics.

Re: SQL CASE or IF Else Condition [message #681707 is a reply to message #681705] Mon, 17 August 2020 13:38 Go to previous message
Solomon Yakobson
Messages: 3106
Registered: January 2010
Location: Connecticut, USA
Senior Member
wasimK wrote on Mon, 17 August 2020 13:20

When employee have only two mails then it should show only one row which have code 1.
What if employee has two email addresses and none of them is Yahoo (code 1): Gmail and Hotmail. Also, is employee id, email type combination unique? And, as Michel already noted, please provide CREATE TABLE and INSERT data statements. In any case, all you need is analytic COUNT.

SY.
Previous Topic: regexp_substr fix string unable to handle.
Next Topic: Select and load CLOB column data into another table
Goto Forum:
  


Current Time: Thu Dec 09 04:37:56 CST 2021