gla
asked on
decode range date field
I'm trying to DECODE a date field to create date range. The date field in DD/MM/YYYY HH:MM:SS format. I want to use only Decode statement like the following
DECODE(ORDER_DATE,< 01/01/2002, 0, between 01/01/2003 and 31/12/2003,1,between 01/01/2004 and 31/3/2004, 3,
between 01/04/2004 and 30/06/2004, 4,5)
DECODE(ORDER_DATE,< 01/01/2002, 0, between 01/01/2003 and 31/12/2003,1,between 01/01/2004 and 31/3/2004, 3,
between 01/04/2004 and 30/06/2004, 4,5)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Actually, this will still work on Oracle 8i, but only in SQL, not in PlSQL.
ASKER
I can use
select
CASE
statement, I have a small problem with joins, my statement likes
SELECT
AAU.COMPANYID,
AAU.AUDITORID,
AAU.CUSTOMERID,
AAU.ID,
AAU.KINDCONTROLID,
A.YEARID,
A.FL,
AAU.FSHOURS,
AAU.FSACCOUNT
FROM CUSTOMER_TRANSACTIONS
--DECODE(TO_DATE(SYSDATE, 'RRRR') - TO_DATE(CTT.TRNDATE, 'RRRR'), 2, 'palaio', 'aaaaa'),
-- 'Ï', DECODE(SIGN(( NVL(DECODE (RK, 1 , X, 0), 0) - NVL(DECODE (RK, 1 , P, 0), 0))), 1, 'Á', 0, 'Åêêáèáñßóôçêå', -1, 'Åêêáèáñßóôçêå', '1 !! Þ null'),
(SELECT DECODE(PDC.VALUE02, '×', SUM(CT.TOTV)) X
FROM CUSTOMER_TRANSACTIONS CT,
PARAMETERSDETAILS PDC
WHERE AAU.COMPANYID = CT.COMPANYID
AND AAU.CUSTOMERID = CT.CUSTOMERID
AND AAU.ID = CT.APPOINTOFCAID
AND CT.TRNDATE >= TO_DATE('01/01/2002', 'DD/MM/RRRR')
AND CT.ACCKINDID = PDC.VALUE(+)
AND PDC.ID(+) = 'ÊÉÍÇÓÇÓ ÃÅÍÉÊÇÓ ËÏÃÉÓÔÉÊÇÓ'
AND PDC.VALUE02 = '×'
AND PDC.VALUE = 68
-- AND CTT.TRNDATE = CT.TRNDATE
GROUP BY AAU.COMPANYID, AAU.CUSTOMERID, AAU.ID, PDC.VALUE02) XTIM,
(SELECT DECODE(PDC.VALUE02, 'Ð', SUM(CT.TOTV)) P
FROM CUSTOMER_TRANSACTIONS CT,
PARAMETERSDETAILS PDC
WHERE AAU.COMPANYID = CT.COMPANYID
AND AAU.CUSTOMERID = CT.CUSTOMERID
AND AAU.ID = CT.APPOINTOFCAID
AND CT.TRNDATE >= TO_DATE('01/01/2002', 'DD/MM/RRRR')
AND CT.ACCKINDID = PDC.VALUE
AND PDC.ID = 'ÊÉÍÇÓÇÓ ÃÅÍÉÊÇÓ ËÏÃÉÓÔÉÊÇÓ'
AND PDC.VALUE02 = 'Ð'
AND PDC.VALUE = 62
GROUP BY AAU.COMPANYID, AAU.CUSTOMERID, AAU.ID, PDC.VALUE02) PTIM,
(SELECT DECODE(PDC.VALUE02, '×', SUM(CT.TOTV)) X
FROM CUSTOMER_TRANSACTIONS CT,
PARAMETERSDETAILS PDC
WHERE CT.COMPANYID = AAU.COMPANYID
AND CT.CUSTOMERID = AAU.CUSTOMERID
AND CT.APPOINTOFCAID = AAU.ID
AND CT.TRNDATE >= TO_DATE('01/01/2002', 'DD/MM/RRRR')
AND CT.ACCKINDID = PDC.VALUE(+)
AND PDC.ID(+) = 'ÊÉÍÇÓÇÓ ÃÅÍÉÊÇÓ ËÏÃÉÓÔÉÊÇÓ'
AND PDC.VALUE02 = '×'
AND PDC.VALUE = 30
AND CT.TRNENDDATE >= SYSDATE + 7
GROUP BY AAU.COMPANYID, AAU.CUSTOMERID, AAU.ID, PDC.VALUE02) XEPT,
(SELECT DECODE(PDC.VALUE02, 'Ð', SUM(CT.TOTV)) P
FROM CUSTOMER_TRANSACTIONS CT,
PARAMETERSDETAILS PDC
WHERE AAU.COMPANYID = CT.COMPANYID
AND AAU.CUSTOMERID = CT.CUSTOMERID
AND AAU.ID = CT.APPOINTOFCAID
AND CT.TRNDATE >= TO_DATE('01/01/2002', 'DD/MM/RRRR')
AND CT.ACCKINDID = PDC.VALUE(+)
AND PDC.ID(+) = 'ÊÉÍÇÓÇÓ ÃÅÍÉÊÇÓ ËÏÃÉÓÔÉÊÇÓ'
AND PDC.VALUE02 = 'Ð'
AND (PDC.VALUE = 22 OR PDC.VALUE = 23)
AND CT.TRNENDDATE >= SYSDATE + 7
GROUP BY AAU.COMPANYID, AAU.CUSTOMERID, AAU.ID, PDC.VALUE02) PEPT,
0 KEPT,
(SELECT DECODE(PDC.VALUE02, '×', SUM(CT.TOTV)) X
FROM CUSTOMER_TRANSACTIONS CT,
PARAMETERSDETAILS PDC
WHERE AAU.COMPANYID = CT.COMPANYID
AND AAU.CUSTOMERID = CT.CUSTOMERID
AND AAU.ID = CT.APPOINTOFCAID
AND CT.TRNDATE >= TO_DATE('01/01/2002', 'DD/MM/RRRR')
AND CT.ACCKINDID = PDC.VALUE(+)
AND PDC.ID(+) = 'ÊÉÍÇÓÇÓ ÃÅÍÉÊÇÓ ËÏÃÉÓÔÉÊÇÓ'
AND PDC.VALUE02 = '×'
GROUP BY AAU.COMPANYID, AAU.CUSTOMERID, AAU.ID, PDC.VALUE02) X,
(SELECT DECODE(PDC.VALUE02, 'Ð', SUM(CT.TOTV)) P
FROM CUSTOMER_TRANSACTIONS CT,
PARAMETERSDETAILS PDC
WHERE AAU.COMPANYID = CT.COMPANYID
AND AAU.CUSTOMERID = CT.CUSTOMERID
AND AAU.ID = CT.APPOINTOFCAID
AND CT.TRNDATE >= TO_DATE('01/01/2002', 'DD/MM/RRRR')
AND CT.ACCKINDID = PDC.VALUE(+)
AND PDC.ID(+) = 'ÊÉÍÇÓÇÓ ÃÅÍÉÊÇÓ ËÏÃÉÓÔÉÊÇÓ'
AND PDC.VALUE02 = 'Ð'
GROUP BY AAU.COMPANYID, AAU.CUSTOMERID, AAU.ID, PDC.VALUE02) P,
RANK() OVER ( PARTITION BY AAU.COMPANYID, AAU.AUDITORID, AAU.CUSTOMERID, AAU.ID
ORDER BY AAU.COMPANYID, AAU.AUDITORID, AAU.CUSTOMERID, AAU.ID, AAU.KINDCONTROLID) AS RK
FROM APPOINTSOFCAS_AUDITORS AAU ,
APPOINTSOFCAS A,
CUSTOMER_TRANSACTIONS CTT
WHERE AAU.COMPANYID = A.COMPANYID
AND AAU.COMPANYID = CTT.COMPANYID
AND AAU.CUSTOMERID = CTT.CUSTOMERID
AND AAU.ID = CTT.APPOINTOFCAID
AND AAU.CUSTOMERID = A.CUSTOMERID
AND AAU.ID = A.ID
AND AAU.KINDCONTROLID = A.KINDCONTROLID
AND AAU.AUDITORID = 328
ORDER BY AAU.COMPANYID, AAU.AUDITORID, AAU.CUSTOMERID, AAU.ID, CTT.TRNDATE
any other way?
select
CASE
statement, I have a small problem with joins, my statement likes
SELECT
AAU.COMPANYID,
AAU.AUDITORID,
AAU.CUSTOMERID,
AAU.ID,
AAU.KINDCONTROLID,
A.YEARID,
A.FL,
AAU.FSHOURS,
AAU.FSACCOUNT
FROM CUSTOMER_TRANSACTIONS
--DECODE(TO_DATE(SYSDATE, 'RRRR') - TO_DATE(CTT.TRNDATE, 'RRRR'), 2, 'palaio', 'aaaaa'),
-- 'Ï', DECODE(SIGN(( NVL(DECODE (RK, 1 , X, 0), 0) - NVL(DECODE (RK, 1 , P, 0), 0))), 1, 'Á', 0, 'Åêêáèáñßóôçêå', -1, 'Åêêáèáñßóôçêå', '1 !! Þ null'),
(SELECT DECODE(PDC.VALUE02, '×', SUM(CT.TOTV)) X
FROM CUSTOMER_TRANSACTIONS CT,
PARAMETERSDETAILS PDC
WHERE AAU.COMPANYID = CT.COMPANYID
AND AAU.CUSTOMERID = CT.CUSTOMERID
AND AAU.ID = CT.APPOINTOFCAID
AND CT.TRNDATE >= TO_DATE('01/01/2002', 'DD/MM/RRRR')
AND CT.ACCKINDID = PDC.VALUE(+)
AND PDC.ID(+) = 'ÊÉÍÇÓÇÓ ÃÅÍÉÊÇÓ ËÏÃÉÓÔÉÊÇÓ'
AND PDC.VALUE02 = '×'
AND PDC.VALUE = 68
-- AND CTT.TRNDATE = CT.TRNDATE
GROUP BY AAU.COMPANYID, AAU.CUSTOMERID, AAU.ID, PDC.VALUE02) XTIM,
(SELECT DECODE(PDC.VALUE02, 'Ð', SUM(CT.TOTV)) P
FROM CUSTOMER_TRANSACTIONS CT,
PARAMETERSDETAILS PDC
WHERE AAU.COMPANYID = CT.COMPANYID
AND AAU.CUSTOMERID = CT.CUSTOMERID
AND AAU.ID = CT.APPOINTOFCAID
AND CT.TRNDATE >= TO_DATE('01/01/2002', 'DD/MM/RRRR')
AND CT.ACCKINDID = PDC.VALUE
AND PDC.ID = 'ÊÉÍÇÓÇÓ ÃÅÍÉÊÇÓ ËÏÃÉÓÔÉÊÇÓ'
AND PDC.VALUE02 = 'Ð'
AND PDC.VALUE = 62
GROUP BY AAU.COMPANYID, AAU.CUSTOMERID, AAU.ID, PDC.VALUE02) PTIM,
(SELECT DECODE(PDC.VALUE02, '×', SUM(CT.TOTV)) X
FROM CUSTOMER_TRANSACTIONS CT,
PARAMETERSDETAILS PDC
WHERE CT.COMPANYID = AAU.COMPANYID
AND CT.CUSTOMERID = AAU.CUSTOMERID
AND CT.APPOINTOFCAID = AAU.ID
AND CT.TRNDATE >= TO_DATE('01/01/2002', 'DD/MM/RRRR')
AND CT.ACCKINDID = PDC.VALUE(+)
AND PDC.ID(+) = 'ÊÉÍÇÓÇÓ ÃÅÍÉÊÇÓ ËÏÃÉÓÔÉÊÇÓ'
AND PDC.VALUE02 = '×'
AND PDC.VALUE = 30
AND CT.TRNENDDATE >= SYSDATE + 7
GROUP BY AAU.COMPANYID, AAU.CUSTOMERID, AAU.ID, PDC.VALUE02) XEPT,
(SELECT DECODE(PDC.VALUE02, 'Ð', SUM(CT.TOTV)) P
FROM CUSTOMER_TRANSACTIONS CT,
PARAMETERSDETAILS PDC
WHERE AAU.COMPANYID = CT.COMPANYID
AND AAU.CUSTOMERID = CT.CUSTOMERID
AND AAU.ID = CT.APPOINTOFCAID
AND CT.TRNDATE >= TO_DATE('01/01/2002', 'DD/MM/RRRR')
AND CT.ACCKINDID = PDC.VALUE(+)
AND PDC.ID(+) = 'ÊÉÍÇÓÇÓ ÃÅÍÉÊÇÓ ËÏÃÉÓÔÉÊÇÓ'
AND PDC.VALUE02 = 'Ð'
AND (PDC.VALUE = 22 OR PDC.VALUE = 23)
AND CT.TRNENDDATE >= SYSDATE + 7
GROUP BY AAU.COMPANYID, AAU.CUSTOMERID, AAU.ID, PDC.VALUE02) PEPT,
0 KEPT,
(SELECT DECODE(PDC.VALUE02, '×', SUM(CT.TOTV)) X
FROM CUSTOMER_TRANSACTIONS CT,
PARAMETERSDETAILS PDC
WHERE AAU.COMPANYID = CT.COMPANYID
AND AAU.CUSTOMERID = CT.CUSTOMERID
AND AAU.ID = CT.APPOINTOFCAID
AND CT.TRNDATE >= TO_DATE('01/01/2002', 'DD/MM/RRRR')
AND CT.ACCKINDID = PDC.VALUE(+)
AND PDC.ID(+) = 'ÊÉÍÇÓÇÓ ÃÅÍÉÊÇÓ ËÏÃÉÓÔÉÊÇÓ'
AND PDC.VALUE02 = '×'
GROUP BY AAU.COMPANYID, AAU.CUSTOMERID, AAU.ID, PDC.VALUE02) X,
(SELECT DECODE(PDC.VALUE02, 'Ð', SUM(CT.TOTV)) P
FROM CUSTOMER_TRANSACTIONS CT,
PARAMETERSDETAILS PDC
WHERE AAU.COMPANYID = CT.COMPANYID
AND AAU.CUSTOMERID = CT.CUSTOMERID
AND AAU.ID = CT.APPOINTOFCAID
AND CT.TRNDATE >= TO_DATE('01/01/2002', 'DD/MM/RRRR')
AND CT.ACCKINDID = PDC.VALUE(+)
AND PDC.ID(+) = 'ÊÉÍÇÓÇÓ ÃÅÍÉÊÇÓ ËÏÃÉÓÔÉÊÇÓ'
AND PDC.VALUE02 = 'Ð'
GROUP BY AAU.COMPANYID, AAU.CUSTOMERID, AAU.ID, PDC.VALUE02) P,
RANK() OVER ( PARTITION BY AAU.COMPANYID, AAU.AUDITORID, AAU.CUSTOMERID, AAU.ID
ORDER BY AAU.COMPANYID, AAU.AUDITORID, AAU.CUSTOMERID, AAU.ID, AAU.KINDCONTROLID) AS RK
FROM APPOINTSOFCAS_AUDITORS AAU ,
APPOINTSOFCAS A,
CUSTOMER_TRANSACTIONS CTT
WHERE AAU.COMPANYID = A.COMPANYID
AND AAU.COMPANYID = CTT.COMPANYID
AND AAU.CUSTOMERID = CTT.CUSTOMERID
AND AAU.ID = CTT.APPOINTOFCAID
AND AAU.CUSTOMERID = A.CUSTOMERID
AND AAU.ID = A.ID
AND AAU.KINDCONTROLID = A.KINDCONTROLID
AND AAU.AUDITORID = 328
ORDER BY AAU.COMPANYID, AAU.AUDITORID, AAU.CUSTOMERID, AAU.ID, CTT.TRNDATE
any other way?
Im not sure I know what you meant there. Why cant you use a case statement? CASE is synonymous with DECODE only it has a few extra features like comparing doing exactly what you asked.
Your server obviously can take a CASE statement as you are using RANK().
Your server obviously can take a CASE statement as you are using RANK().
Try this decode:
SQL> SELECT * FROM TAB3;
ORDER_DAT
---------
01-JAN-02
31-DEC-01
05-AUG-03
05-JAN-04
05-MAY-04
15-NOV-04
6 rows selected.
Elapsed: 00:00:00.00
SQL> SELECT ORDER_DATE, DECODE(SIGN(ORDER_DATE-TO_
2 DECODE(SIGN(ORDER_DATE-TO_
3 DECODE(SIGN(ORDER_DATE-TO_
4 DECODE(SIGN(ORDER_DATE-TO_
5 "DECODE"
6 FROM TAB3
7 /
ORDER_DAT DECODE
--------- ----------
01-JAN-02 5
31-DEC-01 0
05-AUG-03 1
05-JAN-04 3
05-MAY-04 4
15-NOV-04 5
6 rows selected.
Elapsed: 00:00:00.00
Hope this helps!
SQL> SELECT * FROM TAB3;
ORDER_DAT
---------
01-JAN-02
31-DEC-01
05-AUG-03
05-JAN-04
05-MAY-04
15-NOV-04
6 rows selected.
Elapsed: 00:00:00.00
SQL> SELECT ORDER_DATE, DECODE(SIGN(ORDER_DATE-TO_
2 DECODE(SIGN(ORDER_DATE-TO_
3 DECODE(SIGN(ORDER_DATE-TO_
4 DECODE(SIGN(ORDER_DATE-TO_
5 "DECODE"
6 FROM TAB3
7 /
ORDER_DAT DECODE
--------- ----------
01-JAN-02 5
31-DEC-01 0
05-AUG-03 1
05-JAN-04 3
05-MAY-04 4
15-NOV-04 5
6 rows selected.
Elapsed: 00:00:00.00
Hope this helps!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.