ORA-01476: divisor is equal to zero

select 

 

'&field1' field1,

'&field2' field2,

case 

    when '&field1' = 0 then 0

    when '&field1' != 0 then (('&field1'/'&field2')*100) 

end zero1_,

decode('&field2',0,0,(('&field1'/'&field2')*100)) zero2_,

decode('&field2',0,null,(('&field1'/'&field2')*100)) zero3_ 

from dual 

Difference between row_number(), rank() and dense_rank() window functions in Oracle SQL

-- difference between row_number(), rank(), and dense_rank()
-- will only be visible when there were duplicates.
-- row_number gives consecutive ranking even with duplicate
-- rank and dense_rank give the same ranking but rank has a jump
-- while dense_rank doesn't have jump

select e.*,
row_number() over (order by salary desc) row_number,
rank() over (order by salary desc) rank,
dense_rank() over (order by salary desc) as dense_rank
from #Employee e

                                        row_number rank dense_rank

AD_PRES 24000 90 1 1 1

AD_VP 17000 100 90 2 2 2

AD_VP 17000 100 90 3 2 2

SA_MAN 14000 0.4 100 80 4 4 3

ICON STATUS

 create table city_statuses
 (city_name varchar2(50),
  status1   varchar2(2),
    status2   varchar2(2),
    status3   varchar2(2)
  )
  
  insert into city_statuses
  values('Dhaka','1','0','-1')
  
  
  
  insert into city_statuses
  values('Dhaka','1','0','-1')
    insert into city_statuses
  values('Dhaka','1','0','-1')
    insert into city_statuses
  values('Dhaka','1','0','-1')
    insert into city_statuses
  values('Dhaka','1','0','-1')
    insert into city_statuses
  values('Dhaka','1','0','-1')
    insert into city_statuses
  values('Dhaka','1','0','-1')
    insert into city_statuses
  values('Dhaka','-1','1','1')
    insert into city_statuses
  values('Dhaka','1','0','-1')
    insert into city_statuses
  values('Dhaka','1','0','-1')

 insert into city_statuses
  values('BARISHAL','1','0','-1')
  
  
  
  insert into city_statuses
  values('BARISHAL','1','0','-1')
    insert into city_statuses
  values('BARISHAL','1','0','-1')
    insert into city_statuses
  values('BARISHAL','1','0','-1')
    insert into city_statuses
  values('BARISHAL','1','0','-1')
    insert into city_statuses
  values('BARISHAL','1','0','-1')
    insert into city_statuses
  values('BARISHAL','1','0','-1')
    insert into city_statuses
  values('BARISHAL','-1','1','1')
    insert into city_statuses
  values('BARISHAL','1','0','-1')
    insert into city_statuses
  values('BARISHAL','1','0','-1')
  
  For the icons and modifiers, I used:

• Success:  fa-check-circle-o u-success-text

• Warning:  fa-exclamation-triangle u-warning-text

• Error:  fa-exception u-danger-text

  SQL
  select city_name,
       status1,
       status2,
       status3,
       case status1 
           when '1' then 'fa-check-circle-o u-success-text'
           when '0' then 'fa-exclamation-triangle u-warning-text'
           when '-1' then 'fa-exception u-danger-text'
       end status1_icon,
       case status1 
           when '1'  then 'OK'
           when '0'  then 'Warning'
           when '-1' then 'Danger'
       end status1_description,        
       case status2 
           when '1'  then 'fa-check-circle-o u-success-text'
           when '0'  then 'fa-exclamation-triangle u-warning-text'
           when '-1' then 'fa-exception u-danger-text'
       end status2_icon,
       case status2 
           when '1'  then 'OK'
           when '0'  then 'Warning'
           when '-1' then 'Danger'
       end status2_description,                
       case status3 
           when '1'  then 'fa-check-circle-o u-success-text'
           when '0'  then 'fa-exclamation-triangle u-warning-text'
           when '-1' then 'fa-exception u-danger-text'
       end status3_icon,
       case status3 
           when '1'  then 'OK'
           when '0'  then 'Warning'
           when '-1' then 'Danger'
       end status3_description
 from city_statuses
  

Then, for the columns STATUS1, STATUS2 and STATUS3, in Page Designer I changed the property HTML Expression to:

<span class="fa #STATUS1_ICON#" title="#STATUS1_DESCRIPTION#"></span>

APEX Workspace Activity Logs

 select apex_user as who

  ,application_id||':'||page_id as what
  ,view_date as when
  ,elapsed_time as how_long
  ,page_view_type as why
  ,request_value as more_why
from apex_workspace_activity_log
where application_id >= 4000
and apex_user != 'nobody'

