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 Go to next message
harishankar_kar
Messages: 21
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 Go to previous messageGo to next message
BlackSwan
Messages: 26460
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 #674761 is a reply to message #674755] Thu, 14 February 2019 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 66269
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

The SELECT part does not matter (in first step).
Replace that by * (or better the list of columns you select) and optimize it.

Re: Need suggestion on code refactoring [message #674764 is a reply to message #674757] Thu, 14 February 2019 12:57 Go to previous messageGo to next message
joy_division
Messages: 4944
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 #674765 is a reply to message #674757] Thu, 14 February 2019 12:58 Go to previous messageGo to next message
harishankar_kar
Messages: 21
Registered: July 2014
Location: India
Junior Member
HI Please find the code in the attachment .
I have changed the code as per formatter .

please suggest me how to decrease the lines of code if possible .


Re: Need suggestion on code refactoring [message #674766 is a reply to message #674765] Thu, 14 February 2019 13:57 Go to previous messageGo to next message
Michel Cadot
Messages: 66269
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Replace all what is in "SELECT ..." by "SELECT *".

And post ALL what is asked in the link, you just did point 1.

Re: Need suggestion on code refactoring [message #674767 is a reply to message #674765] Thu, 14 February 2019 14:36 Go to previous messageGo to next message
BlackSwan
Messages: 26460
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Need suggestion on code refactoring [message #674772 is a reply to message #674757] Fri, 15 February 2019 08:01 Go to previous messageGo to next message
John Watson
Messages: 7823
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 like
where shipment_date >= to_date('201801','yyyymm')


Re: Need suggestion on code refactoring [message #675074 is a reply to message #674772] Fri, 08 March 2019 04:00 Go to previous messageGo to next message
harishankar_kar
Messages: 21
Registered: July 2014
Location: India
Junior Member
Hi ,
Sorry for late reply .I will attach the DDL and all insert statements by EOD today .
Sorry for puting all in hold on this .
Re: Need suggestion on code refactoring [message #675078 is a reply to message #675074] Fri, 08 March 2019 14:27 Go to previous messageGo to next message
BlackSwan
Messages: 26460
Registered: January 2009
Location: SoCal
Senior Member
harishankar_kar wrote on Fri, 08 March 2019 02:00
Hi ,
Sorry for late reply .I will attach the DDL and all insert statements by EOD today .
Sorry for puting all in hold on this .

OK but you are not the same forum user who started this thread.
Please clarify & explain your relationship to OP.
Re: Need suggestion on code refactoring [message #675079 is a reply to message #675078] Sat, 09 March 2019 07:21 Go to previous messageGo to next message
harishankar_kar
Messages: 21
Registered: July 2014
Location: India
Junior Member
HI ,

I am the same user who started the discussion .
Re: Need suggestion on code refactoring [message #675087 is a reply to message #675079] Sat, 09 March 2019 07:53 Go to previous messageGo to next message
BlackSwan
Messages: 26460
Registered: January 2009
Location: SoCal
Senior Member
harishankar_kar wrote on Sat, 09 March 2019 05:21
HI ,

I am the same user who started the discussion .
and still provided no DDL
Re: Need suggestion on code refactoring [message #675122 is a reply to message #675087] Sat, 09 March 2019 13:50 Go to previous messageGo to next message
harishankar_kar
Messages: 21
Registered: July 2014
Location: India
Junior Member
Please find the code
  • Attachment: ddls.sql
    (Size: 232.41KB, Downloaded 9 times)
Re: Need suggestion on code refactoring [message #675123 is a reply to message #675122] Sat, 09 March 2019 13:50 Go to previous messageGo to next message
harishankar_kar
Messages: 21
Registered: July 2014
Location: India
Junior Member
few more
Re: Need suggestion on code refactoring [message #675124 is a reply to message #675122] Sat, 09 March 2019 13:51 Go to previous message
harishankar_kar
Messages: 21
Registered: July 2014
Location: India
Junior Member
one more attachment
Previous Topic: nvl and is null
Next Topic: Replacing bulk collect with loop
Goto Forum:
  


Current Time: Mon Mar 25 08:42:01 CDT 2019