Home » SQL & PL/SQL » SQL & PL/SQL » Need suggestion on code refactoring (oracle 11g )
Need suggestion on code refactoring [message #674755] |
Thu, 14 February 2019 08:45 |
|
harishankar_kar
Messages: 22 Registered: July 2014 Location: India
|
Junior Member |
|
|
Hi,
I am using oracle 11g .
I am given a query where a lot of hard coding is there .
I am suppose to get rid of the same and do some tuning on top of the same .
If some one can suggest me the approaches or code changes then it will be great favour .
Here i am pasting the code and not providing any DDL or anything .
|
|
|
Re: Need suggestion on code refactoring [message #674757 is a reply to message #674755] |
Thu, 14 February 2019 09:14 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
Welcome to this forum
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
SELECT vw.spp_shipment_key AS row_wid, NULL AS gl_date,
vw.ship_date AS ship_date,
CASE
WHEN TRIM (vw.pharmacy_name) = 'ACCURATERX'
AND oss.shipment_dt
BETWEEN TO_DATE
(lh_logging.lh_utils.standardize_date
(t2.eff_date),
'MM/DD/YYYY'
)
AND TO_DATE
(lh_logging.lh_utils.standardize_date
(t2.end_date),
'MM/DD/YYYY'
)
THEN '55048931'
WHEN NVL (oss.update_src_filename, oss.insert_src_filename) LIKE
'%SP002A%'
THEN '55048959'
WHEN NVL (oss.update_src_filename, oss.insert_src_filename) LIKE
'%SP002B%'
THEN '55048960'
WHEN TRIM (vw.pharmacy_name) = 'BIORX'
AND oss.shipment_dt
BETWEEN TO_DATE
(lh_logging.lh_utils.standardize_date
(t2.eff_date),
'MM/DD/YYYY'
)
AND TO_DATE
(lh_logging.lh_utils.standardize_date
(t2.end_date),
'MM/DD/YYYY'
)
THEN '55048962'
WHEN TRIM (vw.pharmacy_name) = 'COMFORT-INFUSION'
AND oss.shipment_dt
BETWEEN TO_DATE
(lh_logging.lh_utils.standardize_date
(t2.eff_date),
'MM/DD/YYYY'
)
AND TO_DATE
(lh_logging.lh_utils.standardize_date
(t2.end_date),
'MM/DD/YYYY'
)
THEN '55048957'
WHEN TRIM (vw.pharmacy_name) = 'DIPLOMAT'
AND oss.shipment_dt
BETWEEN TO_DATE
(lh_logging.lh_utils.standardize_date
(t2.eff_date),
'MM/DD/YYYY'
)
AND TO_DATE
(lh_logging.lh_utils.standardize_date
(t2.end_date),
'MM/DD/YYYY'
)
THEN '55048962'
WHEN TRIM (vw.pharmacy_name) = 'FOCUSRX'
AND oss.shipment_dt
BETWEEN TO_DATE
(lh_logging.lh_utils.standardize_date
(t2.eff_date),
'MM/DD/YYYY'
)
AND TO_DATE
(lh_logging.lh_utils.standardize_date
(t2.end_date),
'MM/DD/YYYY'
)
THEN '55050717'
WHEN TRIM (vw.pharmacy_name) = 'DIPLOMAT-AFFINITY'
AND oss.shipment_dt
BETWEEN TO_DATE
(lh_logging.lh_utils.standardize_date
(t2.eff_date),
'MM/DD/YYYY'
)
AND TO_DATE
(lh_logging.lh_utils.standardize_date
(t2.end_date),
'MM/DD/YYYY'
)
THEN '55048962'
WHEN oss.shipment_dt
BETWEEN TO_DATE
(lh_logging.lh_utils.standardize_date
(t2.eff_date),
'MM/DD/YYYY'
)
AND TO_DATE
(lh_logging.lh_utils.standardize_date
(t2.end_date),
'MM/DD/YYYY'
)
THEN t2.flex_cust_id
ELSE NULL
END AS parent_customer,
'NULL' AS customer_sold_to, vw.full_product_name AS product,
vw.ndc_number AS ndc_code,
CASE
WHEN ndc.prod_id IN (1, 2, 5, 6)
THEN NVL (vw.quantity_shipped, 0)
WHEN ndc.prod_id IN (3, 4)
AND UPPER (TRIM (pharmacy_name)) NOT IN ('CIGNA TEL-DRUG')
THEN NVL (vw.quantity_shipped, 0)
* TO_NUMBER (NVL (vw.product_strength, 1))
WHEN ndc.prod_id IN (3, 4)
AND UPPER (TRIM (pharmacy_name)) IN ('CIGNA TEL-DRUG')
THEN NVL (vw.quantity_shipped, 0)
END AS units,
'NULL' AS sales,
(CASE
WHEN vw.full_product_name IN
('ARALAST', 'GLASSIA', 'ADVATE', 'ADYNOVATE', 'BEBULIN',
'FEIBA', 'HEMOFIL', 'OBIZUR', 'RECOMBINATE', 'RIXUBIS',
'VONVENDI', 'CEPROTIN')
THEN 'EU'
WHEN vw.full_product_name IN
('BUMINATE', 'FLEXBUMIN', 'CUVITRU', 'GAMMAGARD IGA',
'GAMMAGARD LIQUID', 'GAMMAGARD S/D LOW IGA', 'HYQVIA')
THEN 'VI'
ELSE vw.quantity_uom
END
) AS um,
CASE
WHEN ndc.prod_id IN (1, 2, 5, 6)
THEN NULL
WHEN ndc.prod_id IN (3, 4)
THEN TO_NUMBER (NVL (vw.product_strength, 1)
)
END AS activity_units,
CASE
WHEN NVL (UPPER (TRIM (vw.physician_address1)), '-X') <>
NVL (UPPER (TRIM (vw.physician_address2)),
'-X'
)
THEN vw.physician_address1 || ' ' || vw.physician_address2
ELSE vw.physician_address1
END AS address,
vw.physician_city AS city, vw.physician_state AS state,
vw.physician_zip AS zip_code, vw.product_code AS lot_number,
vw.order_transaction_number AS invoice_number,
NULL AS dist_customer_number, vw.dea# AS dea_number,
'NULL' AS hin_number, 'NULL' AS contract_id, 'NULL' AS buying_group,
CASE
WHEN vw.first_name || vw.last_name IS NULL
THEN 'Physician Name not provided'
ELSE vw.first_name || ' ' || vw.last_name
END AS hospital_name,
'NULL' AS purchase_price, 'NULL' AS contract_price,
'NULL' AS comments_field,
NVL (oss.update_src_filename, oss.insert_src_filename) AS file_tag,
'NULL' AS original_buying_group,
vw.first_name || ' ' || vw.last_name AS original_hospital_name,
vw.spp_patient_id AS clean_agreement_id,
vw.hub_identifier AS aatmosphere_number, 'NULL' AS novation_lic,
vw.primary_payer_name payor,
CASE
WHEN vw.pharmacy_name = 'HEALIX'
THEN CASE
WHEN SUBSTR (vw.icd_9_codes, 1, 1) = ','
AND regexp_count (vw.icd_9_codes, ',') > 1
THEN SUBSTR (vw.icd_9_codes,
2,
INSTR (vw.icd_9_codes, ',', 1, 2) - 2
)
WHEN SUBSTR (vw.icd_9_codes, 1, 1) = ','
AND regexp_count (vw.icd_9_codes, ',') = 1
THEN SUBSTR (vw.icd_9_codes, 2)
WHEN SUBSTR (vw.icd_9_codes, 1, 1) <> ','
AND regexp_count (vw.icd_9_codes, ',') >= 1
THEN SUBSTR (vw.icd_9_codes,
1,
INSTR (vw.icd_9_codes, ',', 1, 1) - 1
)
ELSE vw.icd_9_codes
END
WHEN vw.full_product_name IN
('CUVITRU', 'GAMMAGARD LIQUID', 'GAMMAGARD S/D LOW IGA',
'HYQVIA')
AND NVL (vw.icd_9_codes, 'X') = 'X'
THEN 'N/A'
ELSE vw.icd_9_codes
END AS icd9,
'NULL' AS gardian, 'SPP' AS SOURCE, pf.file_id AS file_key,
NVL (oss.update_src_filename, oss.insert_src_filename) AS file_name,
'LIQUIDHUB' AS created_by,
TO_CHAR (oss.insert_timestamp, 'YYYYMMDD') AS created_date,
'NULL' AS modified_by,
TO_CHAR (oss.update_timestamp, 'YYYYMMDD') AS modified_date,
'NULL' AS modification_desc, vw.spp_shipment_key AS row_num,
'P' AS action_flg, 'NULL' AS error_corrected_flg,
NULL AS prescribed_dose, NULL AS number_of_doses,
NULL AS total_dispensed_quantity,
TO_CHAR
(TO_DATE (vw.patient_first_ship_date, 'YYYYMMDD'),
'YYYYMMDD'
) AS therapy_start_date,
NULL AS discharge_date, TO_CHAR (vw.lh_pat_id) AS patient_id,
CASE
WHEN NVL (vw.patient_age, '-123') = '-123'
AND LENGTH (vw.patient_yob) = 4
AND vw.full_product_name IN ('HYQVIA')
THEN TO_NUMBER (SUBSTR (ship_date, 1, 4))
- TO_NUMBER (vw.patient_yob)
WHEN vw.patient_yob IN (1)
AND pharmacy_name LIKE 'ACCREDO%'
AND vw.full_product_name IN ('HYQVIA')
THEN 180
WHEN vw.patient_yob IN (2)
AND pharmacy_name LIKE 'ACCREDO%'
AND vw.full_product_name IN ('HYQVIA')
THEN 390
WHEN vw.patient_yob IN (3)
AND pharmacy_name LIKE 'ACCREDO%'
AND vw.full_product_name IN ('HYQVIA')
THEN 590
WHEN vw.patient_yob IN (4)
AND pharmacy_name LIKE 'ACCREDO%'
AND vw.full_product_name IN ('HYQVIA')
THEN 600
WHEN NVL (vw.patient_age, '-123') <> '-123'
AND pharmacy_name LIKE '%NUFACTOR%'
AND vw.patient_age >= 16
THEN 999
WHEN NVL (vw.patient_age, '-123') <> '-123'
AND pharmacy_name LIKE '%NUFACTOR%'
AND vw.patient_age < 16
THEN 1
ELSE TO_NUMBER (vw.patient_age)
END patient_age,
vw.hub_identifier AS bx_hub_id, vw.npi_# AS npi_number,
vw.baxalta_customer_id AS lh_prescriber_id,
vw.baxalta_customer_addr_id AS lh_prescriber_address_id,
'NULL' AS lh_ship_to_org_id, 'NULL' AS lh_ship_to_address_id,
CASE
WHEN UPPER (TRIM (pharmacy_name)) = 'BIORX'
THEN 'DIPLOMAT'
ELSE pharmacy_name
END pharmacy_name
FROM (SELECT *
FROM lh_ods.vw_shipment_det_ext@bax_logging
WHERE TO_NUMBER (SUBSTR (ship_date, 1, 6)) >= '201801') vw,
(SELECT *
FROM lh_ods.ods_spp_shipment@bax_logging
WHERE TO_NUMBER (TO_CHAR (shipment_dt, 'YYYYMM')) >= '201801') oss,
lh_logging.lh_ndc_master@bax_logging ndc,
(SELECT MIN (file_id) file_id, UPPER (file_name) file_name
FROM lh_logging.lh_procs_files@bax_logging
GROUP BY UPPER (file_name)) pf,
(SELECT *
FROM lh_ods.ods_spp_qty_conv_factor@bax_logging
WHERE spp_key = 11) ods_conv_fact,
(SELECT *
FROM lh_support.parent_updated_customer_list) t2
WHERE vw.spp_shipment_key = oss.spp_shipment_key
AND oss.ndc_no = ndc.ndc_no
AND vw.ndc_number = ods_conv_fact.ndc_no
AND NVL (oss.update_src_filename, oss.insert_src_filename) = UPPER (pf.file_name(+))
AND oss.shipped_qty > 0
AND TRIM (vw.pharmacy_name) = TRIM (t2.src_name(+))
AND TRIM (vw.full_product_name) = TRIM (t2.product_name(+))
AND ( (CASE
WHEN vw.full_product_name IN
('BUMINATE', 'FLEXBUMIN', 'CUVITRU',
'GAMMAGARD LIQUID', 'GAMMAGARD S/D LOW IGA',
'HYQVIA')
THEN NVL (oss.shipped_qty, 0)
END BETWEEN 1 AND 99
)
OR (CASE
WHEN vw.full_product_name IN ('ARALAST', 'GLASSIA')
AND UPPER (TRIM (pharmacy_name)) NOT IN ('CIGNA TEL-DRUG')
THEN NVL (oss.shipped_qty, 0)
* TO_NUMBER (NVL (vw.product_strength, 1))
WHEN vw.full_product_name IN ('ARALAST', 'GLASSIA')
AND UPPER (TRIM (pharmacy_name)) IN ('CIGNA TEL-DRUG')
THEN NVL (oss.shipped_qty, 0)
END
) > 200
)
|
|
|
|
Re: Need suggestion on code refactoring [message #674764 is a reply to message #674757] |
Thu, 14 February 2019 12:57 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
Without seeing any DDL, can't say too much, but if your function is returning a DATE, then using TO_DATE on it is so wrong.
select to_date(sysdate,'MM/DD/YYYY') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
|
|
|
|
|
|
Re: Need suggestion on code refactoring [message #674772 is a reply to message #674757] |
Fri, 15 February 2019 08:01 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
This clause, FROM (SELECT *
FROM lh_ods.vw_shipment_det_ext@bax_logging
WHERE TO_NUMBER (SUBSTR (ship_date, 1, 6)) >= '201801') vw,
(SELECT *
FROM lh_ods.ods_spp_shipment@bax_logging
WHERE TO_NUMBER (TO_CHAR (shipment_dt, 'YYYYMM')) >= '201801') oss,
lh_logging.lh_ndc_master@bax_logging ndc,
(SELECT MIN (file_id) file_id, UPPER (file_name) file_name
FROM lh_logging.lh_procs_files@bax_logging
GROUP BY UPPER (file_name)) pf,
(SELECT *
FROM lh_ods.ods_spp_qty_conv_factor@bax_logging
WHERE spp_key = 11) ods_conv_fact,
(SELECT *
FROM lh_support.parent_updated_customer_list) t2 is not good.
First, your use of SELECT * will be causing the optimizer problems. You should explicitly project only the columns you need. Once you project "*", the optimizer is limited in what it can do to eliminate table access.
Second, these structures,WHERE TO_NUMBER (TO_CHAR (shipment_dt, 'YYYYMM')) >= '201801') oss, will suppress use of indexes and confuse the optimizer about cardinalities. It is also a straightforweard bug to compare a number to a string. You should rather use something likewhere shipment_date >= to_date('201801','yyyymm')
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 08:17:23 CDT 2024
|