RE: BSVW Table Joins

[David Robertson]

RE: BSVW Table Joins

Hi Zoltán,
The procedure I used is the same as for importing foreign tables into OW I beleive, but basically:
1. Create a new table in OW, F55XXYYY, with all of the columns you require. You need to give it a file prefix in the creation process, and also define a primary, unique key. This does not seem to be important to the process, so just create something to keep OW happy.
2. Generate the file in OW.
3. Go to the appropriate enterprise server, and delete the index and file i.e. F55XXYYY_1 and F55XXYYY.
4. Start the apropriate interactive SQL feature, and CREATE VIEW F55XXYYY etc.. I renamed my output fields to match the column names I entered in OW for the table, but I don't know if this is required. A simple example of the SQL required is:
CREATE VIEW F55XXYYY AS SELECT aian8 as ccan8, aiclmg as ccclmg, abalky as ccalky
FROM F03012, F0101
WHERE aian8=aban8 AND aico<>'00000' AND (abatr='E' OR abatr='C')
5. Back to OW, and create a new BSVW, something like V55XXYYY, over the dummy file F55XXYYY, including all columns.
6. Generate the view.
7. You can now use the V55XXYYY as the input BSVW for UBE's and APPL's.
8. Moving to Production. Haven't done this yet, but I plan to deploy the dummy file and the view, repeat the processes 3 and 4 above, then generate the V55XXYYY view again in Production.

I also added attachments to the OL for F55XXYYY and V55XXYYY, so that the next poor developer has a chance at figuring out is happening. I think it is also very useful to copy the CREATE VIEW SQL statement into the attachment, so that you can regenerate if you need to.

Also, I now have no F55XXYYY_1, but OW does not seem to be bothered by this.

David Robertson
[email protected]
WorldSoftware A4.1 to A8.1
OneWorld B7a to B733.3
All O/S's, All DB's

Regards,
David Robertson
Mobile: +44 (0)7799664342
Fax: +44 (0)
Mailto[email protected]
Webpage: http://David.Robertson.net/

 

[Zoltan_Gyimesi]

David,
Thank you that you described and shared your solution.
I am going to archive it for the future.
Please, let us to know if you find something hard limitation or problem using this method. Hopefully you won't find.
Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)

 

[David Robertson]

RE: BSVW Table Joins

Margaret,
Sorry, but I haven't any experience with MS SQL Server 2000 yet. I know that it works on B7332 DB2/400 and Oracle. I also haven't tried it on XE so far.

What you are saying is very interesting though, because if JDE has added the object type 'TABLE' to the check, then it effectively blocks the work around for a limitation of OneWorld that I know a number of people are using. Let me know how you get on.

Regards,
David

 

[Adrian_Chimirel]

Sorry to intervene David, but would there be a problem in NOT applying steps 2&3? It seems that doing 2, you're just forcing yourself to do 3! Am I missing something?
Thank you,
Adrian

LIVE: B732.1 SP12.2, Oracle 806, FormScape 2.1
SANDBOX: Xe SP15 & Update1, Oracle 8i
RS/6000, Citrix

 

[David Robertson]

Further to Margarets comments: it seems the problem was elsewhere, and the technique still applies to Xe.

Adrian,
In step 2, you generate the file on the server, but also the .h file and the specs for the file. If you don't generate it, then OW won't let you access it at or use it at all.

What we are achieving is to fool OW into believing it has created a file, which we then delete and replace with a SQL view.

 

[Richard_Zhang]

Hi David,

Thank for your great idea!

I tried this in our XE, not successful, 2 things I need to check with you:

1)After you created the dummy table, you deleted it, then you created a view, did you try to check the data from UTB. (I could not)

2)After I created OW BSVW and an APPL, there was no error, but I could retrive any record from APPL.

I think if can read the records from UTB, the APPL should have no problem to retrive records

Thanks and regards

 

[David Robertson]

Hi Richard,

Sorry for the very late reply. For some reason your response never came to me by email.

It's a while since I did this now, but I'll try to figure it out.

This technique does work in Xe I believe.

1) I don't thing the TBLE is viewable in UTB. I think this is to do with the way UTB works, and it is actually trying open a table, but as it is really a view, it doesn't quite happen the same way. UTB works very differently from the way OneWorld APPL's and UBE's work.

2) After I create the BSVW over the dummy TBLE that is really a CREATE VIEW, I am able to retrieve data in APPL's or UBE's. The BSVW is really just a template for an SQL statement, and will use the TBLE it is over for the SELECT FROM paramaters. Something like SELECT (BSVW columns) FROM (dummy TBLE)... (dummy TBLE) is really an SQL VIEW, but the SQL generated is still correct, and should return the expected values.

Turn on debugging to the maximum level and check what the SQL statement being generated is. This usually gives good clues as to the problem.

You may have trouble trying to open the table for update though....

Good luck,
David