RichardKnight
asked on
Problem with decode in the where clause
I have a problem with the following query:
SELECT
TOL.product_heading,
TOL.tenor_category,
TOL.tolerance_pct,
RE.product_heading_order,
decode(TOL.tenor_category, 'SHORT',10 00000,'MED ',2000000, 'LONG',300 0000) AS FROM_TENOR,
RE.trade_id,
RE.dealer,
RE.profit_centre,
RE.counterparty_code,
TO_CHAR(DMO_FUNCTIONS.TO_O RACLE_DATE (value_dat e,'YYYYMMD D'),'DD-Mo n-YYYY') AS VALUE_DATE,
RE.instrument,
RE.instrument_description,
RE.rate,
TEMP.avg_rate,
'Tolerances used in this report' AS TOLERANCE_TEXT
FROM
dmo_days_prodheading_rate RE,
dmo_rate_tolerance_lookup TOL,
(SELECT
AVG(RE.rate) AS AVG_RATE,
RE.product_heading,
DECODE (GREATEST (2555, daysbucket), 2555,'SHORT',
DECODE (GREATEST (5475, daysbucket), 5475,'MED','LONG')) AS TENOR_CATEGORY
FROM
dmo_days_prodheading_rate RE
WHERE
RE.product_heading_order >19
GROUP BY
RE.product_heading,
DECODE (GREATEST (2555, daysbucket), 2555,'SHORT',
DECODE (GREATEST (5475, daysbucket), 5475,'MED','LONG'))
)TEMP
WHERE
TEMP.product_heading = TOL.product_heading AND
TEMP.tenor_category = TOL.tenor_category AND
TOL.product_heading = RE.product_heading AND
TOL.tenor_category = DECODE (GREATEST (2555, daysbucket), 2555,'SHORT',
DECODE (GREATEST (5475, daysbucket), 5475,'MED','LONG')) AND
product_heading_order >19 AND
rate NOT BETWEEN
(AVG_RATE - (AVG_RATE*TOL.Tolerance_pc t/100)) AND
(AVG_RATE + (AVG_RATE*TOL.Tolerance_pc t/100))
I get the following error:
ORA - 01843: Not a valid month
The problem appears to lie with the
TOL.tenor_category = DECODE (GREATEST (2555, daysbucket), 2555,'SHORT',
DECODE (GREATEST (5475, daysbucket), 5475,'MED','LONG'))
line as commenting this out allows the query to run.
Why do I get the 01843 error when this line is included? The daysbucket field is a number generated by subtracting 2 dates. The TOL.tenor_category field is a varchar2(20) field.
Thanks,
Rich.
SELECT
TOL.product_heading,
TOL.tenor_category,
TOL.tolerance_pct,
RE.product_heading_order,
decode(TOL.tenor_category,
RE.trade_id,
RE.dealer,
RE.profit_centre,
RE.counterparty_code,
TO_CHAR(DMO_FUNCTIONS.TO_O
RE.instrument,
RE.instrument_description,
RE.rate,
TEMP.avg_rate,
'Tolerances used in this report' AS TOLERANCE_TEXT
FROM
dmo_days_prodheading_rate RE,
dmo_rate_tolerance_lookup TOL,
(SELECT
AVG(RE.rate) AS AVG_RATE,
RE.product_heading,
DECODE (GREATEST (2555, daysbucket), 2555,'SHORT',
DECODE (GREATEST (5475, daysbucket), 5475,'MED','LONG')) AS TENOR_CATEGORY
FROM
dmo_days_prodheading_rate RE
WHERE
RE.product_heading_order >19
GROUP BY
RE.product_heading,
DECODE (GREATEST (2555, daysbucket), 2555,'SHORT',
DECODE (GREATEST (5475, daysbucket), 5475,'MED','LONG'))
)TEMP
WHERE
TEMP.product_heading = TOL.product_heading AND
TEMP.tenor_category = TOL.tenor_category AND
TOL.product_heading = RE.product_heading AND
TOL.tenor_category = DECODE (GREATEST (2555, daysbucket), 2555,'SHORT',
DECODE (GREATEST (5475, daysbucket), 5475,'MED','LONG')) AND
product_heading_order >19 AND
rate NOT BETWEEN
(AVG_RATE - (AVG_RATE*TOL.Tolerance_pc
(AVG_RATE + (AVG_RATE*TOL.Tolerance_pc
I get the following error:
ORA - 01843: Not a valid month
The problem appears to lie with the
TOL.tenor_category = DECODE (GREATEST (2555, daysbucket), 2555,'SHORT',
DECODE (GREATEST (5475, daysbucket), 5475,'MED','LONG'))
line as commenting this out allows the query to run.
Why do I get the 01843 error when this line is included? The daysbucket field is a number generated by subtracting 2 dates. The TOL.tenor_category field is a varchar2(20) field.
Thanks,
Rich.
Looks like this error is from:
>> TO_CHAR(DMO_FUNCTIONS.TO_O RACLE_DATE (value_dat e,'YYYYMMD D'),'DD-Mo n-YYYY') AS VALUE_DATE,
Don't know what the function you are calling is, but "not a valid month" is from a character-to-date conversion error. Look at what is being generated by:
DMO_FUNCTIONS.TO_ORACLE_DA TE(value_d ate,'YYYYM MDD')
and see if it is an Oracle date.
>> TO_CHAR(DMO_FUNCTIONS.TO_O
Don't know what the function you are calling is, but "not a valid month" is from a character-to-date conversion error. Look at what is being generated by:
DMO_FUNCTIONS.TO_ORACLE_DA
and see if it is an Oracle date.
My money would be on this line:
TO_CHAR(DMO_FUNCTIONS.TO_O RACLE_DATE (value_dat e,'YYYYMMD D'),'DD-Mo n-YYYY') AS VALUE_DATE,
What does DMO_FUNCTIONS.TO_ORACLE_DA TE look like? If it returns a CHAR value like '20040825' then the line above is equivalent to:
TO_CHAR(TO_CHAR(value_date ,'YYYYMMDD '),'DD-Mon -YYYY') AS VALUE_DATE,
... which doesn't make sense.
OTOH, if it takes a char parameter and returns a DATE then is value_date a character string in the required 'YYYYMMDD' format?
TO_CHAR(DMO_FUNCTIONS.TO_O
What does DMO_FUNCTIONS.TO_ORACLE_DA
TO_CHAR(TO_CHAR(value_date
... which doesn't make sense.
OTOH, if it takes a char parameter and returns a DATE then is value_date a character string in the required 'YYYYMMDD' format?
Seazodiac may be right. I was assuming that daysbucket was stored as an integer value, not derived. If it is derived, you have a problem with the date conversions in this computation, as he said.
ASKER
The value date was the first place I looked as it was the obvious place to try. Unfortunately commenting this out doesn't change things. The whole query runs correctly until you put in the decode join:
TOL.tenor_category = DECODE (GREATEST (2555, daysbucket), 2555,'SHORT',
DECODE (GREATEST (5475, daysbucket), 5475,'MED','LONG'))
and I can't see why this gives a problem.
TOL.tenor_category = DECODE (GREATEST (2555, daysbucket), 2555,'SHORT',
DECODE (GREATEST (5475, daysbucket), 5475,'MED','LONG'))
and I can't see why this gives a problem.
Rich, as I said in the very first post, how do you derive daysbucket? that's the key, which most often is ignored by people..
ASKER
Daysbucket comes from the dmo_days_prodheading_rate view:
decode (FU.product_type, 'RPO',TO_DATE (FU.effective_end_date,'YY YYMMDD') -
TO_DATE (FU.effective_start_date,' YYYYMMDD') ,
TO_DATE (FU.expir_matur_century,'Y YYYMMDD') -
decode(least(FU.deliv_cent ury, expir_matur_century),
expir_matur_century, TO_DATE(expir_matur_centur y,'YYYYMMD D'),
TO_DATE(FU.deliv_century,' YYYYMMDD') )) AS DAYSBUCKET,
The code used to generate the view works fine when run as a standalone query.
decode (FU.product_type, 'RPO',TO_DATE (FU.effective_end_date,'YY
TO_DATE (FU.effective_start_date,'
TO_DATE (FU.expir_matur_century,'Y
decode(least(FU.deliv_cent
expir_matur_century, TO_DATE(expir_matur_centur
TO_DATE(FU.deliv_century,'
The code used to generate the view works fine when run as a standalone query.
>>The code used to generate the view works fine when run as a standalone query.
Try scrolling through all the records in the view. I'll bet you get an error. But you won't see it until you scroll through, and the bad formatting is encountered.
Try scrolling through all the records in the view. I'll bet you get an error. But you won't see it until you scroll through, and the bad formatting is encountered.
is FU a table or view?
can you try this and post back if you have any errors?
select TO_DATE (FU.effective_end_date,'YY YYMMDD'),
TO_DATE (FU.effective_start_date,' YYYYMMDD') ,
TO_DATE (FU.expir_matur_century,'Y YYYMMDD'),
TO_DATE(FU.deliv_century,' YYYYMMDD')
from FU; --you need to replace FU with the real table or view name here.
can you try this and post back if you have any errors?
select TO_DATE (FU.effective_end_date,'YY
TO_DATE (FU.effective_start_date,'
TO_DATE (FU.expir_matur_century,'Y
TO_DATE(FU.deliv_century,'
from FU; --you need to replace FU with the real table or view name here.
Try to use the function TO_NUMBER(daysbucket) instead of daysbucket.
ASKER
seazodiac - your suggested code does produce an error but I knew it would. There are various checks and clauses in the where clause (I didn't show the where clause when showing my daysbucket code)that prevent any dates getting through that may cause an error. Daysbucket is included in a view that runs perfectly and all of the daysbucket values are as expected with no errors.
ASKER
It turns out that the problem was related to the way that Oracle 'optimises' queries.
I was using views and inline views and the predicates were getting mixed up when Oracle was combining the code into one uber-query. A simple 'order by' in the offending view has fixed the problem. If there is a better way of dealing with this not using order by (or rownum or by aggregating) I'd be grateful to hear it. Oracle suggest using decode around any columns that may cause problems and setting the offending values in those columns to NULL. This was too fiddly for my needs - I chose the easy route!
I was using views and inline views and the predicates were getting mixed up when Oracle was combining the code into one uber-query. A simple 'order by' in the offending view has fixed the problem. If there is a better way of dealing with this not using order by (or rownum or by aggregating) I'd be grateful to hear it. Oracle suggest using decode around any columns that may cause problems and setting the offending values in those columns to NULL. This was too fiddly for my needs - I chose the easy route!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the reason why you get this error is one of the 2 dates you are using to derive daysbucket does not have a valid date format