ORA-01481: invalid number format model :-1481

building semantic layer
1

I’m getting this error msg:

ORA-01481: invalid number format model :-1481

with this sql from a Filter I created in Designer:

SELECT FEDERAL_EFFECTIVE_DATE
FROM FED_DEDN
WHERE to_number(FEDERAL_EFFECTIVE_DATE, ‘YYYY’) = @prompt(‘Enter Tax Year’, ‘n’, , mono, free)

I tried using to_char also, but the query just hung.

Any suggestions?

Thank you,
Bob

BobL (BOB member since 2002-08-29)

2

Try:

SELECT FEDERAL_EFFECTIVE_DATE 
FROM FED_DEDN 
WHERE to_number ( To_Char (FEDERAL_EFFECTIVE_DATE, 'YYYY') ) = @prompt('Enter Tax Year', 'n', , mono, free)

Andreas :de: (BOB member since 2002-06-20)

3

Just a note… I think you may also want to consider using: ‘’ for the list of values. This makes sure that you don’t ever that the LOV try to build.

The TO_NUMBER(TO_CHAR…)) suggestion by Andreas should work.

-RM

digpen :us: (BOB member since 2002-08-15)

4

Please, could you eloborate on this one?

Andreas :de: (BOB member since 2002-06-20)

5

If I didn’t want to use a list of values for this object, I could phrase it as:


TO_NUMBER( TO_CHAR( FEDERAL_EFFECTIVE_DATE, 'YYYY') ) = @prompt('Enter Tax Year', 'N', '', mono, free)

The ‘’ means that it doesn’t pull the list of values from a Class or Object, therefore you never really have a LOV for this prompt.

-RM

digpen :us: (BOB member since 2002-08-15)

6

And why would you put in the empty single quotes for the LOV parameter in the @prompt instead of just leaving the parameter empty :?:

TO_NUMBER( TO_CHAR( FEDERAL_EFFECTIVE_DATE, 'YYYY') ) = @prompt('Enter Tax Year', 'N', , mono, free)

Andreas :de: (BOB member since 2002-06-20)

7

Ah…

I do that out of habit. BO used to have problems with the parameter being left blank. I don’t remember which version, but it was one of the early 5.x versions. I have done it both ways, but the double single-quotes have had a more consistent behavior for me.

-RM

digpen :us: (BOB member since 2002-08-15)

8

Digpen, thank you for the insight :slight_smile:

Andreas :de: (BOB member since 2002-06-20)