Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Problem with decode in the where clause

Posted on 2004-08-26
14
Medium Priority
?
869 Views
Last Modified: 2012-06-21
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',1000000,'MED',2000000,'LONG',3000000) AS FROM_TENOR,
      RE.trade_id,
      RE.dealer,
      RE.profit_centre,
      RE.counterparty_code,
      TO_CHAR(DMO_FUNCTIONS.TO_ORACLE_DATE(value_date,'YYYYMMDD'),'DD-Mon-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_pct/100)) AND
      (AVG_RATE + (AVG_RATE*TOL.Tolerance_pct/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.

0
Comment
Question by:RichardKnight
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +3
14 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 11903468
How do you generate daysbucket?

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
0
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11903470
Looks like this error is from:
>> TO_CHAR(DMO_FUNCTIONS.TO_ORACLE_DATE(value_date,'YYYYMMDD'),'DD-Mon-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_DATE(value_date,'YYYYMMDD')
and see if it is an Oracle date.
0
 
LVL 15

Expert Comment

by:andrewst
ID: 11903472
My money would be on this line:

TO_CHAR(DMO_FUNCTIONS.TO_ORACLE_DATE(value_date,'YYYYMMDD'),'DD-Mon-YYYY') AS      VALUE_DATE,

What does DMO_FUNCTIONS.TO_ORACLE_DATE 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?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 7

Expert Comment

by:bvanderveen
ID: 11903489
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.
0
 

Author Comment

by:RichardKnight
ID: 11903972
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.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 11904002
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..
0
 

Author Comment

by:RichardKnight
ID: 11904512
Daysbucket comes from the dmo_days_prodheading_rate view:


decode (FU.product_type, 'RPO',TO_DATE (FU.effective_end_date,'YYYYMMDD') -
                                      TO_DATE (FU.effective_start_date,'YYYYMMDD'),
                                      TO_DATE (FU.expir_matur_century,'YYYYMMDD') -  
                                                                            decode(least(FU.deliv_century,  expir_matur_century),
                                                   expir_matur_century, TO_DATE(expir_matur_century,'YYYYMMDD'),
                                                   TO_DATE(FU.deliv_century,'YYYYMMDD'))) AS DAYSBUCKET,

The code used to generate the view works fine when run as a standalone query.
0
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11904537
>>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.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 11904553
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,'YYYYMMDD'),
TO_DATE (FU.effective_start_date,'YYYYMMDD'),
TO_DATE (FU.expir_matur_century,'YYYYMMDD'),
TO_DATE(FU.deliv_century,'YYYYMMDD')
from FU;  --you need to replace FU with the real table or view name here.


0
 

Expert Comment

by:kamar_k
ID: 11943371
Try to use the function TO_NUMBER(daysbucket) instead of daysbucket.
0
 

Author Comment

by:RichardKnight
ID: 11952264
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.
0
 

Author Comment

by:RichardKnight
ID: 11990693
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!
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12591491
PAQed with points refunded (125)

modulo
Community Support Moderator
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question