Solved

Optimize PL/SQL code

Posted on 2011-09-22
1
321 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

691 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