- Script Name remainder_issue_followup
- Description Shows difference when round() and trunc() are used on the m/n results.
- Category SQL General / Functions
- Contributor David Fitzjarrell
- Created Monday June 13, 2016

- Statement 1
`set serveroutput on size 1000000 echo on`

Unsupported Command - Statement 2
`BEGIN DBMS_OUTPUT.put_line (MOD (15, 2)); DBMS_OUTPUT.put_line (REMAINDER (15, 2)); DBMS_OUTPUT.put_line (MOD (15, 3)); DBMS_OUTPUT.put_line (REMAINDER (15, 3)); DBMS_OUTPUT.put_line (MOD (15, 4)); DBMS_OUTPUT.put_line (REMAINDER (15, 4)); DBMS_OUTPUT.put_line (MOD (15, 5)); DBMS_OUTPUT.put_line (REMAINDER (15, 5)); DBMS_OUTPUT.put_line (MOD (15, 6)); DBMS_OUTPUT.put_line (REMAINDER (15, 6)); DBMS_OUTPUT.put_line (MOD (15, 7)); DBMS_OUTPUT.put_line (REMAINDER (15, 7)); DBMS_OUTPUT.put_line (MOD (15, 8)); DBMS_OUTPUT.put_line (REMAINDER (15, 8)); END;`

1

-1

0

0

3

-1

0

0

3

3

1

1

7

-1

- Statement 3
`create or replace function remainder_func(p_num1 in number, p_num2 in number) return number is v_rmdr number; v_x number:=1; begin v_x := trunc(p_num1/p_num2, 0); v_rmdr := p_num1 - (v_x * p_num2); return(v_rmdr); end;`

Function created. - Statement 4
`BEGIN DBMS_OUTPUT.put_line (MOD (15, 2)); DBMS_OUTPUT.put_line (REMAINDER_func (15, 2)); DBMS_OUTPUT.put_line (MOD (15, 3)); DBMS_OUTPUT.put_line (REMAINDER_func (15, 3)); DBMS_OUTPUT.put_line (MOD (15, 4)); DBMS_OUTPUT.put_line (REMAINDER_func (15, 4)); DBMS_OUTPUT.put_line (MOD (15, 5)); DBMS_OUTPUT.put_line (REMAINDER_func (15, 5)); DBMS_OUTPUT.put_line (MOD (15, 6)); DBMS_OUTPUT.put_line (REMAINDER_func (15, 6)); DBMS_OUTPUT.put_line (MOD (15, 7)); DBMS_OUTPUT.put_line (REMAINDER_func (15, 7)); DBMS_OUTPUT.put_line (MOD (15, 8)); DBMS_OUTPUT.put_line (REMAINDER_func (15, 8)); END;`

1

1

0

0

3

3

0

0

3

3

1

1

7

7

- Statement 5
`create or replace procedure remainder_test(p_num1 in number, p_num2 in number) is v_t_rmdr number; v_r_rmdr number; v_tx number:=1; v_rx number:=1; begin v_tx := trunc(p_num1/p_num2, 0); v_rx := round(p_num1/p_num2, 0); v_t_rmdr := p_num1 - (v_tx * p_num2); v_r_rmdr := p_num1 - (v_rx * p_num2); dbms_output.put_line('Rounded: '||v_rx||' (n*X): '||v_rx*p_num2||' Remainder: '||v_r_rmdr); dbms_output.put_line('Truncated: '||v_tx||' (n*X): '||v_tx*p_num2||' Remainder: '||v_t_rmdr); end;`

Procedure created. - Statement 6
`BEGIN REMAINDER_test (15, 2); REMAINDER_test (15, 3); REMAINDER_test (15, 4); REMAINDER_test (15, 5); REMAINDER_test (15, 6); REMAINDER_test (15, 7); REMAINDER_test (15, 8); END;`

Rounded: 8 (n*X): 16 Remainder: -1

Truncated: 7 (n*X): 14 Remainder: 1

Rounded: 5 (n*X): 15 Remainder: 0

Truncated: 5 (n*X): 15 Remainder: 0

Rounded: 4 (n*X): 16 Remainder: -1

Truncated: 3 (n*X): 12 Remainder: 3

Rounded: 3 (n*X): 15 Remainder: 0

Truncated: 3 (n*X): 15 Remainder: 0

Rounded: 3 (n*X): 18 Remainder: -3

Truncated: 2 (n*X): 12 Remainder: 3

Rounded: 2 (n*X): 14 Remainder: 1

Truncated: 2 (n*X): 14 Remainder: 1

Rounded: 2 (n*X): 16 Remainder: -1

Truncated: 1 (n*X): 8 Remainder: 7

- Statement 7
`set echo off`

Unsupported Command - Statement 8
`set serveroutput on size 1000000 echo on`

