OraFAQ Forum: SQL & PL/SQL » SQL CASE or IF Else Condition

Home » SQL & PL/SQL » SQL & PL/SQL » SQL CASE or IF Else Condition (DB 12C)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL CASE or IF Else Condition [message #681705]

Mon, 17 August 2020 12:20

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.

Report message to a moderator

Re: SQL CASE or IF Else Condition [message #681706 is a reply to message #681705]

Mon, 17 August 2020 12:50

Michel Cadot
Messages: 68418
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.

Report message to a moderator

Re: SQL CASE or IF Else Condition [message #681707 is a reply to message #681705]

Mon, 17 August 2020 13:38

Solomon Yakobson
Messages: 3212
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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Mar 24 11:24:38 CDT 2023