Solved

Oracle PL/SQL: Return rows on condition clause

Posted on 2014-04-16
16
936 Views
Last Modified: 2014-04-21
Need PL/SQL for data retrieve data:

I have two Oracle two tables: ProjectMaster & ProjectDetail
ProjectMaster has three columns: ID(int), Projects & Status(varchars). The Status has values New, InProgress, Processed
ProjectDetail has three columns: ID, ProjectDetail & Status.  The Status has values New, Processed, Rejected.
The tables are linked by a common ID. A single row in ProjectMaster  is linked to many rows in ProjectDetail via common ID.
I work with each row of ProjectMaster  at a time.

I need to retrieve data from ProjectDetail based on Status values provided from ProjectMaster   like below, as input Parameter:

If Status provided as input parameter is:

New (It means project is untouched)
then
Return ALL rows in ProjectDetail only if ALL of it's rows have Status as New.  

InProgress (Still working on Project)
then
Return ALL rows in ProjectDetail  if at least one of it's row has Status as New.  Rest are either Processed or Rejected.

Processed (Done with processing)
then
Return all Rows in ProjectDetail  if  ALL rows are have Status as either Processed or Rejected.
0
Comment
Question by:vakils
  • 8
  • 7
16 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40005597
PL/SQL is Oracle's procedural language.  It is typically used to write stored procedures and functions.

That said, are you still wanting pl/sql or straight SQL?
How do you want the output?  cursor, XML, ???

Can you provide table definitions, sample data and expected results?

This way we can set up a complete test case that is proven to provide the expected results you provide.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40006104
As slightwv suggests, sample data and expected results would answer many questions.  As a guess, and using just SQL, a query like this might suffice. nb: I'm not exactly sure what you really want when a project's status is in progress.
select
      detail.*
      
from ProjectMaster project
inner join ProjectDetail detail on project.id = detail.projectid -- that "common id" not sure what the fieldname is

where project.id = 1
   and(
          ( project.status = 'New ' and detail.status = 'New ' )
       or 
          ( project.status = 'InProgress ' and detail.status = 'New' ) -- not sure abut this one
       or 
          ( project.status = 'Processed ' and detail.status in ('Processed ','Rejected') )
      )
;     
 

Open in new window

0
 

Author Comment

by:vakils
ID: 40007603
I want a procedure. The current implementation is:
--Spec
PROCEDURE ProjectDetailSelect
                                   (ID_IN        IN     NUMBER,
                                    STATUS_IN            IN     VARCHAR2,
                                    ProjectDetail_Cv  IN OUT NOCOPY REF_CURTYPE,
                                    APPRETCODE           OUT CHAR);
--Body
PROCEDURE ProjectDetailSelect
                                   ID_IN        IN     NUMBER,
                                    STATUS_IN            IN     VARCHAR2,
                                    ProjectDetail_Cv  IN OUT NOCOPY REF_CURTYPE,
                                    APPRETCODE           OUT CHAR)
IS
BEGIN
--
    APPRETCODE := 'S';
    OPEN ProjectDetail_Cv  
     FOR SELECT /*+ RULE */ *
           FROM ProjectDetail
          WHERE ID         = ID_IN
           AND STATUS    = STATUS_IN;
END;

Open in new window

Now the rules for new implementation: based on STATUS_IN
STATUS_IN = 'New' then return ALL rows if Status of ALL rows is 'New'
STATUS_IN = 'InProgress' then return ALL rows if Status of at least one row is 'Processed' or 'Rejected' (That means we have not processed all rows, some rows are in 'New' status)
STATUS_IN = 'Processed' then return ALL rows if Status of ALL rows is 'Processed' or 'Rejected'

I will provide table definition and sample data, expected results for ProjectDetail since we are operating on this table only.
0
 

Author Comment

by:vakils
ID: 40008074
ProjectDetail Table
Logic:
ID          Rows to return
 1           Return ALL rows of STATUS_IN = 'New'   No rows for any other STATUS_IN
 2           Return ALL rows of STATUS_IN = 'InProgress'   No rows for any other STATUS_IN
 3           Return ALL rows of STATUS_IN = 'Processed'   No rows for any other STATUS_IN


CREATE TABLE projectdetail
    (id                             NUMBER(9,0) NOT NULL,
    status                         VARCHAR2(30 BYTE),
    description                    VARCHAR2(50 BYTE))
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40008115
I'll wait for the sample data and expected results but until then, here is my own test case.

It doesn't return a cursor but I think the select inside the loop is the main select for your cursor.  I just added a loop for the dbms_output for simplification.

If there are problems with it, please add to the test case and explain why you added what you did.

drop table tab1 purge;
create table tab1(id number, status varchar2(20));

