Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Optimize PL/SQL code

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

618 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