BSVW Table Joins

David Robertson

David Robertson

Reputable Poster
I am trying to create a Business View over 4 tables, and a particular data selection to drive a UBE.

The problem I am having is to create the equivalent of brackets ( ) around an "OR" clause within the select statement. I can create the equivalent SQL easily enough, but cannot replicate the data selection in OW. Without being able to do this, the selection criteria becomes extremely long and unwieldy.

Does anyone have any ideas how to get around this limitation?

An example format of the SQL command is as follows:
select * from F03012, F550301, F0006, F0101
where AICO=CCCO and AIAN8=ABAN8 and ABMCU=MCMCU and CCCO=MCCO
and (CCMCU='*' or CCMCU=MCMCU)
and (CCRP24='*' or CCRP24=MCRP24)
and (CCRP25='*' or CCRP25=MCRP25)
and (CCRP26='*' or CCRP26=MCRP26)
and (CCRP27='*' or CCRP27=MCRP27)
order by AICO, CCRP27, CCRP26, CCRP25, CCRP24
 
You may have to rethink the UBE. You could play
around with outer joins in the BSVW, but if the
limitation still applies, you can only join 3 tables
when outers are used.

What about multiple sections in the UBE? - maybe a
parent/child? It will be slower though. Your data
selections could either be entered directly against
the section(s) or in the ER.

