New database fields not showing in UDT 4.1 sp3

[Former Member]

Hi guys    

our DBA has added 3 new fields in one of the views in database(oracle).

Now when I am trying to pull in these new fields in my universe, I am getting an error saying ' exception DBD oracle ora 00904'.

I have tested these fields in sql developer and it works fine.

Any ideas?

[Former Member]

looked like something funny happened in pre-prod.

I did a promotion management from dev to prod and it seems to have fixed the issue. I still don't know the root cause though!

[Former Member]

The strange thing is, it works fine in the dev environment but in pre-prod, when I test these newly added columns in the 'query panel, then I get this ORA error. These newly added fields are from a view which is a 'materialized' view.

[mhmohammed]

Hi Buddy,

Go to this link and see what it says for that error ORA 00904, there can be few reasons.

ORA-00900 to ORA-01499

The column name may be missing or invalid.

Questions:

1. You said it works in Dev and not in Pre-Prod, are you sure that the Materialized view exists in Pre Prod database?
2. If yes, is the definition of the materialized view same in both envs? Definition meaning, the database type of columns and the size of columns example varchar(30), is it the same in both envs?
3. Are the column names same in both envs? You can have a column name more than 30 characters.
4. Are you using any reserved names for column names, such as Date? Its a reserved word, you can't use that.

Let us know if you were able to figure it out.

Thanks,

Mahboob Mohammed

[Former Member]

When ORA-00904 occurs, you must enter a valid column name as it is either missing or the one entered is invalid. The "invalid identifier" most common happens when you are referencing an invalid alias in a select statement.  The Oracle docs note this on the ORA-00904 error:

ORA-00904 string: invalid identifier

Cause: The column name entered is either missing or invalid.

Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #.

If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.

To avoid ORA-00904, column names cannot be a reserved word, and must contain these four criteria to be valid:

• begin with a letter
• be less than or equal to thirty characters
• consist only of alphanumeric and the special characters ($_#); other characters need double quotation marks around them

Another important factor in correcting ORA-00904 is remembering to run  catproc.sql

You can also check your trace file to find the particular error which is causing the ORA-00904 to occur.

[Former Member]

Immediate solution : create the same view at IDT data-foundation a  derived table.

are you getting this error when you are doing refresh structure? just after refresh structure or after clicking table detection nexxt  or after clicking column detection next..?

[CdnConnection]

    Did you refresh your structure and make sure the fields are visible in the foundation layer before you use it in Business Layer.

Ajay

[Former Member]

Hi Ajay

i am working In the universe designer.. I did refresh  universe structure

[former_member207052]

To Troubleshoot:

1. Reconfirm that your connection is pointing to the right database.
2. Create a new universe for testing and add the table to confirm if you can see the columns.

If things are good, make sure the column name is valid and can be properly parsed in UDT. Do an integrity check to make sure that there are no issues with the semantic layer.