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)
Return ALL rows in ProjectDetail only if ALL of it's rows have Status as New.
InProgress (Still working on Project)
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)
Return all Rows in ProjectDetail if ALL rows are have Status as either Processed or Rejected.