Webi ORA-01722 Invalid Number

building reports webintelligence
1

Hi
I have a WEBI report, where i base it directly from a view in sql, there is a certain column in that view that its type is number, this view is based on a table that has a column of this type number(15,5), when i run the view in oracle sql developer, it went all smoth for the entire table in all its entries, but when i try the same view on WEBI the object althought specifying number too, when i run the querry it throws me an error:

ora-01722: invalid number . (WIS 10901)

it only happens in that exact object every other object/column went fine.

need answer with some urgency.

thank you very much

ps: the field is suposed to accept positive like 1 or negative numbers like -1, and amounts lower than 1 like 0,1.

normanin (BOB member since 2014-06-05)

2

Welcome to B:bob:B!

Can you post the SQL statement that WebI generates?

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

thank you very much for the welcome

the statement is:

SELECT
SCORE_TABLE.PLAYER_ID,
SCORE_TABLE.COMENT,
SCORE_TABLE.RESULT
FROM
SCORE_TABLE
ACCOUNT
WHERE
(SCORE_TABLE.PLAYER_ID=ACCOUNT_TABLE.PLAYER_ID)

ps: not posting the entire SQL but the result is where the problem is, it was suposed to be a number.

normanin (BOB member since 2014-06-05)

4

Does this SQl statement runs OK in a query tool? For all records it returns?

Are player_ID columns in both tables SCORE_TABLE and ACCOUNT_TABLE numbers? Can you double-check?

Marek Chladny :slovakia: (BOB member since 2003-11-27)

5

what do you mean query tool?

anyway no both player_id are string, and they work fine, the only one failing is the result…

normanin (BOB member since 2014-06-05)

6

Oracle SQL Developer or whatever tool you use to query the database directly.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

7

oh yeah in oracle developer it went all fine, positive, negative, no problem at all.

ps: i even checked its integrity on universe designer… by the way forgot to add it was suposed to accept (null) results.

normanin (BOB member since 2014-06-05)

8

Maybe there is something else going on in the part of the final SQL that WebI generates and that you did not post? Just guessing based on the few pieces of information that you have provided so far.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

9

well i can check it, but i didnt put any extra webi condition about the result… :S anyway thx

normanin (BOB member since 2014-06-05)

10

It might be the locale settings on your Webi preferences.

If you are set to the wrong locale, then it might be expecting 0.5 instead of 0,5 or vice versa

11

hum gonna check that, oh wait how do i edit my locale preferences? :stuck_out_tongue:

normanin (BOB member since 2014-06-05)

12

The Preferences link at the top right of the Launchpad / Infoview home page

13

hum im using the web intelligence rich client of bussiness project XI 3.1, and i dont see the preferences option link in the top :S

normanin (BOB member since 2014-06-05)

14

Ah, ok, I was talking about the web-based versions.

Go to Tools → Options → Locale

15

ok thank you very much

normanin (BOB member since 2014-06-05)

16

If this is the SQL then the first thing I notice is you are missing a comma between the table names. Second, the join says ACCOUNT_TABLE.PLAYER_ID but there is no ACCOUNT_TABLE specified in the FROM clause. This will generate an error when running the SQL. Actually, the only table you ‘truly’ have in the FROM is one named ACCOUNT because of the missing comma. You aliased the table SCORE_TABLE.

SELECT 
SCORE_TABLE.PLAYER_ID, 
SCORE_TABLE.COMENT, 
SCORE_TABLE.RESULT 
FROM 
SCORE_TABLE 
ACCOUNT 
WHERE 
(SCORE_TABLE.PLAYER_ID=ACCOUNT_TABLE.PLAYER_ID)

The ORA-01722 error occurs when Oracle is trying to cast a non-number value, typically a varchar2, to a number. There is nothing in the SQL you show that does this unless one of the PLAYER_ID columns is a number and the other is not. If so, you will get the error when Oracle attempts to convert the bad value to perform the join.
The datatype in Bus Obj is irrelevant at this point as it isn’t used when running the SQL, it just tells BO how to handle the data it gets back. Oracle is working with the data types defined for each column in the table.

Also, keep in mind that most SQL tools such as SQL Developer and Toad initially bring back only part of the result set so you may not hit the bad value unless you scroll through the entire result set.

I think there is more to the SQL than what is shown.

jwhite9 :us: (BOB member since 2006-07-28)