On Monday, February 24, 2014 7:59:00 AM UTC-7, Sagar Thakkar wrote:
>
> HI can some explain the case function below specially the DECODE (SIGN
> (TO_DATE (:P_CHK_RECD_TO, 'DD/MM/RRRR') -
> (TO_DATE('10/02/2014','DD/MM/RRRR'))), 1, 'A') = 'A'
>
> WHERE CASE
> WHEN Y.DOC_STATUS = 'R' AND DECODE (SIGN (TO_DATE
> (:P_CHK_RECD_TO, 'DD/MM/RRRR') - (TO_DATE('10/02/2014','DD/MM/RRRR'))), 1,
> 'A') = 'A'
> THEN
> 'A'
> WHEN Y.DOC_STATUS = 'A'
> THEN
> 'A'
> ELSE
> 'R'
> END = 'A'
>
>
>>
Michael is correct, that does not belong in a WHERE clause. But, that
doesn't explain the DECODE statement, so let's take it apart.
The SIGN function returns one of three values: 1,0,-1. Which value it
returns depends on whether the value is positive (1), zero (0) or negative
(-1):
SQL> select SIGN (TO_DATE ('&pdate', 'DD/MM/RRRR') -
(TO_DATE('10/02/2014','DD/MM/RRRR'))) from dual;
Enter value for pdate: 01/02/2012
SIGN(TO_DATE('01/02/2012','DD/MM/RRRR')-(TO_DATE('10/02/2014','DD/MM/RRRR')))
-----------------------------------------------------------------------------
-1
SQL> /
Enter value for pdate: 10/02/2014
SIGN(TO_DATE('10/02/2014','DD/MM/RRRR')-(TO_DATE('10/02/2014','DD/MM/RRRR')))
-----------------------------------------------------------------------------
0
SQL> /
Enter value for pdate: 10/04/2014
SIGN(TO_DATE('10/04/2014','DD/MM/RRRR')-(TO_DATE('10/02/2014','DD/MM/RRRR')))
-----------------------------------------------------------------------------
1
SQL>
Unfortunately the DECODE only handles the positive result:
SQL> select DECODE (SIGN (TO_DATE ('&pdate', 'DD/MM/RRRR') -
(TO_DATE('10/02/2014','DD/MM/RRRR'))), 1, 'A') from dual;
Enter value for pdate: 01/02/2012
D
-
SQL> /
Enter value for pdate: 10/02/2014
D
-
SQL> /
Enter value for pdate: 10/04/2014
D
-
A
SQL>
Not really a good strategy. A default value should also be specified to
handle the 'unhandled' cases:
SQL> select DECODE (SIGN (TO_DATE ('&pdate', 'DD/MM/RRRR') -
(TO_DATE('10/02/2014','DD/MM/RRRR'))), 1, 'A', 'X') from dual;
Enter value for pdate: 01/02/2012
D
-
X
SQL> /
Enter value for pdate: 10/02/2014
D
-
X
SQL> /
Enter value for pdate: 10/04/2014
D
-
A
SQL>
Ideally all three conditions should be explicitly handled with the default
covering the unfortunate event when none of the expected values are
returned:
SQL> select DECODE (SIGN (TO_DATE ('&pdate', 'DD/MM/RRRR') -
(TO_DATE('10/02/2014','DD/MM/RRRR'))), 1, 'A', 0, 'B', -1, 'X', 'Z') from
dual;
Enter value for pdate: 01/02/2013
D
-
X
SQL> /
Enter value for pdate: 10/02/2014
D
-
B
SQL> /
Enter value for pdate: 10/04/2014
D
-
A
SQL>
Even using dates in the BC range won't return 'Z':
SQL> select DECODE (SIGN (TO_DATE ('&pdate', 'DD/MM/SYYYY') -
(TO_DATE('10/02/2014','DD/MM/RRRR'))), 1, 'A', 0, 'B', -1, 'X', 'Z') from
dual
2> /
Enter value for pdate: 01/01/-4712
D
-
X
SQL>
However all cases are now considered which is MUCH better programming
practice.
Possibly you understand the DECODE and SIGN functions now.
David Fitzjarrell
--
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
---
You received this message because you are subscribed to the Google Groups
"Oracle PL/SQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to oracle-plsql+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
