Error when Accessing Prompt values

[mur76]

Hi,

Iam getting the following error when I am accesing prompt values from a Dimension. The error is :ORA-01722-invalid number -1722.

Iam having Dimension variable as Number and accesing the Varchar values. Is this the problem. If So, how I can get the varchar values in Number data type.

 

[skrandel]

You need to supply a bit more info. How is the prompt built? We need to see syntax.


In general, you need to make data types match.

Steve Krandel
VERITAS Software

 

[mur76]

Here is the Syntax,

@Prompt('Select Org','A','Projects\Org2',[MULTI],FREE)

The query is parsing. It showing the Values tab also. When I click values tab, there is no response.

Sometimes the error is showing like Missing right parenthesis..

Regarding the Datatype:

I have 2 tables: 1. Projects 2. Lookup
Projects table column is having releationship with Lookup table. Based on this relation Iam fetching the values.

If you need more details, let me know.

 

[skrandel]

That's the syntax for the prompt. I need to see the whole line.

1) MULTI should not be in brackets.
2) MULTI will only be valid with and IN operator
3) This prompt can only be used with a VARCHAR column.

Steve Krandel
VERITAS Software

 

[mur76]

Here is the syntax in the Where Clause:

PV_PROJECTS.RELEASE IN @Prompt('SELECT RELEASE','A','Projlookups\Codevalue',[MULTI],FREE)

Release Column is Number data type and Iam having relationship in the Universe with Lookupid(Number datatype) and in the Prompt values are only input Varchar values.
Codevalue is Varchar data type.

IF you need more detail, pls.let me know

 

[skrandel]

Again, why the brackets around MULTI?

What happens if you change the 'A' to 'N'?

I don't understand how you can expect a character value to be used in a condition with a numeric. You have to do some converting of the datatypes.

Steve Krandel
VERITAS Software

 

[mur76]

Thanks Steve.

Iam using BO 6.5 the Syntax is parsing correctly and when Iam running the report it is not showing the values when I clicked Values Tab.

Here is the Syntax:

PV_PROJECTS.ORG2 IN @Prompt('SELECT ORGZ','A','PV_PROJECTS\ORG2',MULTI,FREE)

 

[skrandel]

Does the object 'PV_PROJECTS\ORG2' have an LOV associated with it?

Steve Krandel
VERITAS Software

 

[mur76]

Steve,

It is having LOV, but when Iam clicking the Values button it is not responding..

 

[sh4kil]

Oracle is unable to match PV_PROJECTS.RELEASE with a value being returned from Codevalue, i.e. it cannot convert to Codevalue to a number due to a non-numeric character.

You could try SELECT TO_NUMBER(columnname of Codevalue) FROM tablename and probably will get same error.