Oracle timeframe conversion
Date : March 29 2020, 07:55 AM
Hope that helps I'm assuming valid_from/valid_to never spans multiple years; i.e., TO_DATE(valid_from,'YYYY') always = TO_DATE(valid_to,'YYYY'). SQL> CREATE TABLE source (
2 bid VARCHAR2(5)
3 , valid_from DATE
4 , valid_to DATE
5 , qty NUMBER
6 );
Table created.
SQL> INSERT INTO source VALUES ('00001',TO_DATE('20010201','YYYYMMDD'),TO_DATE('20010630','YYYYMMDD'),132);
1 row created.
SQL> INSERT INTO source VALUES ('00002',TO_DATE('20020301','YYYYMMDD'),TO_DATE('20021231','YYYYMMDD'),59);
1 row created.
SQL> CREATE TABLE destination (
2 bid VARCHAR2(5)
3 , jan NUMBER
4 , feb NUMBER
5 , mar NUMBER
6 , apr NUMBER
7 , may NUMBER
8 , jun NUMBER
9 , jul NUMBER
10 , aug NUMBER
11 , sep NUMBER
12 , oct NUMBER
13 , nov NUMBER
14 , dec NUMBER
15 );
Table created.
SQL> COLUMN jan FORMAT 999
SQL> COLUMN feb FORMAT 999
SQL> COLUMN mar FORMAT 999
SQL> COLUMN apr FORMAT 999
SQL> COLUMN may FORMAT 999
SQL> COLUMN jun FORMAT 999
SQL> COLUMN jul FORMAT 999
SQL> COLUMN aug FORMAT 999
SQL> COLUMN sep FORMAT 999
SQL> COLUMN oct FORMAT 999
SQL> COLUMN nov FORMAT 999
SQL> COLUMN dec FORMAT 999
SQL> INSERT INTO destination
2 SELECT bid
3 , NVL(MAX(DECODE(r,01,split_qty)),0) jan
4 , NVL(MAX(DECODE(r,02,split_qty)),0) feb
5 , NVL(MAX(DECODE(r,03,split_qty)),0) mar
6 , NVL(MAX(DECODE(r,04,split_qty)),0) apr
7 , NVL(MAX(DECODE(r,05,split_qty)),0) may
8 , NVL(MAX(DECODE(r,06,split_qty)),0) jun
9 , NVL(MAX(DECODE(r,07,split_qty)),0) jul
10 , NVL(MAX(DECODE(r,08,split_qty)),0) aug
11 , NVL(MAX(DECODE(r,09,split_qty)),0) sep
12 , NVL(MAX(DECODE(r,10,split_qty)),0) oct
13 , NVL(MAX(DECODE(r,11,split_qty)),0) nov
14 , NVL(MAX(DECODE(r,12,split_qty)),0) dec
15 FROM
16 (
17 SELECT x.bid
18 , x.month_abbr
19 , x.r
20 , x.rn
21 , x.total_months
22 , x.qty
23 , FLOOR(x.qty / x.total_months)
24 + DECODE(x.rn
25 , x.total_months, MOD(x.qty, x.total_months)
26 , 0) split_qty
27 FROM (SELECT s.bid
28 , months.r
29 , ROW_NUMBER()
30 OVER (PARTITION BY s.bid
31 ORDER BY months.r) rn
32 , COUNT(*)
33 OVER (PARTITION BY s.bid) total_months
34 , s.qty
35 FROM (SELECT ROWNUM r
36 FROM DUAL
37 CONNECT BY LEVEL <= 12) months
38 , source s
39 WHERE TO_CHAR(s.valid_from,'YYYY') = TO_CHAR(s.valid_to,'YYYY')
40 AND months.r BETWEEN TO_NUMBER(TO_CHAR(s.valid_from,'MM'))
41 AND TO_NUMBER(TO_CHAR(s.valid_to,'MM'))) x
42 )
43 GROUP BY bid
44 ;
2 rows created.
SQL> SELECT *
2 FROM destination
3 ;
BID JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
00001 0 26 26 26 26 28 0 0 0 0 0 0
00002 0 0 5 5 5 5 5 5 5 5 5 14
SQL>
|
Oracle select months out of a timeframe
Tag : sql , By : Stephen Dewar
Date : March 29 2020, 07:55 AM
wish help you to fix your issue You should use a row generator: select
id,
ADD_MONTHS( "from", N.N ),
to_char( dt_column, 'mm' ) ,
to_char( dt_column, 'yyyy' )
from
yourTable t
inner join
(SELECT ROWNUM n
FROM ( SELECT 1 just_a_column
FROM dual
CONNECT BY LEVEL <= 365
) N
on ADD_MONTHS( "from", N.N ) <= last_day( t."to" )
|
Oracle Apex: Dynamic Calculation in Classic Report
Date : March 29 2020, 07:55 AM
this one helps. Im assuming that you know how to get the name attribute of the textbox under the attribute5 column using Inspect element feature of your browser. It's something like "f01","f02" or maybe higher, just check it out. Another assumption(based on your given data) is that row 5 contains the total of rows 1 to 4 and row 7 contains the total of rows 5 and 6. Once you got the name, paste this function on your page's "Function and Global Variable Declaration" property: function setTotals(itemname){
var v_subtotal=0;
var v_total=0;
for(i=0;i<4;i++){
v_subtotal = Number(v_subtotal) + Number(document.getElementsByName(itemname)[i].value);
}
v_total = Number(v_subtotal) + Number(document.getElementsByName(itemname)[5].value);
document.getElementsByName(itemname)[4].value = v_subtotal;
document.getElementsByName(itemname)[6].value = v_total;
}
function setOnChangeEvent(itemname){
for(i=0;i<4;i++){
document.getElementsByName(itemname)[i].setAttribute("onchange","setTotals(\"" + itemname + "\")");
}
$("[name=" + itemname + "]").eq(5).change(function(){
$("[name=" + itemname + "]").eq(6).val(Number($("[name=" + itemname + "]").eq(4).val()) + Number($("[name=" + itemname + "]").eq(5).val()));
});
$("[name=" + itemname + "]").eq(4).attr("disabled","disabled");
$("[name=" + itemname + "]").eq(6).attr("disabled","disabled");
}
setTotals(itemname );
setOnChangeEvent(itemname );
setTotals("f02");
setOnChangeEvent("f02");
<input onchange="setTotals('f02')" name="f02" size="20" maxlength="2000" value="5" type="text">
|
Dynamic calculation in oracle apex tabular form : sys.htp.p
Date : March 29 2020, 07:55 AM
To fix this issue I have tabular form where col1 and col2 are numbers, and col3 should contain the difference between col1 and col2. , Instead of using expensive AJAX call to database, use JavaScript. function f_CALC_DIFF(pThis) {
var row_id = pThis.id.substr(4);
var s = $('#f18_'+row_id).val().replace(/[^\d.-]/g, '');
var curr = $(pThis).val().replace(/[^\d.-]/g, '');
if(!s){
var s= 0;
var prev = s;}
else{
var prev = s;}
var diff = 0;
diff = (curr - prev);
var n = diff.toFixed(2);
alert (n);
$('#f23_'+row_id).val(n);
// Please remove the AJAX call to database.
/*apex.server.process
( "CALC_DIFF", { x01: prev, x02: curr },
{ success: function( pData ) {
$('#f23_'+row_id).val(pData);}}
);
} */
|
Need help for dynamic calculation in oracle sql query
Tag : sql , By : delphiace
Date : March 29 2020, 07:55 AM
seems to work fine I Have a table tab_1 with below values. , "Need help writing the query for below logic." create or replace function dyn_calc
(p_id in number)
return number
is
result number;
n1 number;
n2 number;
l_rec t23%rowtype;
l_val number;
type split_calc_r is record (
val1 number
, operator varchar2(1)
, val2 number
);
l_calc_rec split_calc_r;
function get_rec
(p_id in number)
return t23%rowtype
is
rv t23%rowtype;
begin
select *
into rv
from t23
where id = p_id;
return rv;
end get_rec;
procedure split_calc
(p_calc in varchar2
, p_n1 out number
, p_n2 out number
, p_operator out varchar2)
is
begin
p_n1 := regexp_substr(p_calc, '[0-9]+', 1, 1);
p_n2 := regexp_substr(p_calc, '[0-9]+', 1, 2);
p_operator := translate(p_calc, '-+*%01923456789','-+*%'); --regexp_substr(p_calc, '[\-\+\*\%]', 1, 1);
end split_calc;
function exec_calc
(p_n1 in number
, p_n2 in number
, p_operator in varchar2)
return number
is
rv number;
begin
execute immediate
'select :n1 ' || p_operator || ' :n2 from dual'
into rv
using p_n1, p_n2;
return rv;
end exec_calc;
begin
l_rec := get_rec(p_id);
if l_rec.value is not null then
result := l_rec.value;
else
split_calc(l_rec.calculation
, l_calc_rec.val1
, l_calc_rec.val2
, l_calc_rec.operator);
n1 := dyn_calc (l_calc_rec.val1);
n2 := dyn_calc (l_calc_rec.val2);
result := exec_calc(n1, n2, l_calc_rec.operator);
end if;
return result;
end;
/
SQL> select dyn_calc(6) from dual;
DYN_CALC(6)
-----------
20
SQL>
select id, calculation, dyn_calc(id) as value
from t23
where calculation is not null;
|
Boards Message :
Related QUESTIONS :