--all new
insert into tab1 values(1, 'New');
insert into tab1 values(1, 'New');
--one processed
insert into tab1 values(2, 'Processed');
insert into tab1 values(2, 'Junk');
-- ALL processed or rejected
insert into tab1 values(3, 'Processed');
insert into tab1 values(3, 'Rejected');
--Never return anthing
insert into tab1 values(4, 'Fred');
insert into tab1 values(4, 'Barney');
--new w/o ALL new
insert into tab1 values(5, 'New');
insert into tab1 values(5, 'Pete');
commit;


create or replace procedure myproc(
	ID_IN		IN	NUMBER,
	STATUS_IN	IN	VARCHAR2
) is
begin

	for i in (
		select * from (
			select id, status,
				count(*) over(partition by id) status_count,
				sum(case when status='New' then 1 end) over(partition by id) new_count,
				sum(case when status in ('Processed','Rejected') then 1 end) over(partition by id) inprogress_count
			from tab1 where id = id_in
		)
		where 
		(
			(status_in='New' and new_count=status_count) or
			(status_in='InProgress' and inprogress_count > 0) or
			(status_in='Processed' and status_count = inprogress_count)
		)
	) loop
		dbms_output.put_line(i.id || ' ' || i.status);
	end loop;
end;
/

show errors

exec myproc(1, 'New');
exec myproc(1, 'InProgress');
exec myproc(1, 'Processed');
exec myproc(1, 'Bob');

exec myproc(2, 'New');
exec myproc(2, 'InProgress');
exec myproc(2, 'Processed');
exec myproc(2, 'Bob');

exec myproc(3, 'New');
exec myproc(3, 'InProgress');
exec myproc(3, 'Processed');
exec myproc(3, 'Bob');

exec myproc(4, 'New');
exec myproc(4, 'InProgress');
exec myproc(4, 'Processed');
exec myproc(4, 'Bob');

exec myproc(5, 'New');
exec myproc(5, 'InProgress');
exec myproc(5, 'Processed');
exec myproc(5, 'Bob');

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40008116
Sorry, I was posting and didn't see your recent post.

See if my test case will work for you.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40008576
If you already looked at the code I posted above, I changed it this morning.

I moved the id check to the inner query.  If you have an index on the id column it should run a lot faster.
0
 

Author Comment

by:vakils
ID: 40009882
Sorry for delay. Got caught up in a different issue. Ran the proc with the table above:
For ID = 3 it should return rows only for Status = Processed, but returns rows also for Status = InProgress. See below:
Proc ResultsI just changed tablename in proc as projectdetail.
I already gave above sql to create table.
Below is sql to populate for test:

INSERT INTO projectdetail
VALUES(1,'New','Test 1');
INSERT INTO projectdetail
VALUES(1,'New','Test 2');
INSERT INTO projectdetail
VALUES(2,'New','Test 1');
INSERT INTO projectdetail
VALUES(2,'Processed','Test 2');
INSERT INTO projectdetail
VALUES(2,'Rejected','Test 3');
INSERT INTO projectdetail
VALUES(3,'Processed','Test 1');
INSERT INTO projectdetail
VALUES(3,'Processed','Test 2');
INSERT INTO projectdetail
VALUES(3,'Rejected','Test 3');
INSERT INTO projectdetail
VALUES(1,'New','Test 3');
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40009925
>>For ID = 3 it should return rows only for Status = Processed,

Above you defined them as follows:
STATUS_IN = 'InProgress' then return ALL rows if Status of at least one row is 'Processed' or 'Rejected'
STATUS_IN = 'Processed' then return ALL rows if Status of ALL rows is 'Processed' or 'Rejected'

For: exec myproc(3, 'InProgress');

At lease one of id 3 is 'Processed' or 'Rejected' so it should come back.


For: exec myproc(3, 'Processed');

ALL rows is 'Processed' or 'Rejected'


I guess I'm not following the requirements.  Can you clarify where I'm missing them?

I suggest you try to understand my code and the logic I'm using.  You should be able to adjust it to the actual requirements.

Basically, I'm counting the different 'groups' with:
      count(*) over(partition by id) status_count,
      sum(case when status='New' then 1 end) over(partition by id) new_count,
      sum(case when status in ('Processed','Rejected') then 1 end) over(partition by id) inprogress_count

Then it's straight forward number checking in the outer query to see if the 'group' fits.
0
 

Author Comment