To limit the slower performance, create a BSVW (within
JDE's limitations) that will perform the "most likely"
data selections and read the fourth table in the UBE.
Just an example.

Regards

Lee




--- DavidRobertson <[email protected]> wrote: > I am
trying to create a Business View over 4 tables,
> and a particular data selection to drive a UBE.
>
> The problem I am having is to create the equivalent
> of brackets ( ) around an "OR" clause within the
> select statement. I can create the equivalent SQL
> easily enough, but cannot replicate the data
> selection in OW. Without being able to do this, the
> selection criteria becomes extremely long and
> unwieldy.
>
> Does anyone have any ideas how to get around this
> limitation?
>
> An example format of the SQL command is as follows:
> select * from F03012, F550301, F0006, F0101
> where AICO=CCCO and AIAN8=ABAN8 and ABMCU=MCMCU and
> CCCO=MCCO
> and (CCMCU='*' or CCMCU=MCMCU)
> and (CCRP24='*' or CCRP24=MCRP24)
> and (CCRP25='*' or CCRP25=MCRP25)
> and (CCRP26='*' or CCRP26=MCRP26)
> and (CCRP27='*' or CCRP27=MCRP27)
> order by AICO, CCRP27, CCRP26, CCRP25, CCRP24
>
>
> --------------------------
> Visit the forum to view this thread at:
>
http://198.144.193.139/cgi-bin/wwwthreads/showflat.pl?Cat=&Board=OWDEV&Number=1553
>
>
*************************************************************
> This is the JDEList One World / XE Developers
> Mailing List.
> Archives and information on how to SUBSCRIBE, and
> UNSUBSCRIBE can be found at http://www.JDELIST.com
>
*************************************************************
>


__________________________________________________
Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products.
http://shopping.yahoo.com/
 
Have yor tried to use "List of Values" for your Data Selection?

Victor Juang
System/Programmer Analyst
Virtual ESI
Enterprise Solutions & Innovations
email - [email protected]




DavidRobertson
<[email protected] To: [email protected]
et> cc:
Sent by: Subject: BSVW Table Joins ~~0:1553
owner-jdeowdevml@j
delist.com


11/22/00 05:14 AM
Please respond to
jdeowdev





I am trying to create a Business View over 4 tables, and a particular data
selection to drive a UBE.

The problem I am having is to create the equivalent of brackets ( ) around
an "OR" clause within the select statement. I can create the equivalent
SQL easily enough, but cannot replicate the data selection in OW. Without
being able to do this, the selection criteria becomes extremely long and
unwieldy.

Does anyone have any ideas how to get around this limitation?

An example format of the SQL command is as follows:
select * from F03012, F550301, F0006, F0101
where AICO=CCCO and AIAN8=ABAN8 and ABMCU=MCMCU and CCCO=MCCO
and (CCMCU='*' or CCMCU=MCMCU)
and (CCRP24='*' or CCRP24=MCRP24)
and (CCRP25='*' or CCRP25=MCRP25)
and (CCRP26='*' or CCRP26=MCRP26)
and (CCRP27='*' or CCRP27=MCRP27)
order by AICO, CCRP27, CCRP26, CCRP25, CCRP24


--------------------------
Visit the forum to view this thread at:
http://198.144.193.139/cgi-bin/wwwthreads/showflat.pl?Cat
=&Board=OWDEV&Number=1553
*************************************************************
This is the JDEList One World / XE Developers Mailing List.
Archives and information on how to SUBSCRIBE, and
UNSUBSCRIBE can be found at http://www.JDELIST.com
*************************************************************
 
Have you tried to use "List of Values" for your Data Selection?

Victor Juang
System/Programmer Analyst
Virtual ESI
Enterprise Solutions & Innovations
email - [email protected]





DavidRobertson
<[email protected] To: [email protected]
et> cc:
Sent by: Subject: BSVW Table Joins ~~0:1553
owner-jdeowdevml@j
delist.com


11/22/00 05:14 AM
Please respond to
jdeowdev





I am trying to create a Business View over 4 tables, and a particular data
selection to drive a UBE.

The problem I am having is to create the equivalent of brackets ( ) around
an "OR" clause within the select statement. I can create the equivalent
SQL easily enough, but cannot replicate the data selection in OW. Without
being able to do this, the selection criteria becomes extremely long and
unwieldy.

Does anyone have any ideas how to get around this limitation?

An example format of the SQL command is as follows:
select * from F03012, F550301, F0006, F0101
where AICO=CCCO and AIAN8=ABAN8 and ABMCU=MCMCU and CCCO=MCCO
and (CCMCU='*' or CCMCU=MCMCU)
and (CCRP24='*' or CCRP24=MCRP24)
and (CCRP25='*' or CCRP25=MCRP25)
and (CCRP26='*' or CCRP26=MCRP26)
and (CCRP27='*' or CCRP27=MCRP27)
order by AICO, CCRP27, CCRP26, CCRP25, CCRP24


--------------------------
Visit the forum to view this thread at:
http://198.144.193.139/cgi-bin/wwwthreads/showflat.pl?Cat
=&Board=OWDEV&Number=1553
*************************************************************
This is the JDEList One World / XE Developers Mailing List.
Archives and information on how to SUBSCRIBE, and
UNSUBSCRIBE can be found at http://www.JDELIST.com
*************************************************************
 
I would recommend looking at narrowing the number of tables you want to join
for the view (this will make the UBE much faster). Also, use table I/O's
for the information you need from the other tables that don't need to be
included in the UBE's data selection.
Good luck!
-Harry
 
Re: RE: BSVW Table Joins

Sorry, but I don't see how this actually answers the question. How does narrowing the number of files "make the UBE much faster"? With the 4 files joined, I am looking at processing around 80,000 records. Joining 3 files and fetching (slow) rows from the 4th results in reading over 1,000,000 records. The whole point of doing the join is to speed up the processing.

The best suggestion so far has been to create a dummy table in OW (with no index?), with all of the columns as required by the join. Then go to the DB and DROP TABLE, and CREATE VIEW with the same name and columns. Then create a business view over this in OW. I'm still in the process of testing this.

There is a new API JDB_SetSelectionX, which according o the documentation extends the abilities of JDB_SetSelection to allow for precedence in the WHERE clause. Other than creating custom BSFN's, I can't see anyway of calling this API.
 
David,
I'm not really experienced in RDA but I have a maybe silly idea.
Also maybe it is not implementable or does not fit to your needs even so I put it here.

Define your selection criteria in a SQL view outside of OneWorld.
Make this view accessible in OneWorld as "Foreign Table".
If your task is a something like from table to table task then you can it do now with a Table Conversion UBE if the logging possibility is enough for you as documentation on the process.

You can also use Table Conversion (based on the "Foreing Table" input) as a pre-processor to make easy and very efficiently a work table for the further actions.

Please, let me/us to know, does it work for you or doesn't! Thanks.
P.S.: Place your system configuration information in your signature of your profile or attach them to your issues next time. Tips, ideas possible solutions could depend on and vary.
Good luck,
Zoltán



B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
How did you join the tables in the business view? If it are als simple joins, the maximum number of tables you can use is 5, but if you use outer-joins or unions, the maximum tables you can use is 3 altough I think Business View design does not give you an error!

Marcel.

DavidRobertson wrote:

> I am trying to create a Business View over 4 tables, and a particular data selection to drive a UBE.
>
> The problem I am having is to create the equivalent of brackets ( ) around an "OR" clause within the select statement. I can create the equivalent SQL easily enough, but cannot replicate the data selection in OW. Without being able to do this, the selection criteria becomes extremely long and unwieldy.
>
> Does anyone have any ideas how to get around this limitation?
>
> An example format of the SQL command is as follows:
> select * from F03012, F550301, F0006, F0101
> where AICO=CCCO and AIAN8=ABAN8 and ABMCU=MCMCU and CCCO=MCCO
> and (CCMCU='*' or CCMCU=MCMCU)
> and (CCRP24='*' or CCRP24=MCRP24)
> and (CCRP25='*' or CCRP25=MCRP25)
> and (CCRP26='*' or CCRP26=MCRP26)
> and (CCRP27='*' or CCRP27=MCRP27)
> order by AICO, CCRP27, CCRP26, CCRP25, CCRP24
>
> --------------------------
> Visit the forum to view this thread at:
> http://198.144.193.139/cgi-bin/wwwthreads/showflat.pl?Cat=&Board=OWDEV&Number=1553
> *************************************************************
> This is the JDEList One World / XE Developers Mailing List.
> Archives and information on how to SUBSCRIBE, and
> UNSUBSCRIBE can be found at http://www.JDELIST.com
> *************************************************************
 
Re: RE: BSVW Table Joins

Hey Dave,

regarding the impact on performance of reducing the tables in the view and using ER code to to perform additional data I-O and record rejection.

It may not be slower than letting OneWorld try to handle all of it in one view. I don't believe you can assume that OneWorld posesses the benefits of a Relational Database in this regard. It may in fact be handling joins itself in many cases rather than constructing a single SQL statement. It certainly has to do this in cases where tables are not in the same database (OCM mappings, etc). So whether your code is processing a million records or JDE's code is processing a million records may not make a significant difference in performance. In fact, my experience is to not trust OW views with that many tables in them. You'll note that there are few if any JDE views that processing is based on that use that many tables. Wonder why ... :)

The approach of creating a database view and treating it as a custom or external table should certainly work and will probably have the best performance. Its a lot of extra work - but if you have millions of records to process I suppose it will pay off for you.

Cheers.

Larry Jones
[email protected]
OneWorld B733.1, SP 11.3
HPUX 11.0, Oracle SE 8.0.5
 

Similar threads

David Robertson
Replies
6
Views
2K
David Robertson
David Robertson
Back
Top