Home » SQL & PL/SQL » SQL & PL/SQL » problem in sql query (oracle 11g)
problem in sql query [message #575076] Sat, 19 January 2013 00:37 Go to next message
deepikachaudhary31
Messages: 8
Registered: October 2012
Junior Member
I have a table employee

clientId empid empname active_worked_yn
1 1 a Y
1 2 b N
2 1 c N

I want output
one record from clientId 1 if active_worked_yn = 'Y'
1 1 a Y
2 1 c N
otherwise
1 2 b N
2 1 c N
Re: problem in sql query [message #575078 is a reply to message #575076] Sat, 19 January 2013 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

Michel Cadot wrote on Thu, 18 October 2012 08:05
Welcome to the forum.

With any SQL question, Post a working Test case: create table 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.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...


As you refuse to follow the guide and to feedback to people that help you to know if you finally find the solution and which one, I don't any reason to continue to try to help you.

Regards
Michel

Re: problem in sql query [message #575085 is a reply to message #575076] Sat, 19 January 2013 01:30 Go to previous messageGo to next message
deepikachaudhary31
Messages: 8
Registered: October 2012
Junior Member
I want this type of output from this table.

Say for example.

I have table employee with these 4 fields.

1. CLientID
2. EMPID
3. EMPNAME
4. WORKED_ACTIVE

These table have suppose 4 records.
CLIENTID EMPID EMPNAME WORKED_ACTIVE
1 1 RAM Y
1 2 SHYAM N
2 1 RAM N
3 1 RAM N

Output i want.

I want to check if there multiple records for same clientId then check for that clientId worked_active = 'Y'

Will appreciate help!
Re: problem in sql query [message #575086 is a reply to message #575085] Sat, 19 January 2013 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sat, 19 January 2013 07:50
From your previous topic:

Michel Cadot wrote on Thu, 18 October 2012 08:05
Welcome to the forum.

With any SQL question, Post a working Test case: create table 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.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

...


As you refuse to follow the guide and to feedback to people that help you to know if you finally find the solution and which one, I don't any reason to continue to try to help you.

Regards
Michel


[Updated on: Sat, 19 January 2013 01:34]

Report message to a moderator

Re: problem in sql query [message #575087 is a reply to message #575085] Sat, 19 January 2013 01:50 Go to previous messageGo to next message
flyboy
Messages: 1750
Registered: November 2006
Senior Member
deepikachaudhary31 wrote on Sat, 19 January 2013 08:30
I want this type of output from this table.

I do not see any output in your post. It would be useless without of exact rules leading to it (which are missing too) anyway.
deepikachaudhary31 wrote on Sat, 19 January 2013 08:30
I want to check if there multiple records for same clientId

Aggregation by CLIENTID is capable to get this information. Alternatively, analytic COUNT function can tell it too.
deepikachaudhary31 wrote on Sat, 19 January 2013 08:30
then check for that clientId worked_active = 'Y'

Place it to appropriate WHERE condition then.
Re: problem in sql query [message #575106 is a reply to message #575076] Sat, 19 January 2013 09:03 Go to previous message
shumail
Messages: 59
Registered: September 2012
Location: Canada
Member
Check it out.
with data as
(
select empid,clientid, row_number() over (partition by clientid order by clientid desc) cnt,
COUNT(*) OVER (partition by clientid) ctt
from employee)
select data.*,(case when ctt > 1 and cnt=1 then 'Y' else 'N'  end) active_worked_yn
from data
;

Previous Topic: logic for procedure
Next Topic: QUERYING SESSION PARAMETERS THAT I CHANGED [merged 4 by jd]
Goto Forum:
  


Current Time: Thu Apr 17 20:41:01 CDT 2014

Total time taken to generate the page: 0.10803 seconds