Unsupported Command - Statement 9
`set echo off`

Unsupported Command - Statement 10
`set serveroutput on size 1000000 echo on`

Unsupported Command - Statement 11
`BEGIN DBMS_OUTPUT.put_line (MOD (15, 2)); DBMS_OUTPUT.put_line (REMAINDER (15, 2)); DBMS_OUTPUT.put_line (MOD (15, 3)); DBMS_OUTPUT.put_line (REMAINDER (15, 3)); DBMS_OUTPUT.put_line (MOD (15, 4)); DBMS_OUTPUT.put_line (REMAINDER (15, 4)); DBMS_OUTPUT.put_line (MOD (15, 5)); DBMS_OUTPUT.put_line (REMAINDER (15, 5)); DBMS_OUTPUT.put_line (MOD (15, 6)); DBMS_OUTPUT.put_line (REMAINDER (15, 6)); DBMS_OUTPUT.put_line (MOD (15, 7)); DBMS_OUTPUT.put_line (REMAINDER (15, 7)); DBMS_OUTPUT.put_line (MOD (15, 8)); DBMS_OUTPUT.put_line (REMAINDER (15, 8)); END;`

1

-1

0

0

3

-1

0

0

3

3

1

1

7

-1

- Statement 12
`create or replace function remainder_func(p_num1 in number, p_num2 in number) return number is v_rmdr number; v_x number:=1; begin v_x := trunc(p_num1/p_num2, 0); v_rmdr := p_num1 - (v_x * p_num2); return(v_rmdr); end;`

Function created. - Statement 13
`BEGIN DBMS_OUTPUT.put_line (MOD (15, 2)); DBMS_OUTPUT.put_line (REMAINDER_func (15, 2)); DBMS_OUTPUT.put_line (MOD (15, 3)); DBMS_OUTPUT.put_line (REMAINDER_func (15, 3)); DBMS_OUTPUT.put_line (MOD (15, 4)); DBMS_OUTPUT.put_line (REMAINDER_func (15, 4)); DBMS_OUTPUT.put_line (MOD (15, 5)); DBMS_OUTPUT.put_line (REMAINDER_func (15, 5)); DBMS_OUTPUT.put_line (MOD (15, 6)); DBMS_OUTPUT.put_line (REMAINDER_func (15, 6)); DBMS_OUTPUT.put_line (MOD (15, 7)); DBMS_OUTPUT.put_line (REMAINDER_func (15, 7)); DBMS_OUTPUT.put_line (MOD (15, 8)); DBMS_OUTPUT.put_line (REMAINDER_func (15, 8)); END;`

1

1

0

0

3

3

0

0

3

3

1

1

7

7

- Statement 14
`create or replace procedure remainder_test(p_num1 in number, p_num2 in number) is v_t_rmdr number; v_r_rmdr number; v_tx number:=1; v_rx number:=1; begin v_tx := trunc(p_num1/p_num2, 0); v_rx := round(p_num1/p_num2, 0); v_t_rmdr := p_num1 - (v_tx * p_num2); v_r_rmdr := p_num1 - (v_rx * p_num2); dbms_output.put_line('m/n Rounded: '||v_rx||' (n*X): '||v_rx*p_num2||' Remainder: '||v_r_rmdr); dbms_output.put_line('m/n Truncated: '||v_tx||' (n*X): '||v_tx*p_num2||' Remainder: '||v_t_rmdr); end;`

Procedure created. - Statement 15
`BEGIN REMAINDER_test (15, 2); REMAINDER_test (15, 3); REMAINDER_test (15, 4); REMAINDER_test (15, 5); REMAINDER_test (15, 6); REMAINDER_test (15, 7); REMAINDER_test (15, 8); END;`

m/n Rounded: 8 (n*X): 16 Remainder: -1

m/n Truncated: 7 (n*X): 14 Remainder: 1

m/n Rounded: 5 (n*X): 15 Remainder: 0

m/n Truncated: 5 (n*X): 15 Remainder: 0

m/n Rounded: 4 (n*X): 16 Remainder: -1

m/n Truncated: 3 (n*X): 12 Remainder: 3

m/n Rounded: 3 (n*X): 15 Remainder: 0

m/n Truncated: 3 (n*X): 15 Remainder: 0

m/n Rounded: 3 (n*X): 18 Remainder: -3

m/n Truncated: 2 (n*X): 12 Remainder: 3

m/n Rounded: 2 (n*X): 14 Remainder: 1

m/n Truncated: 2 (n*X): 14 Remainder: 1

m/n Rounded: 2 (n*X): 16 Remainder: -1

m/n Truncated: 1 (n*X): 8 Remainder: 7

- Statement 16
`set echo off`

Unsupported Command