?
Solved

Optimize PL/SQL code

Posted on 2011-09-22
1
Medium Priority
?
322 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 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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 …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

766 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