union & table joins

dsaad

Member
I have created a business view V56nnn which is a union of F4201 & F42019 - Sales Order Header & History tables.
Is there a way to then create another business view which a join of my V56nnn and another table?


XE RELEASE B7333 SP14.2
 
Welcome to the Business Views Problems web-site, David!
And allow me to cite Sr. JDE Specialist Saad Mian's tips and techniques related to the issue you/we have.
1 BSVW introduce a middle layer between the application and the database.
2 They do not use aggregate functions, sub-elects or value-based joins.
3 They may use misleading column names in case of multiple joins.
4 They cannot be created over non-OneWorld files and
5 Non-OneWorld software cannot use BSVW to query data.
But how do we approach YOUR problem? When you can't make it, fake it! That is:
1 Create a DATABASE View, say FV55xxx (as a union of F4201 & F42019)
2 Create a OneWorld file (FV55xxx) using the DATABASE View's specs (columns, data types ...).
3 Do not generate the file on the Enterprise Server! Just check the specs out, on the Deployment Server
4 Create the (working) Business View W55xxx over FV55xxx joined with your OTHER table.
5 Generate W55xxx, and use it in any OW Appplication or UBE :)
Thank you Saad!

LIVE: B732.1 SP12.2, Oracle 806
SANDBOX: Xe SP15, Oracle 8i
RS/6000, Citrix
 
The answer is no. Bus.Views is based on tables, you can not have a BSVW
based on another BSVW
Emad Banoub
A7.3 Cu.12/X3 - Xe 13.0 coexistence




[email protected]@jdelist.com on 04/26/2001 02:07:05 PM

Please respond to [email protected]

Sent by: [email protected]


To: [email protected]
cc:

Subject: union & table joins


Subject: union & table joins

I have created a business view V56nnn which is a union
of F4201 & F42019 - Sales
Order Header & History tables.
Is there a way to then create another business view
which a join of my V56nnn
and another table?


XE RELEASE B7333 SP14.2
--------------------------
Visit the forum to view this thread at:
http://198.144.193.139/cgi-bin/wwwthreads/showflat.pl?Ca
t=&Board=OWDEV&Number=9824


----- End forwarded message -----
 
I need some clarification on your suggestion.
Could you elaborate on step #3.
Is the file FV55xxx actually created and populated with any data or is this really a way to fake out JDE into allowing a join between a business view and some other tables?

If so, are there any issues in getting this defined (i.e. OMW project project advancement) in CRP and PROD after we test it in DEV?



XE RELEASE B7333 SP14.2
 
As I was saying, faking is the clue;
-you create the table within OW in order to make OW think it is dealing with a ... regular OW table
-you do NOT generate it (remember, it's only a mask)
-you may check it into the deployment & you may use it in any OW business view
and, yes, this IS a valid way to fake out JDE into allowing ....

As far as I kow, there are no issues in handling it as any other OW table (but, don't generate it ... :) and, I tried this method only in B7321 but I don't see any reason why should it fail in Xe; will you be so kind to test it and let the list know the results, please?

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

I tried this method on B733.2 and it works fine. Is there any way to execute action query on DBMS (SQL 7.0 in my case) from OW?

Thanks in advance,
Bojan.

B733.2, SP 10, NT 4.0, SQL 7.0.
 
Hello Bojan,

It may be, but, unfortunately it is (currently) out of my repertoire :)

Let's hope somebody else will share it with us.

One is glad to be of help,

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

one technique is to create a dummy F55xxxx table. Set up SQL triggers on insert or update that use the data in the passed columns as the information controlling the sql or SP you want to execute.

Regards,

Larry Jones
[email protected]
OneWorld B733.1, SP 11.3
HPUX 11, Oracle SE 8.1.6
SandBox: OneWorld XE SP15
 
Thanks Larry,

That is nice idea. It is much easier than using VC++ and ADO.

Best regards,

Bojan.
 
Could you please explain in greater detail the steps required to perform item #2:

"2 Create a OneWorld file (FV55xxx) using the DATABASE View's specs (columns, data types ...). "

I created a table using the fields from the data dictionary. But don't I need to somehow relate this table to the underlying business view I created in step #1? If so, how do I do it?


XE RELEASE B7333 SP14.2
 
dsaad,

you need your DBA or DBA simulacrum to create a VIEW in your DBMS containing the data you need. The NAME of the VIEW should be the same name as the bogus table you defined in the Object Librarian. Location of this VIEW needs to be the same owner or schema as the regular production data tables.

Cheers,

Larry Jones
[email protected]
OneWorld B733.1, SP 11.3
HPUX 11, Oracle SE 8.1.6
SandBox: OneWorld XE SP15
 
OMW doesn't let you create two objects (view and table) with the same name. Is there a workaround to this?

XE RELEASE B7333 SP14.2
 
The view is created in the underlying DBMS (whatever it is - your signature failes to include it) - not in OneWorld.

Larry Jones
[email protected]
OneWorld B733.1, SP 11.3
HPUX 11, Oracle SE 8.1.6
SandBox: OneWorld XE SP15
 
David,
Please bear with us; don't get confused by my English (it is THE Second Language for me :)

When I said at #1 Create a DATABASE View, you may get to understand there are other/many ways to create Views.
a) One of them (and that was BEFORE JDEdwards played with World in the sand) is the Database View (such as Oracle or Sybase or infant MS SQL Server);
b) Another View is the (very popular MS Access) Query;
and the list goes on and on with as many views as databases.

The JDE (or OW) Business View is NONE of them!
It is just JDE's way of building SQL Statements in order to retrieve/update/insert data.
Therefore you should consider:
i-Creating a Database View as a join between table Fyyy and Fzzz. That is JOIN #1
ii-Creating a OneWorld table, FV55xxx with the Database View structure
iii-Creating (enfin) the OneWorld Business View that will allow the join of the FV55xxx with your Other table Fooo. That is JOIN #2.
Views are different animals, hunted by different got_to_love_jde predators! Be one of them (with a help from your DBA or such).
Should you have ANY other question, don't keep it with you; allow it to fly free over our JDEList. It will get an answer!

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