Error when creating a view in physical layer

Chris Arnold

I am trying to create a view in my physical layer of my RPD (OBIEE 12c). Here is what I have done:

1) Ensured that CREATE_VIEW_SUPPORTED was checked.

2) Right-clicked my physical schema and selected 'New Physical Table'

3) Named my view 'AR_IN_VIEW'

4) For Table Type, I picked 'SELECT'

5) Selected the 'Default Initialization String' radio button

6) Entered in the following query:

  select * from ARSchem.ACCOUNTS A where A.account_num in (select B.account_num from ARSchem.ACCOUNTS_IN ;

7) Checked the 'Cacheable' check box, and picked the 'Cache Never Expires' radio button.

8) Click 'OK'

9) I then checked in my changes & saved the RPD. (My consistency check returns no errors)

10) I right-click the view and select 'Update Row Count' or 'View Data'. Here is where I get the error:

  [NQODBC][SQL_STATE:HY000][nQSError: 10058] A general error has occurred.

Notes:

- I have tested this query through SQL Developer and it runs fine.

- I can successfully 'Update Row Count' and 'View Data' on all of the physical tables in my ARSchem physical schema (in the physical layer)

- While I would have thought the RPD would have used my query to auto-generate the columns for my view, I see that no columns get generated. So I manually added some of the columns myself in the 'Columns' tab. This doesn't seem to fix my issue. I still get the error.

Am I missing a step or doing something wrong?

SriniVEERAVALLI

You need to do any of these

1) use column names in the query like select Name, fst_name from ARSchem.ACCOUNTS

or

2) After created as you have done as in Point# 6, just add a column manually to the object ex: Name and then go for "view data"

Andrew Fomin.

If your point 6 query is the exact one you are trying to use in BI, then remove ; from the query.

Chris Arnold

Thank you both for your replies.

I removed the ';' from the end of my query. I also explicitly named the columns I am selecting (rather than use the '*').

The query is now like this:

select account_num,fname,lname,address1,address2,city,state,zip from ARSchem.ACCOUNTS A where A.account_num in (select B.account_num from ARSchem.ACCOUNTS_IN

After doing a consistency check & saving the RPD...

1) The columns still do not show up on the 'Columns' tab. I would still need to manually add each one on 'Columns' tab?

2) I can now do 'Update Row Count' but I still get the error when I try to 'View Data'

Again, thank you for your replies. I will keep looking at this. If I get it figured out, I will post the resolution.

Andrew Fomin.

1. Yes, you have to define columns manually.

2. "View data" will show you the data after you define columns.

Chris Arnold

Yep...defining my columns on the 'Columns' tab seems to work.

It seems weird to me that it would not auto-generate the columns based on my SELECT statement...but what do I know? At least we got this figured out now.

Thanks for all of the help!

SriniVEERAVALLI

rpd is not a db client tool to know your sql queries.

You have to tell what is what about columns and tables to the rpd

This is what I said do manually in very first response!

Andrew Fomin.

Chris, actually, the core issue was the error in the query (that semicolon in the end). Columns definition is necessary but a query syntax supposed to be correct in the first place.