Solved

Optimize PL/SQL code

Posted on 2011-09-22
1
319 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 250 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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 copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

749 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