Fake Table Not Working

peterbruce

peterbruce

Legendary Poster
JDEList,

I have created a table in JDE, but not generated it (see thread #102985). I also created a database view of the same name and structure. However, JDE is not connecting to it.

I have attached a zip file containing two files - an excel workbook containing the file structures and a text file containing the create view statement and a portion of the debug log.

This is only in my DV811 environment and pathcode. All tables have been checked in, but not deployed to the enterprise server. I have created a test application that runs on the FAT dev/admin client.

Have I missed something? Does this still work in 8.11 (my system config is in my signature)? What have I done wrong?
 

Attachments

  • 122695-FakeTableProblem.zip
    3.9 KB · Views: 153
Hey Peter,

A new avatar, huh? I don't have a solution for you. I've never done this on an oracle db, but I gotta think it would the same on any db that you can create a view for. One thing to try:

1) submit the following statement from your sql tool:
select * from TESTDTA.F5543E0M

Do you get output?
If yes:
Does your new table definition in jde (F5543e0m) match, EXACTLY, with that of F5543E00? Same field order? Same field names? Same column prefix?
 
G'Day Jeremy,

I had the old avatar for a couple of years and I thought it was time for a change. You can't really see (it's too small), but my new avatar is the Bruce Coat of Arms.

The SQL runs fine on my SQL tool. As far as I can see, the columns are exactly the same - same names and same order

In one of my experiments I only had one column and it had the same errors in the debug log. I had even named the column in the create view statement/command. What I can't understand is that the debug log contains the oracle error:

ORA-00942: table or view does not exist

It appears that the error is coming from the database. I copied the SQL in the debug log to my SQL tool and it ran fine.

Have you done this in E8.11/TR8.96 using another database?
 
Peter,

I would concentrate the troubleshooting on the ORA-942. This either means that the table/view really does not exist or that the proxy user used by E1 does not have access to the table. I would suggest you are dealing with the second case.

If your 8.11 install is configured in the default way the proxy user will be PSFT. PSFT will not be able to see a new table created in the TESTDTA schema unless it has been granted SELECT ANY TABLE or a role like DBA_ROLE. Out-of-the-box the E1 tables are delivered with PUBLIC access granted. New tables that you create via a SQL tool will not have PUBLIC access granted. You must explicitly grant access to either PUBLIC, the PSFT proxy user or a role that the PSFT proxy user holds. I personally prefer to grant to a role as it allows for more flexibility in the future and is more secure than public grants.
 
Yes - first school of thought, give everyone rights to the table. If that
causes it to work, then you know it is security. If it doesn't work - make
sure that columns are in the exact same order, same prefex and expected data
type(s)...

Ordinarily - it's a simple task. The most troublesome issue is security

One last issue - make sure you are putting them in the right area. On
iSeries - I'd say library. I forget what Oracle calls them (CRPDTA,
TESTDTA, PRODDTA....)

(db)




--
 
Although it's already been covered, I'll re-iterate...

I did this just last week on a SQL 2000 database and three things bit me: 1) I had one column that didn't match (I fat-fingered the prefix) 2) Security: I created the view under my database ID and it wasn't recognized. I changed the DBO to PRODDTA (yes I created this is production first...shame on me, but it was necessary) 3) After all that it still didn't work. Like you I never actually generated the database from JDE, so I gave that a try: removed view, generated table, removed table from database and re-created view. Then it worked. Can't explain it, but it just worked....

Oh, and I deleted my global tables about ten times, each time I tried something new, just to make sure that the local spec was not my problem (since I was testing locally).

For what it's worth..
 
Are you using this "fake" table with the JDE toolset? I think you will have issues unless you generate it through JDE. It still needs to have a .h file for it's definition to be used in any tool JDE has. It's just my opinion; but unless you create it in via a JDE table gen you will always have some sort of issue. Is there is reason you can't create it via a JDE Gen? - Thanks Angelis
 
No - fake tables (I call them Virtual Tables) - are either SQL Views,
Logicals or foreign tables - that fallow JDE convetions (naming, dd and
security)... An FDA Spec is created (NOT GENERATED) that perfectly matches
the layout...

If all goes well - the Virtual Table is treated just like any other JDE
Table.

Make sure it is in the same schema/library and security is *all/*public for
inital testing.

one other gotcha - UNICODE????

(db)




--
 
Not sure what this needs to be for your specific database, but the command for me at the time was:

GRANT ALL ON CRPDTA.F550101 TO PUBLIC

Obviously, you need to substitute your information for the file and environment. I posted this and more info related to this back in August of 2000 but can't seem to find any of it.

Let us know,
Ben again,
 
Peter,

Don't know if this will help but I have been trapped by not explicitly casting the fields to the same exact data types that JDE is expecting.

Sometimes the implicit conversions of data that the database server does won't match what JDE expects.

I had one virtual table at one time where I had to add TOP 1000000 (or something to that effect) to the select - never quite figured that one out but without it, JDE didn't like the table and wouldn't retrieve any records, with it it worked fine. Go figure.

Not sure of the Oracle syntax but I have also added no locking hints in SQL in some of my views as well - this can reduce the time it takes to retrieve the records - but of course you need to know that the data you are selecting from is relatively stagnant or you may not get the records you expect.

Hope one of these ideas helps.

Have a good day!

Dave Schlieder
 
Dave,
Adding the "TOP 1000000(a big number)" is not related to JDE. We have to add it for some views that have nothing to do JDE to make them work.
 
We are starting to use "Fake" tables quite a bit (we call them Virtual Tables or SQL Views).

Anyway, I have found that if it the FIRST time you have created the table you must generate it from within JDE in at least one environment, then drop the table in SQL and create your SQL view to match the table struct. I don't know why this works...

Also, as others have pointed out, check security, field data types, dbo, etc.
 
I think there is a bug in the JDE list... my last post only showed up in the threaded view... no option to go to page 2 of the thread...
 
LOL, no, it was no were in the flat list, only in the threaded list... as soon as I made another post I got the option for page 2|All.
 
Justin, Daniel, Francois, Angelis, Ben, Dave, Bull, Brian, Eric,

Firstly, my apologies for the tardiness of my reply, I have been away for a few days, and have returned to work this morning.

Secondly, thank you all for your responses and advice and for taking the time to do so.

Thirdly, I am just about to go get some coffee and set to work to solve this problem with the advice you have provided. My gut feeling is that, as many of you have pointed out, it is a permissions problem. I will let you know what I find out.

By the by, I called them "Fake Tables" as that was the term used in the post I read. I think the term "Virtual Tables" is more appropriate.
 
Justin, Daniel, Francois, Angelis, Ben, Dave, Bull, Brian, Eric, and anyone else that is interested,

I have got my "Virtual Tables" (aka "Fake Tables") working!
smile.gif
smile.gif
cool.gif
The problem was indeed permissions (I probably should have realised this
blush.gif
- but that's the way it goes sometimes). There was no need to generate the tables.

Ben's SQL command to grant access (adjusted for the virtual table name) worked for my database.

I would like to thank you all again for your advice, time and expertise. That is what makes JDEList the best place for help with JDE (and all its aliases and incarnations and related topics).
 
JDEList,

The thread to wich I refered in my original post is not 102985 but 105985 - sorry I made a typo
blush.gif
. Here is the correct link:

Thread 105985
 
Back
Top