• JDELIST is celebrating its 20 year anniversary today!
  • Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

Query on table joins in BSVW

anand1991

Member
Hi All,

I have 3 tables to be joined in BSVW.
Let us suppose three tables be A,B,C.
The requirement is that the table a has TYPS field.While running he report if user gives TYPS=6,the DOCO field from table A should be mapped with WONA of C table and if user gives TYPS=M,then the DOCO field from table A should be mapped with DOCO of C table.
Is this possible as far as i know the BSVW are not dynamic.Once the mapping is done in BSVW design aid,it cannot be changed runtime.
Is there other way round to do so.
Please help me.
 

Chan Rana

Legendary Poster
Through some twik you can use the report sub section joins to achive it. You might have to define multiple sections though.

Chan
 

DBohner-(db)

Legendary Poster
Howdy,

Yes, there is a trick, but you have to know what you are doing.

The concept is advanced, and not for those that like to live in the box we call E1 (sometimes, we have to open the doors and go for a walk).

First, make sure you understand the concept of a Virtual Table and of the "SQL Injector"
http://www.jderesearch.com/tips-traps/sql-injection


- In TDA, create a Table Structure that will contain all the columns you will want for your report (Do Not Generate)
- Create a BSVW over that table,
- Write your report over that BSVW

Create a Script that drops an SQL View, and ReCreates the SQL View with the same exact View Name as your Virtual Table and Exact Column Names / Sequence of Columns. AGAIN, Make sure you Sequence and Rename the Columns in the SQL View to EXACTLY match that of you Virtual Table!

Run the Script from the Report Initialization Section of your report. This will re-create the SQL View (Virtual Table) every time the report is run. If you want different data in the Virtual Table, adjust your script (or run different scripts).

Only run the report Single-Threaded

This will allow you to define your SQL View Data in almost any configuration, as long as it exactly matches your Virtual Table layout - and run a UBE over it.

Enjoy!

(db)
 

BOster

Legendary Poster
You can do this with NEWSELECTSTRUCT and JDB_SetSelectionX - basically completing the join at run time but this would be in a BSFN and not for the BSVW for a UBE section so you would have to write the results to a work table in your BSFN and then have your UBE section display the work table records.

Basically in the BSVW you need to have at least one field that is statically joined and then at run-time you complete the join with JDB_SetSelectionX. Having said that I did have a developer that was able to create a BSVW that didn't have any joins defined and he did the full join at run-time but he was using what was probably a bug in the BSVW design aid to save the BSVW in that state so I wouldn't recommend trying to create a BSVW with no join defined.
 

nkuebelbeck

VIP Member
I don't think what you are attempting is possible without doing what dbohner has suggested

If you can live in the "E1 Box"

I would typically use multiple custom sections with the field (in your case TYPS) be a processing options.

So
if TYPS == 6 {
Do Custom Section (Table A (where WONO == 6 defined in init section)
}

another attack would be to just create different ube's with different views for each combination and UBE interconnect to the one you want
 

johndanter

Legendary Poster
Or just do it the simple way

Create the 2 BSVWs and use which ever one based on a PO value. The driver section should contain all the BC fields you would want
 
Last edited:
Top