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 |
|
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 #663765 is a reply to message #663764] |
Fri, 16 June 2017 10:28 |
|
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
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 09:27:42 CDT 2024
|