CODEWITHSUNDEEP
oracle-databasestored-procedures
Brandon
Brandon

Reputation: 1465

modify results of cusor from oracle based on results

I have a stored procedure that returns a ref cursor. What I need to do is filter the results even further. Due to things being protected I'm not sure how much information I can give out. In the results there is an 'Editedflag' column. If that column value is 'Y' I need to get the 'PassID' column value and find the other result that has the same 'PassID' and remove it from the results. I am at a loss of what to even google for this type of question. Thanks.

SELECT  a.log_curv_id LogCurveId
        ,a.log_curv_type_id LogCurveTypeId
        ,c.log_curv_type_desc LogCurveTypeDesc
        ,a.cmpl_fac_id CompletionId
        ,d.fac_nme CompletionName
        ,b.edtd_curv_indc EditedIndicator
        ,b.stsfr_qlty_indc SatisfactoryIndicator
        ,a.top_md_qty TopMeasuredDepth
        ,a.base_md_qty BaseMeasuredDepth
        ,NVL(TO_NUMBER(eds.p_surv_load.fget_log_curv_spec(a.log_curv_id,2248)),NULL) FlowRate
        ,NVL(TO_NUMBER(eds.p_surv_load.fget_log_curv_spec(a.log_curv_id,800)),NULL) TubingPressure
        ,NVL(TO_NUMBER(eds.p_surv_load.fget_log_curv_spec(a.log_curv_id,2249)),NULL) LossAbove
        ,NVL(TO_NUMBER(eds.p_surv_load.fget_log_curv_spec(a.log_curv_id,2250)),NULL)LossBelow
        ,b.lggg_tlstr_pass_id PassId
        FROM eds.log_curv a,
        eds.acqn_curv b,
        eds.log_curv_type c,
        eds.fac_nme d
        WHERE a.log_curv_id = b.log_curv_id
        AND b.lggg_tlstr_pass_id = 188481
        AND a.log_curv_type_id = c.log_curv_type_id
        AND NVL(a.cmpl_fac_id,0) = d.fac_id(+)
        AND d.term_dttm IS NULL
        AND a.del_indc = 'N'
        AND b.del_indc = 'N'
        AND c.del_indc = 'N'
        AND d.fac_nme_type_cde = 'NME'  /*12/09/2013 ksk NEW*/
        AND d.del_indc = 'N';           /*12/09/2013 ksk NEW*/

Results

enter image description here

You will notice that the completion id is the same on the results. I need to check for the Y flag in the Editedindicator column and if present find the results that has the matching completionid and remove it.

Upvotes: 0

Views: 40

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

with yq as (
select  a.log_curv_id, a.log_curv_type_id, c.log_curv_type_desc, a.cmpl_fac_id, 
        d.fac_nme, b.edtd_curv_indc, b.stsfr_qlty_indc, a.top_md_qty, a.base_md_qty, 
        b.lggg_tlstr_pass_id PassId,
        count (case when edtd_curv_indc = 'Y' then 1 end) 
         over (partition by b.lggg_tlstr_pass_id) cnt
  from log_curv a
  join acqn_curv b     on b.del_indc = 'N' and a.log_curv_id = b.log_curv_id
  join log_curv_type c on c.del_indc = 'N' and a.log_curv_type_id = c.log_curv_type_id
  left join fac_nme d  on nvl(a.cmpl_fac_id,0) = d.fac_id and d.del_indc = 'N' 
                          and d.term_dttm is null and d.fac_nme_type_cde = 'NME'
  where b.lggg_tlstr_pass_id = 188481 and a.del_indc = 'N')
select yq.* from yq where (cnt>0 and edtd_curv_indc='Y') or cnt = 0

SQLFiddle demo

In the query above I used analytic count() function to check if exists rows with edtd_curv_indc='Y' for specific PassId. If so only these rows will be shown, if not - rest of rows, according to condition where (cnt>0 and edtd_curv_indc='Y') or cnt = 0. In answer I ignored columns using function fget_log_curv_spec, they are not important for this example. I also changed old-style joins to ansi syntax.

Upvotes: 2

Related Questions