?
Solved

Optimize PL/SQL code

Posted on 2011-09-22
1
Medium Priority
?
326 Views
Last Modified: 2012-05-12
I would like to rewrite a PL/SQL code I believe there can be a better way to do it with less code.
There is a procedure Procedure(H_ID IN NUMBER,  p_DP IN CHAR(1), RES  OUT  VARCHAR2 ) which need to return a list of Items .
On the basis of the parameter p_DP it returns ITEMS with the value of  IS_DP  greater than or equal to 0.
If p_DP = 1 it returns  ITEMS with IS_DP >O
If p_DP=0  it returns  ITEMS  with IS_DP=0
The table ITEMS  joins another table HD through ITEM_CD to retrieve additional information.
All the columns  of ITEMS and HD need to be return except IS_VALID.
Here are the tables data:
ITEMS
ITEM_ID      ITEM_CD      IS_DP      IS_VALID
100      RT10      3      0
980      RT98      1      0
12O      RT12      4      0
202      RT20      7      1
140      RT14      0      0

HD
HD_ID      ITEM_CD      HD_DSC      IS_VALID
101      RT10      CT      0
102      RT98      DG      0
110      RT12      ET      0
106      RT14      LN      0

The out come should be:
IT.DUM_ID  IT.ITEM_CD  IT.IS_DP    HD.HD_ID   HD.ITEM_CD       HD
100      RT10      3      101      RT10       CT
980      RT98      1      102      RT98       DG
12O      RT12      4      110      RT12       ET

I have written a proposed solution below which seems to  have alot of code. Can there be a smarter way to do it.
Procedure(H_ID IN NUMBER,  p_DP CHAR(1), RES  OUT  VARCHAR2 )
IS
BEGIN
IF(IS_DP=1) THEN
OPEN res  FOR
WITH PASS AS (
SELECT
DUM_ID,
DUM_CODE,
IS_DP
FORM PASS
WHERE IS_VALID =0),
HD AS (
SELECT
HD_ID,
DUM_CODE,
HD_DSC
FORM HD
WHERE IS_VALID =0)
SELECT
--
--
WHERE
PS. IS_DP >0  
AND
JOINS
ELSIF (IS_DP=0) THEN
SAME QUERY BUT A CHANGE IN THE
WHERE CLAUSE
PS. IS_DP =0  
ELSE
SAME QUERY  AS ABOVE WITHOUT A CONDITION.
0
Comment
Question by:diteps06
1 Comment
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 36585087
open res for
SELECT it.dum_id,
       it.item_cd,
       it.is_dp,
       hd.hd_id,
       hd.item_cd,
       hd.hd_dsc
  FROM items it, hd
 WHERE id.item_cd = hd.item_cd
   AND it.is_valid = 0
   AND hd.is_valid = 0
   AND ((p_dp = 0 AND it.is_dp = 0) OR (p_dp = 1 AND it.is_dp > 0));
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month16 days, 13 hours left to enroll

864 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