Wrong sql generated

User_K41ZX

Hello everyone,

I have just finished creating a new .rpd file and uploaded it the BI server.

The problem is , when I go to Analysis/Analysis and Interactive Reporting in the BI web page  and start to drag and drop the tables, the from clause in the  sql query generated

is wrong. Instead of putting in the from clause the names of the tables dragged, it is putting the name of the subject area and resulting in an error:] Oracle Error code: 942, message: ORA-00942: table or view does not exist at OCI call OCIStmtExecute. (HY000).

I have created the joins but it looks like it is not being able to generate the right from clause.

Robert Angel

Hi, you will always see the logical subject area addressed in OBIEE, you need to look into the log files to see the underlying physical table names addressed.

Gianni Ceresa

1704872 wrote:I have just finished creating a new .rpd file and uploaded it the BI server....the from clause in the sql query generated is wrong

I have a bad news for you: your RPD is wrong.

OBIEE will never guess or imagine anything, it will use what you did in the RPD to generate queries.

If a query is wrong it means your RPD is wrong (except if you find a bug, but you don't say anything pointing in that direction).

Also a detail: LSQL <> SQL !

OBIEE talks in LSQL, its own logical SQL which is then translated into physical SQL when talking to a relational database, MDX for cubes etc.

They do look similar, so you are probably looking at some LSQL instead of physical SQL.

User_K41ZX

What I did is:

In the Criteria Tab I dragged the columns I need to put in my analysis report. If I go to the results tab I see that an error is thrown.

Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)

State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)

State: HY000. Code: 17001. [nQSError: 17001] Oracle Error code: 942, message: ORA-00942: table or view does not exist at OCI call OCIStmtExecute. (HY000)

State: HY000. Code: 17010. [nQSError: 17010] SQL statement preparation failed. (HY000)

SQL Issued:  select a.a1,a.a2,b.b1 from c.

The sql above is not the exact sql statement that is shown but gives an idea of what is happening. IN the from clause is put c and not table names. c is the name of the subject area.

Gianni Ceresa

1704872 wrote:State: HY000. Code: 17001. [nQSError: 17001] Oracle Error code: 942, message: ORA-00942: table or view does not exist at OCI call OCIStmtExecute. (HY000)

As you can see you have a nice and clear Oracle Database error: ORA-00942: table or view does not exist

So, as said, your RPD is wrong somewhere ...

And you aren't really providing any other info to point you in the right direction (an idea of what can be helpful:   ) ....

PS: screenshots of the model and the real generated query are needed if you want better guesses.

Robert Angel

Something to try is can you view data through the physical table(s) that you have used in the subject area with the connection pool they have been created under.

Table does not exist implies that either your physical table is misnamed, or your connection pool does not have rights to view it.