Get records with max value for each group of grouped SQL

SELECT cf$_order_no order_no,
cf$_recovery_officer recovery_officer,
cf$_recovery_officer_name recovery_officer_name,
cf$_recovery_zone recovery_zone_code,
cf$_recovery_zone_name recovery_zone_name
FROM c_recovery_officer_history_clv t1
INNER JOIN
(
SELECT cf$_order_no order_no, MAX(ROWID) AS MAX_ROWID
FROM c_recovery_officer_history_clv
GROUP BY cf$_order_no
) t2
ON cf$_order_no = t2.order_no AND t1.ROWID = t2.MAX_ROWID

if an Item Value Changed then check message in Oracle Apex

-- page on page load Daynamic Action 

if (apex.item("P10_SAL").isChanged()) {

apex.message.alert('You modified the salary, please verify again!');

}

 

Set/Convert an Item Value to Upper Case & Lower Case in Oracle Apex

Column Property 

--Custom Attributes property:

--UpperCase

style="text-transform: uppercase;" onKeyUp="this.value=this.value.toUpperCase();"

--LowerCase

style="text-transform: lowercase;" onKeyUp="this.value=this.value.toLowerCase();"

Highlight Item on Focus in Oracle Apex

1) change the item background color using jquery in oracle apex

  --create page execute when page loads section in daynamic action

  $('input').on('focus', function() {

$("#"+this.id).css("background-color", "yellow");

}).on('blur', function() {

$("#"+this.id).css("background-color", "white");

});

2) highlight page item using css

-- Page Inline CSS

input[type=text], textarea {

-webkit-transition: all 0.30s ease-in-out;

-moz-transition: all 0.30s ease-in-out;

-ms-transition: all 0.30s ease-in-out;

-o-transition: all 0.30s ease-in-out;

outline: none;

padding: 3px 0px 3px 3px;

margin: 5px 1px 3px 0px;

border: 1px solid #DDDDDD;

background-color: white;

}

input[type=text]:focus, textarea:focus {

box-shadow: 0 0 5px rgba(81, 203, 238, 1);

padding: 3px 0px 3px 3px;

margin: 5px 1px 3px 0px;

border: 1px solid rgba(81, 203, 238, 1);

background-color: yellow;

}

ROUND function

SELECT ROUND (SYSDATE, 'YEAR') "As of day" FROM DUAL

/

1/1/2022

SELECT ROUND (TO_DATE ('12-mar-2021'), 'YEAR') "Current Year" FROM DUAL

/

1/1/2021

SELECT ROUND (TO_DATE ('12-aug-2021'), 'YEAR') "New Year" FROM DUAL

1/1/2022

First and Last Day of the year

SELECT TRUNC (SYSDATE, 'YEAR') "First Day" FROM DUAL;

/

1/1/2021

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Last Day" FROM DUAL;

/

12/31/2021

First and last Day of Month

SELECT TRUNC (SYSDATE) - (TO_NUMBER (TO_CHAR (SYSDATE, 'DD')) - 1) FIRST_DAY FROM DUAL;

/

10/1/2021

SELECT ADD_MONTHS ( TRUNC (SYSDATE) - (TO_NUMBER (TO_CHAR (SYSDATE, 'DD')) - 1), 1) - 1 LAST_DAY FROM DUAL;

/

10/31/2021

SELECT  ADD_MONTHS ( TRUNC (SYSDATE) - (TO_NUMBER (TO_CHAR (SYSDATE, 'DD')) - 1), 1) - 1 LAST_DAY,

        TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1)))+1, TRUNC(LAST_DAY(SYSDATE)) ,

        TRUNC(LAST_DAY(SYSDATE)) LAST_D

FROM DUAL;

10/31/2021    10/1/2021    10/31/2021    10/31/2021

ORACLE SQL | Find Number between two values, odd, even, number is odd or even

select '&START_NUM' + level - 1 "Number"

    from dual

  connect by level <= '&END_NUM' - '&START_NUM' + 1

ORACLE APEX | Apex URL Syntax

Anyhow, this is an Oracle related blog, and I wanted to share an idea to remember the Apex URL syntax of:

f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly

Most of the time I remember the order fairly well off the top of my head, though sometimes 

I need to double check; and sometimes within the mix of a bunch of colons it's hard to work

out what is what. Perhaps if you're reading a sentence in your head as you move the cursor

between colons

f?p=MY_APP:HOME::::::

Details

All  -->   Application

Programmers   -->   Page

Should   -->   Session

Read   -->    Request

Delightfully   -->   Debug

Creative   -->   Cache

Information   -->   Items

Values   -->   Printer Friendly

Apexing