by:vakils
ID: 40009967
Sorry I could not clarify requirements. If you think logically, that's the way statuses are named. So New means this project (ID) is untouched no work has been reported as done,
InProgress means some progress is reported. So we have mixed bag of  New and Processed and/or Rejected statuses. There is at least one 'New' status.
So,
STATUS_IN = 'New' then ALL rows are 'New'
STATUS_IN = 'InProgress'  At least one row has Status 'New'. And at least one row is either Rejected or Processed.
STATUS_IN = 'Processed' No rows are 'New'
So for ID 1 'New' holds TRUE
ID 2  'InProgress' holds TRUE
ID 3 'Processed' holds TRUE
Each ID returns holds  one ONE unique Status
so exec myproc(3, 'Processed') is TRUE ( no 'New rows)
myproc(3 'InProgress) is FALSE, does not return any row as there is no 'New' row.
exec myproc(2, 'InProgress') is TRUE(has at least one 'New' row)
exec myproc(1, 'New') is TRUE as ALL rows are 'New'

I don't have much expertise in SQL, so I am trying to understand. But you are close.
0
 

Author Comment

by:vakils
ID: 40009970
Psuedocode:

For an ID
if All rows are 'New' then return ALL rows for STATUS_IN = 'NEW'
else
at least one row is 'New' and rest are either Rejected or Processed then return ALL rows for STATUS_IN = 'InProgress'
else
No rows are New then return ALL rows for STATUS_IN = 'Processed'
0
 

Author Comment

by:vakils
ID: 40010001
An honest attempt to explain Status

SELECT count(*) TotalRows
  FROM projectdetail a
  where a.id = ID_IN;
SELECT count(*) NewRows
  FROM projectdetail a
  where a.status = 'New'
  and    a.id = ID_IN;
  SELECT count(*) SomeRows
  FROM projectdetail a
  where a.status != 'New'
  and    a.id = ID_IN;

If TotalRows = NewRows then return ALL Rows for STATUS_IN = 'NEW'
else
if TotalRows  > NewRows  and NewRows >0  THEN return ALL Rows for STATUS_IN = 'InProgress'
else
if TotalRows = SomeRows then return ALL rows for STATUS_IN = 'Processed'
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40010830
Instead of trying to explain it show me your expected results from the data you provided me above.

Since you are new to sql, let me try to explain what the inner select is doing then hopefully you can tweak my code.

Maybe it would be better to just grab counts for ALL status'.  Then you can make the outer query do whatever you want.

Note: I changed status_count to total_status_count to make it more meaningful.

...
count(*) over(partition by id) total_status_count,
      sum(case when status='New' then 1 end) over(partition by id) new_count,
      sum(case when status='Processed' then 1 end) over(partition by id) processed_count,
      sum(case when status='Rejected' then 1 end) over(partition by id) rejected_count
...


Then in the outer query, just compare the counts:
Since 'New' is ALL or nothing then compare the TOTAL_STATUS_COUNT with the NEW_COUNT.  If they match, then it is 'New':
(status_in='New' and new_count=total_status_count) or

Then just add OR conditions for the rest.

This is from my query above which we now know is wrong but I still don't understand what is correct
                  (status_in='InProgress' and inprogress_count > 0) or
                  (status_in='Processed' and total_status_count = inprogress_count)

However you define 'InProgress' just add AND's with the correct count comparisons.

The parenthesis for each check is important so the OR's make sense.
0
 

Author Comment

by:vakils
ID: 40013490
I followed your above suggestion. The where clause in code should give mutually exclusive result meaning only one should hold true for given ID and Status_IN. Since we grab counts : total_status_count, new_count & inprogress_count, it helps a lot in customizing where clause for whatever logic we want in output.  I am using your initial query which treats InProgress and Rejected statuses as InProgress which is correct interpretation and tweaked only where clause as per my interpretation to get code below:
create or replace procedure myproc(
	ID_IN		IN	NUMBER,
	STATUS_IN	IN	VARCHAR2
) is
begin

	for i in (
		select * from (
			select id, status,
				count(*) over(partition by id) total_status_count,
				sum(case when status='New' then 1 end) over(partition by id) new_count,
				sum(case when status in ('Processed','Rejected') then 1 end) over(partition by id) inprogress_count
			from tab1 where id = id_in
		)
		where 
		(
                       (status_in='New' and new_count=total_status_count) or
                       (status_in='InProgress' and new_count >0 AND inprogress_count >0 ) or
                       (status_in='Processed' and inprogress_count = total_status_count)
		)
	) loop
		dbms_output.put_line(i.id || ' ' || i.status);
	end loop;
end;

Open in new window

.
All three in where clause become mutually exclusive which is my requirement.
Thanks!
0
 

Author Closing Comment

by:vakils
ID: 40013503
Thanks! Nice work, the code is elegant in a way that only where clause needs be changed to get desired output.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40013544
Glad to help.

>>All three in where clause become mutually exclusive which is my requirement.

That was the intent.  Sorry if I didn't stress that before.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now