Home » SQL & PL/SQL » SQL & PL/SQL » stored proc execution based on selected value from a column (Oracle 11.2.0.4)
stored proc execution based on selected value from a column [message #663761] Fri, 16 June 2017 09:10 Go to next message
veepee
Messages: 16
Registered: June 2017
Junior Member
I am using Oracle 11.2.0.4 for the stored procedure execution.

The Insert statement inside the procedure should only occur if at least one of the values for interval (interval_number column in the table 'rpv_parsed_dam0utput') is not a zero. 


From the sample data - the procedure should execute for first and second sets of data and, not for the 3rd set. Not sure the best way to go about making the insert statement to work only for this condition.

INTERVAL_SET1	SET2	SET3
4	         0	0
12	         0	0
1	         0	0
1	         0	0
0	         2	0
2	         0	0
0	         0	0
1	         0	0
0	         0	0
0	         0	0
1	         0	0


PROCEDURE cal_laenrgyamt_q(p_execution_id  IN NUMBER,
                               p_operating_day IN statementschedule.operatingdate%TYPE,
                               p_stmt_schedule IN statementschedule.uidstatementsched%TYPE,
                               p_calc_group    IN calcgroup.calcgroup%TYPE,
                               p_save_channel  IN statementschedule.savechannel%TYPE,
                               p_partition_key IN rp_stage_damoutput.partition_key%TYPE) AS
    
        c_sub constant varchar2(30) := 'cal_laenrgyamt_q';
        c_uom constant number := 100;
    
        v_starttime date := trunc(p_operating_day);
        v_stoptime  date := trunc(p_operating_day + 1) - (1 / 86400);
    
        c_bd_input_dailenrgytot    constant varchar2(32) := 'dailenrgytot';
        c_bd_input_erngrevtot constant varchar2(32) := 'erngrevtot';
        c_bd_input_daers         constant varchar2(32) := 'daers';
        c_bd_output_energyamt    constant varchar2(32) := 'energyamt';
    
    BEGIN
 
        INSERT INTO rp_stage_damoutput rsdo
           (rsdo.execution_id,
             rsdo.partition_key,
             rsdo.uidstatementsched,
             rsdo.calcgroup,
             rsdo.saverecorder,
             rsdo.savechannel,
             rsdo.uidbilldeterminant,
             rsdo.qsecode,
             rsdo.uidmarket,
             rsdo.starttime,
             rsdo.stoptime,
             rsdo.spi,
             rsdo.uomcode,
             rsdo.dstparticipant,
             rsdo.origin,
             rsdo.chnlcuttimestamp,
             rsdo.interval_number,
             rsdo.interval_value,  rsdo.interval_status
             )
            SELECT p_execution_id AS execution_id,
                   p_partition_key AS partition_key,
                   p_stmt_schedule AS uidstatementsched,
                   p_calc_group AS calcgroup,
                   c_bd_output_energyamt || '_' || daers.qsecode AS saverecorder,
                   p_save_channel AS savechannel,
                   (SELECT uidbilldeterminant
                      FROM billdeterminant bd
                     WHERE bd.billdetermcode = c_bd_output_energyamt) AS uidbilldeterminant,
                   daers.qsecode AS qsecode,
                   (SELECT uidmarket
                      FROM market m
                     WHERE m.marketcode = 'DAM') AS uidmarket,
                   v_starttime AS starttime,
                   v_stoptime AS stoptime,
                   daers.spi AS spi,
                   c_uom AS uomcode,
                   'Y' AS dstparticipant,
                   'C' AS origin,
                   SYSDATE AS chnlcuttimestamp,
                   daers.interval_number AS interval_number,
                   ROUND(-1 * (dailenrgytot.interval_value +
                         erngrevtot.interval_value) *
                         daers.interval_value,
                         2) AS interval_value,
                   ' ' AS interval_status
              FROM rp_stage_damoutput daers
             INNER JOIN billdetermin@nt bd
                ON BD.uidbilldeterminant = daers.uidbilldeterminant
             INNER JOIN (SELECT rpdo.saverecorder,
                                rpdo.starttime,
                                rpdo.stoptime,
                                rpdo.savechannel,
                                rpdo.qsecode,
                                rpdo.spi,
                                rpdo.total,
                                rpdo.maximum,
                                rpdo.minimum,
                                rpdo.interval_number,
                                rpdo.interval_value,
                                rpdo.intervalcount
                           FROM rpv_parsed_dam0utput rpdo
                          INNER JOIN billdeterminant bd
                             ON bd.uidbilldeterminant =
                                rpdo.uidbilldeterminant
                          WHERE bd.billdetermcode = c_bd_input_dailenrgytot
                            AND rpdo.saverecorder = c_bd_input_dailenrgytot
                            AND rpdo.savechannel = p_save_channel
                            AND rpdo.starttime = v_starttime
                            AND rpdo.stoptime = v_stoptime) dailenrgytot
                ON dailenrgytot.interval_number = daers.interval_number
               AND dailenrgytot.savechannel = daers.savechannel
               AND dailenrgytot.starttime = daers.starttime
               AND dailenrgytot.stoptime = daers.stoptime
             INNER JOIN (SELECT rpdo.saverecorder,
                                rpdo.starttime,
                                rpdo.stoptime,
                                rpdo.savechannel,
                                rpdo.qsecode,
                                rpdo.spi,
                                rpdo.total,
                                rpdo.maximum,
                                rpdo.minimum,
                                rpdo.interval_number,
                                rpdo.interval_value,
                                rpdo.intervalcount
                           FROM rpv_parsed_dam0utput rpdo
                          INNER JOIN billdeterminant bd
                             ON bd.uidbilldeterminant =
                                rpdo.uidbilldeterminant
                          WHERE bd.billdetermcode = c_bd_input_erngrevtot
                            AND rpdo.saverecorder = c_bd_input_erngrevtot
                            AND rpdo.savechannel = p_save_channel
                            AND rpdo.starttime = v_starttime
                            AND rpdo.stoptime = v_stoptime) erngrevtot
                ON erngrevtot.interval_number = daers.interval_number
               AND erngrevtot.savechannel = daers.savechannel
               AND erngrevtot.starttime = daers.starttime
               AND erngrevtot.stoptime = daers.stoptime
             WHERE bd.billdetermcode = c_bd_input_daers
               AND daers.execution_id = p_execution_id
               AND daers.partition_key = p_partition_key
               AND daers.saverecorder LIKE (c_bd_input_daers || '\_%') escape '\'
               AND daers.savechannel = p_save_channel
               AND daers.starttime = v_starttime
               AND daers.stoptime = v_stoptime;

END cal_laenrgyamt_q;
Re: stored proc execution based on selected value from a column [message #663762 is a reply to message #663761] Fri, 16 June 2017 09:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.
You did a FINE job of presenting your first post here.

Perhaps I need another cup of coffee to wake up more.

What is the relationship between the 3 "interval sets" in the code box & the nicely formatted procedure?
Re: stored proc execution based on selected value from a column [message #663763 is a reply to message #663762] Fri, 16 June 2017 09:47 Go to previous messageGo to next message
veepee
Messages: 16
Registered: June 2017
Junior Member
Thank you BlackSwan for the comment.

These interval sets are values from the rpv_parsed_dam0utput table. So if we were to run a query like below, the result set could be in one of 3 scenarios.

SELECT rpdo.interval_value from rpv_parsed_dam0utput rpdo;  
Re: stored proc execution based on selected value from a column [message #663764 is a reply to message #663763] Fri, 16 June 2017 10:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I do not understand the logic behind such a requirement, but it could be implemented using below

WHERE SUM(rpdo.interval_value) > 0
Re: stored proc execution based on selected value from a column [message #663765 is a reply to message #663764] Fri, 16 June 2017 10:28 Go to previous messageGo to next message
veepee
Messages: 16
Registered: June 2017
Junior Member
So the inner query with the alias erngrevtot should be like this?? (I presume the below code is what you meant)

                         SELECT rpdo.saverecorder,
                                rpdo.starttime,
                                rpdo.stoptime,
                                rpdo.savechannel,
                                rpdo.qsecode,
                                rpdo.spi,
                                rpdo.total,
                                rpdo.maximum,
                                rpdo.minimum,
                                rpdo.interval_number,
                                rpdo.interval_value,
                                rpdo.intervalcount
                           FROM rpv_parsed_dam0utput rpdo
                          INNER JOIN billdeterminant bd
                             ON bd.uidbilldeterminant =
                                rpdo.uidbilldeterminant
                          WHERE bd.billdetermcode = c_bd_input_erngrevtot
                            AND rpdo.saverecorder = c_bd_input_erngrevtot
                            AND rpdo.savechannel = p_save_channel
                            AND rpdo.starttime = v_starttime
                            AND rpdo.stoptime = v_stoptime
                         GROUP BY
                                rpdo.saverecorder,
                                rpdo.starttime,
                                rpdo.stoptime,
                                rpdo.savechannel,
                                rpdo.qsecode,
                                rpdo.spi,
                                rpdo.total,
                                rpdo.maximum,
                                rpdo.minimum,
                                rpdo.interval_number,
                                rpdo.interval_value,
                                rpdo.intervalcount
                         HAVING SUM (rpdo.interval_value) > 0
Re: stored proc execution based on selected value from a column [message #663766 is a reply to message #663765] Fri, 16 June 2017 10:39 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
We don't have your tables.
We don't have your data.
We don't have your requirements.

I have no idea if posted code produces desired/expected result set.
Previous Topic: Oracle database link performance cum security issue
Next Topic: grouping of records
Goto Forum:
  


Current Time: Thu Mar 28 19:28:40 CDT 2024