rbrooker
Member³
Hi,
oracle discoverer has typing fingers on steroids and has produced a sql that cannot be beautified.
oracle discoverer has typing fingers on steroids and has produced a sql that cannot be beautified.
Code:
SELECT /*+ NOREWRITE */
c_12 AS c_12,
c_9 AS c_9,
c_16 AS c_16,
c_1 AS c_1,
c_11 AS c_11,
c_5 AS c_5,
e100289 AS e100289,
e100291 AS e100291,
e100293 AS e100293,
e108666 AS e108666,
e114893 AS e114893,
e198642 AS e198642,
e199187 AS e199187,
e199610 AS e199610,
e199710 AS e199710,
e199745 AS e199745,
e199778 AS e199778,
e199785 AS e199785,
e199834 AS e199834,
e199883 AS e199883,
e200165 AS e200165,
e200232 AS e200232,
e200539 AS e200539,
e200550 AS e200550,
e200657 AS e200657,
e290550 AS e290550,
e290551 AS e290551,
e290552 AS e290552,
e300264 AS e300264,
e300265 AS e300265,
e300266 AS e300266,
e300267 AS e300267,
e300293 AS e300293,
e300294 AS e300294,
e342998 AS e342998,
e343011 AS e343011,
e367060 AS e367060,
e380798 AS e380798,
e380800 AS e380800,
e508501 AS e508501,
e655563 AS e655563,
e697162 AS e697162,
c_6 AS c_6,
e114891_sum AS e114891_sum,
c_8 AS c_8,
c_15 AS c_15,
c_14 AS c_14,
c_10 AS c_10,
c_2 AS c_2,
c_3 AS c_3,
c_4 AS c_4,
c_13 AS c_13,
c_7 AS c_7
FROM ( SELECT c_12 AS c_12,
c_9 AS c_9,
c_16 AS c_16,
c_1 AS c_1,
c_11 AS c_11,
c_5 AS c_5,
e100289 AS e100289,
e100291 AS e100291,
e100293 AS e100293,
e108666 AS e108666,
e114893 AS e114893,
e198642 AS e198642,
e199187 AS e199187,
e199610 AS e199610,
e199710 AS e199710,
e199745 AS e199745,
e199778 AS e199778,
e199785 AS e199785,
e199834 AS e199834,
e199883 AS e199883,
e200165 AS e200165,
e200232 AS e200232,
e200539 AS e200539,
e200550 AS e200550,
e200657 AS e200657,
e290550 AS e290550,
e290551 AS e290551,
e290552 AS e290552,
e300264 AS e300264,
e300265 AS e300265,
e300266 AS e300266,
e300267 AS e300267,
e300293 AS e300293,
e300294 AS e300294,
e342998 AS e342998,
e343011 AS e343011,
e367060 AS e367060,
e380798 AS e380798,
e380800 AS e380800,
e508501 AS e508501,
e655563 AS e655563,
e697162 AS e697162,
c_6 AS c_6,
e114891_sum AS e114891_sum,
c_8 AS c_8,
c_15 AS c_15,
c_14 AS c_14,
c_10 AS c_10,
c_2 AS c_2,
c_3 AS c_3,
c_4 AS c_4,
c_13 AS c_13,
c_7 AS c_7,
np100 AS np100
FROM ( SELECT CASE
WHEN(
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) = o198988.parole_eligibility_date
THEN 'PED'
ELSE(
CASE
WHEN(
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) = o198988.final_release_date
THEN 'FRD'
ELSE(
CASE
WHEN(
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) = o198988.statutory_release_date
THEN 'SRD'
ELSE(
CASE
WHEN(
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) = o198988.sentence_end_date
THEN 'SED'
END )
END )
END )
END AS c_12,
TO_CHAR( o198988.sentence_commence_date, 'YYYY-MM' ) AS c_9,
CASE
WHEN o198988.sentence_commence_date BETWEEN TO_DATE( '19000101000000', 'YYYYMMDDHH24MISS' ) AND TO_DATE( '19500630000000', 'YYYYMMDDHH24MISS' )
THEN '1900-1950'
ELSE(
CASE
WHEN o198988.sentence_commence_date BETWEEN TO_DATE( '19500701000000', 'YYYYMMDDHH24MISS' ) AND TO_DATE( '19800630000000', 'YYYYMMDDHH24MISS' )
THEN '1950-1980'
ELSE(
CASE
WHEN o198988.sentence_commence_date BETWEEN TO_DATE( '19800701000000', 'YYYYMMDDHH24MISS' ) AND TO_DATE( '20020630000000', 'YYYYMMDDHH24MISS' )
THEN '1980-2002'
ELSE(
CASE
WHEN o198988.sentence_commence_date >= TO_DATE( '20020701000000', 'YYYYMMDDHH24MISS' )
AND TO_CHAR( o198988.sentence_commence_date, 'MM' ) BETWEEN '07' AND '12'
THEN TO_CHAR( o198988.sentence_commence_date, 'YYYY' ) || '-' || TO_CHAR(o198988.sentence_commence_date + 365, 'YYYY' )
ELSE(
CASE
WHEN o198988.sentence_commence_date >= TO_DATE( '20020701000000', 'YYYYMMDDHH24MISS' )
AND TO_CHAR( o198988.sentence_commence_date, 'MM' ) BETWEEN '01' AND '06'
THEN TO_CHAR(o198988.sentence_commence_date - 365, 'YYYY' ) || '-' || TO_CHAR( o198988.sentence_commence_date, 'YYYY' )
END )
END )
END )
END )
END AS c_16,
GREATEST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '19000101', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '19000101', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '19000101', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '19000101', 'YYYYMMDD' ) ) ) AS c_1,
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) AS c_11,
CASE
WHEN o342995.conviction_date o198988.max_sentenced_length_in_days
THEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) )
- TRUNC( o198988.sentence_commence_date ) ) ) ) )
ELSE o198988.max_sentenced_length_in_days
END )
END ) ) BETWEEN 0 AND 90
THEN 'a) Up to 3 months'
ELSE(
CASE
WHEN(
TO_NUMBER
( CASE
WHEN(o198988.max_sentenced_length_in_days IS NULL
OR o198988.max_sentenced_length_in_days = 0 )
THEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) )
- TRUNC( o198988.sentence_commence_date ) ) ) ) )
ELSE(
CASE
WHEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) )
- TRUNC( o198988.sentence_commence_date ) ) ) ) ) > o198988.max_sentenced_length_in_days
THEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) )
- TRUNC( o198988.sentence_commence_date ) ) ) ) )
ELSE o198988.max_sentenced_length_in_days
END )
END ) ) BETWEEN 91 AND 180
THEN 'b) 3 to 6 months'
ELSE(
CASE
WHEN(
TO_NUMBER
( CASE
WHEN(o198988.max_sentenced_length_in_days IS NULL
OR o198988.max_sentenced_length_in_days = 0 )
THEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) )
- TRUNC( o198988.sentence_commence_date ) ) ) ) )
ELSE(
CASE
WHEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) )
- TRUNC( o198988.sentence_commence_date ) ) ) ) ) > o198988.max_sentenced_length_in_days
THEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) )
- TRUNC( o198988.sentence_commence_date ) ) ) ) )
ELSE o198988.max_sentenced_length_in_days
END )
END ) ) BETWEEN 180 AND 365
THEN 'c) 6 to 12 months'
ELSE(
CASE
WHEN(
TO_NUMBER
( CASE
WHEN(o198988.max_sentenced_length_in_days IS NULL
OR o198988.max_sentenced_length_in_days = 0 )
THEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) )
- TRUNC( o198988.sentence_commence_date ) ) ) ) )
ELSE(
CASE
WHEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) )
- TRUNC( o198988.sentence_commence_date ) ) ) ) ) > o198988.max_sentenced_length_in_days
THEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) )
- TRUNC( o198988.sentence_commence_date ) ) ) ) )
ELSE o198988.max_sentenced_length_in_days
END )
END ) ) BETWEEN 366 AND 735
THEN 'd) 1 to 2 years'
ELSE(
CASE
WHEN(
TO_NUMBER
( CASE
WHEN(o198988.max_sentenced_length_in_days IS NULL
OR o198988.max_sentenced_length_in_days = 0 )
THEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) )
- TRUNC( o198988.sentence_commence_date ) ) ) ) )
ELSE(
CASE
WHEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) )
- TRUNC( o198988.sentence_commence_date ) ) ) ) ) > o198988.max_sentenced_length_in_days
THEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) )
- TRUNC( o198988.sentence_commence_date ) ) ) ) )
ELSE o198988.max_sentenced_length_in_days
END )
END ) ) BETWEEN 736 AND 1095
THEN 'e) 2 to 3 years'
ELSE(
CASE
WHEN(
TO_NUMBER
( CASE
WHEN(o198988.max_sentenced_length_in_days IS NULL
OR o198988.max_sentenced_length_in_days = 0 )
THEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) )
- TRUNC( o198988.sentence_commence_date ) ) ) ) )
ELSE(
CASE
WHEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) )
- TRUNC( o198988.sentence_commence_date ) ) ) ) ) > o198988.max_sentenced_length_in_days
THEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) )
- TRUNC( o198988.sentence_commence_date ) ) ) ) )
ELSE o198988.max_sentenced_length_in_days
END )
END ) ) BETWEEN 1096 AND 1825
THEN 'f) 3 to 5 years'
ELSE(
CASE
WHEN(
TO_NUMBER
( CASE
WHEN(o198988.max_sentenced_length_in_days IS NULL
OR o198988.max_sentenced_length_in_days = 0 )
THEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) )
- TRUNC( o198988.sentence_commence_date ) ) ) ) )
ELSE(
CASE
WHEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( ( CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ), TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) )
- TRUNC( o198988.sentence_commence_date ) ) ) ) ) > o198988.max_sentenced_length_in_days
THEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( (
CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ),
TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) )
- TRUNC( o198988.sentence_commence_date ) ) ) ) )
ELSE o198988.max_sentenced_length_in_days
END )
END ) ) BETWEEN 1096 AND 2555
THEN 'g) 5 to 7 years'
ELSE(
CASE
WHEN(
TO_NUMBER
( CASE
WHEN(o198988.max_sentenced_length_in_days IS NULL
OR o198988.max_sentenced_length_in_days = 0 )
THEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( (
CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
ELSE o198988.parole_eligibility_date
END ),
TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.final_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.statutory_release_date, TO_DATE( '99991231', 'YYYYMMDD' ) ),
NVL( o198988.sentence_end_date, TO_DATE( '99991231', 'YYYYMMDD' ) ) ) ) )
- TRUNC( o198988.sentence_commence_date ) ) ) ) )
ELSE(
CASE
WHEN(
FLOOR( SUM( (
TRUNC( (
LEAST(
NVL( (
CASE
WHEN o198988.parole_eligibility_date = o198988.sentence_commence_date
THEN NULL
