Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2008
    Posts
    13

    Unanswered: subtracting values in PL/SQL and merging result into cursor or table

    I am trying to pull in values from two different dates on a table (start and end) and subtract one from the other and store it into my result set which I'll be passing back via a cursor with my PL/SQL procedure. I'm a little stuck on how to accomplish this. Here's what I have so far. The FOR loop doesn't work and it's obviously not the way to go but it represents what I'm trying to get to.

    Code:
    create or replace
    PROCEDURE BLMROUTING (startDate IN DATE, endDate IN DATE, series IN VARCHAR, 
         p_cursor OUT SYS_REFCURSOR) AS
    
    TYPE rec IS RECORD 
       (integ       diag.blm_acct.integ_over_val%TYPE,
        signal      diag.PV_SGNL_ID_ASSC.sgnl_id%TYPE,
        sampleDate  diag.blm_acct.smpl_dte%TYPE);
    
    TYPE tableResults IS TABLE OF rec; 
    
    ResultTableStart tableResults; 
    ResultTableEnd tableResults; 
    ResultTable tableResults; 
    
    BEGIN  
    
    SELECT tc.integ_over_val, ta.sgnl_id, MAX(startDate) as MaxStartDate
      BULK COLLECT INTO ResultTableStart
      FROM diag.pv_sgnl_id_assc ta 
        INNER JOIN diag.series_sgnl_rec_asgn tb
           ON ta.sgnl_id = tb.sgnl_id
        INNER JOIN diag.blm_acct tc on ta.pv_id = tc.pv_id
    WHERE tb.series_id = series ORDER BY disp_ord_nbr;
    
    SELECT tc.integ_over_val, ta.sgnl_id, MAX(endDate) as MaxEndDate
      BULK COLLECT INTO ResultTableEnd
      FROM diag.pv_sgnl_id_assc ta 
        INNER JOIN diag.series_sgnl_rec_asgn tb
           ON ta.sgnl_id = tb.sgnl_id
        INNER JOIN diag.blm_acct tc on ta.pv_id = tc.pv_id
    WHERE tb.series_id = series ORDER BY disp_ord_nbr;
    
    FOR j IN ResultTableStart.FIRST..ResultTableStart.LAST LOOP
      INSERT INTO ResultTable VALUES (ResultTableStart(j).integ_over_val - 
        ResultTableEnd(j).integ_over_val,
        ResultTableStart(j).sgnl_id, ResultTableStart(j).MaxStartDate);
    END LOOP;
    
    END BLMROUTING;

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking More arrays?

    Try:
    Code:
    Create -- etc--
    TYPE tableResults IS TABLE OF rec
      INDEX BY BINARY_INTEGER;
    j pls_integer;
    Begin
    -- etc--
    
    FOR j IN 1..ResultTableStart.COUNT LOOP
        ResultTable(j).integ         := ResultTableStart(j).integ_over_val
                                      - ResultTableEnd(j).integ_over_val;
        ResultTable(j).signal        := ResultTableStart(j).sgnl_id;
        ResultTable(j).sampleDate    := ResultTableStart(j).MaxStartDate;
    END LOOP;
    -- etc --
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    A bit puzzled by your procedure:
    1) Both queries are the same (apart from the max)
    2) Both queries wont work because there is no group by I think.
    3) Why not just do it with a single sql statement if its possible, i.e put the two queries into two subqueries, join them and then do the subtraction.

    Alan

  4. #4
    Join Date
    Nov 2008
    Posts
    13
    Thanks for the replies. I tried the loop and it compiles but I still need a way to return it as a cursor when I try

    Code:
    OPEN p_cursor FOR select val, signal FROM ResultTable;
    It complains that the "table or view does not exist". I actually need to return this as a ref cursor since that is what the java method which calls this procedure is expecting. What good are these PL/SQL tables if we can't reference them once we put data into them? I'm sure it is just my ignorance of PL/SQL here that's causing me problems, but could someone help further with this?

    Also Alan you are probably right that I need GROUP BY in the queries. If I could get this to work as a query without using a Procedure that would be great but I've tried what you suggested and cannot get the syntax to work for it...
    Last edited by Solerous; 11-07-08 at 10:27.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Probably the easiest way to do this is if you can provide a simplified version of the 3 tables with sample data (just for the relevant columns) and then the output you would expect from your procedure given the values of the 3 params passed in.

    Alan

  6. #6
    Join Date
    Nov 2008
    Posts
    13
    Here are the three tables I need to access:

    DIAG.SERIES_SGNL_REC_ASGN

    Code:
         SERIES_ID   SGNL_ID       DISP_ORD              
    -------------- ------------- ---------- 
         Series 10   SlowPulse60      0    
         Series 10   SlowPulse65      1
         Series 11   SlowPulse62      0
    DIAG.PV_SGNL_ID_ASSC

    Code:
         PV_ID   SGNL_ID                    
    ---------- ------------- 
           271   SlowPulse60
            12   SlowPulse62
           165   SlowPulse65
    DIAG.BLM_ACCT

    Code:
         PV_ID            SMPL_DTE        INTEG_SINCE_VAL              
    ---------- ------------------------ ------------------
           271   05/05/2008 11:04:30 AM		34.23    
           271   05/05/2008 11:05:00 AM		35.678
           165   05/05/2008 11:04:30 AM 	        25.95
    I pass in a series name, say 'Series 10'. I'll look up all the SGNL_ID names for that series in SERIES_SGNL_REC_ASGN and order the results based on DISP_ORD.

    Then I'll look up all of the SGNL_ID names up in PV_SGNL_ID_ASSC and get the PV_ID number for each SGNL_ID.

    Then I look up all of the PV_IDs in BLM_ACCT and I need two results for each signal, one for the first SMPL_DATE entry *before* a given 'start' date, and the other for the next SMPL_DATE entry *after* a given 'end' date. Then I need to subtract the start result's INTEG_SINCE_VAL from then end result's INTEG_SINCE_VAL.

    Finally the end result should look something like this and should be listed in the correct DISP_ORD though I don't need to return the DISP_ORD value:

    Code:
         SGNL_ID         DIFF_VAL              
    ---------------  --------------
        SlowPulse60   	21.345    
        SlowPulse65   	15.4725
        SlowPulse68  	18.3323
    So just to sum up, the inputs would be a Series name, a start date, and an end date. I just need the difference between the values on the start and end dates for each signal in the series. Thanks in advance for the help!
    Last edited by Solerous; 11-10-08 at 11:28.

  7. #7
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    For the BLM_ACCT part, try this to get your sample values based on beg/end dates
    This does assume PC_ID + SMPL_DTE is unique, may be a bad assumption...

    Code:
    --------------------------------------------------
    -- assumes pv_id + smpl_dte are unique 
    --------------------------------------------------
    
    select
      beg.pv_id, beg.integ_since_val - ending.integ_since_val "DIFF_VAL"
    from
      (
      select 
        a.pv_id, a.integ_since_val from blm_acct a
      where exists
      (select 1 from
        -- get first date on or before BEGDTE
        (select pv_id, max(smpl_dte) beg_dte from blm_acct 
         where smpl_dte <= :BEGDTE
         group by pv_id
        ) xbeg 
        where a.pv_id=xbeg.pv_id and a.smpl_dte=xbeg.beg_dte
      )) beg,
      ---------
      (
      select 
        b.pv_id, b.integ_since_val from blm_acct b
      where exists
      (select 1 from
        -- get first date on or after ENDDTE
        (select pv_id, min(smpl_dte) end_dte from blm_acct 
         where smpl_dte >= :ENDDTE
         group by pv_id
        ) xend
        where b.pv_id=xend.pv_id and b.smpl_dte=xend.end_dte
      )) ending
    where
      beg.pv_id=ending.pv_id;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •