E9.2 Filter does not work when view has left outer join

[Amith]

Hi All,

My business view is based on F4801(primary) and F3112 tables with left outer join. I need all rows from F4801 and common rows from F3112.
In an application i am using this view and when i add F3112.MCU in form and applied filters in properties and test its showing all the records even though i give a MCU value and click find.
Basically it does not filter the records on F3112 fields. when i change it to simple join it works perfectly.

Any solution for this issue, if not is there any alternative solution?

Thanks
Amith
JDE E1 9.2.5.6

 

[mdalton]

Hi Amith,

What does the left outer join do for you in this scenario? A left outer join between two tables (in your case, F4801 and F3112) returns all rows from the left table (F4801) and the matched rows from the right table (F3112). If there is no match, the result is NULL on the side of the right table.

What is the view being used for? It sounds like a JOIN will meet your use case.

Best,

Mark

 

[alfredorz]

Hi Amith,

You can create a database view and link with JDE as table, and filter by any field.

Regards.

 

[Amith]

Hi Amith,

What does the left outer join do for you in this scenario? A left outer join between two tables (in your case, F4801 and F3112) returns all rows from the left table (F4801) and the matched rows from the right table (F3112). If there is no match, the result is NULL on the side of the right table.

What is the view being used for? It sounds like a JOIN will meet your use case.

Best,

Mark

Hi Mark,
The join is on DOCO, basically the data is showing correctly but the user wants to filter on F3112.MCU. somehow it does not consider this filter and shows all rows.
The requirement is to see F3112.MCU in the standard Appl P31225 and filter on it. So adding table F3112 to standard view V4801JG.

Thanks
Amith

 

[Amith]

Hi Amith,

You can create a database view and link with JDE as table, and filter by any field.

Regards.

Hi Alfredorz,

We will not be able to do this, as we are modifying standard Appl P31225, and adding table F3112 to standard view V4801JG.
The requirement is to see F3112.MCU in the standard Appl P31225 and filter on it..

Thanks
Amith

 

[DaveWagoner]

Hi All,

My business view is based on F4801(primary) and F3112 tables with left outer join. I need all rows from F4801 and common rows from F3112.
In an application i am using this view and when i add F3112.MCU in form and applied filters in properties and test its showing all the records even though i give a MCU value and click find.
Basically it does not filter the records on F3112 fields. when i change it to simple join it works perfectly.

Any solution for this issue, if not is there any alternative solution?

Thanks
Amith
JDE E1 9.2.5.6

If you're using standard filtering on a field that might be null, in the case of a left outer join, then all bets are off within JDE tools JDE and true nulls don't play nice.

Try @alfredorz solution, making sure that your "derived virtual table" lists BLANKS instead of NULLS in the case of text fields such as MCU, and 0 instead of NULLS in the case of numeric fields such as UPMJ

What db are you on? I have some instructions I'm happy to share from AS400 platform.

 

[mdalton]

Hi Mark,
The join is on DOCO, basically the data is showing correctly but the user wants to filter on F3112.MCU. somehow it does not consider this filter and shows all rows.
The requirement is to see F3112.MCU in the standard Appl P31225 and filter on it. So adding table F3112 to standard view V4801JG.

Thanks
Amith

Hi Amith,

Because of how the left outer joins work, rows from F4801 without matching F3112.MCU values will be included in your dataset, just with null values for fields coming from F3112. Your best option is to use a simple JOIN.

I would also copy any base JDE artifact and put it into a 55 system code. Modifying delivered Applications and Business Views can have unintended negative outcomes on process and system performance.

Best,

Mark

 

[Amith]

If you're using standard filtering on a field that might be null, in the case of a left outer join, then all bets are off within JDE tools JDE and true nulls don't play nice.

Try @alfredorz solution, making sure that your "derived virtual table" lists BLANKS instead of NULLS in the case of text fields such as MCU, and 0 instead of NULLS in the case of numeric fields such as UPMJ

What db are you on? I have some instructions I'm happy to share from AS400 platform.

Hi Dave,

We are using Oracle db, please do let me know if you have any inputs.

Thanks
Amith

 

[Amith]

Hi Amith,

Because of how the left outer joins work, rows from F4801 without matching F3112.MCU values will be included in your dataset, just with null values for fields coming from F3112. Your best option is to use a simple JOIN.

I would also copy any base JDE artifact and put it into a 55 system code. Modifying delivered Applications and Business Views can have unintended negative outcomes on process and system performance.

Best,

Mark

Hi Mark,

Making it a copy was our first choice, but this application P31225 is called in 22 different locations and all are standard. And by copying to 55 we need to modify all those 22 places. So with no choice we decided to modify the standard itself.

Ya looks like simple join is the only option, but that will not give me all records in F4801. I also tried to use set selection system function but it did work.

Thank you for your time.

 

[DaveWagoner]

Hi Dave,

We are using Oracle db, please do let me know if you have any inputs.

Thanks
Amith

I'll leave it to oracle sql people to help you with the query, but the gist is this (which I learned from Kevin Courtney many years ago):

1) Write your SQL query, and take care to name your fields according to the data dictionary types, and assign a proper prefix. Here you would make sure you're taking care of all potential NULL values by replacing with either space or zero. I'd do that in AS400 SQL using a case or if statement. SELECT F4211.SDDOCO AS DWDOCO,F4201.SHDCTO AS DWDCTO FROM F4201 LEFT OUTER JOIN F4211 ON SHDOCO=SDDOCO AND SHDCTO=SDDCTO
2) Create a JDE table definition giving it a name like F55****V or VF55***** using dev tools with the same table prefix that you specified in previous step, and the same columns in the same order.
3) DO NOT continue with table creation by actually creating the table.
4) Generate a SQL view in your data library, giving it the same name you chose earlier
5) Might require a build for everything to work together, but the tablename should point to the SQL view, and JDE should be none the wiser.

 

[Neal Scarr]

Hi Mark,
The join is on DOCO, basically the data is showing correctly but the user wants to filter on F3112.MCU. somehow it does not consider this filter and shows all rows.
The requirement is to see F3112.MCU in the standard Appl P31225 and filter on it. So adding table F3112 to standard view V4801JG.

Thanks
Amith

Hi Amith,

You should be extremely careful when adding tables to standard views.

Changing a standard view could cause unexpected results in other applications or reports that use the view, especially on applications that allow deletes on grids based over the view (records could get deleted from the added table).

It may not be an issue with V4801JG, but it's something to bear in mind in the future.

Regards,

Neal

 

[Amith]

I'll leave it to oracle sql people to help you with the query, but the gist is this (which I learned from Kevin Courtney many years ago):

1) Write your SQL query, and take care to name your fields according to the data dictionary types, and assign a proper prefix. Here you would make sure you're taking care of all potential NULL values by replacing with either space or zero. I'd do that in AS400 SQL using a case or if statement. SELECT F4211.SDDOCO AS DWDOCO,F4201.SHDCTO AS DWDCTO FROM F4201 LEFT OUTER JOIN F4211 ON SHDOCO=SDDOCO AND SHDCTO=SDDCTO
2) Create a JDE table definition giving it a name like F55****V or VF55***** using dev tools with the same table prefix that you specified in previous step, and the same columns in the same order.
3) DO NOT continue with table creation by actually creating the table.
4) Generate a SQL view in your data library, giving it the same name you chose earlier
5) Might require a build for everything to work together, but the tablename should point to the SQL view, and JDE should be none the wiser.

Thank you Dave for your inputs

 

[Amith]

Hi Amith,

You should be extremely careful when adding tables to standard views.

Changing a standard view could cause unexpected results in other applications or reports that use the view, especially on applications that allow deletes on grids based over the view (records could get deleted from the added table).

It may not be an issue with V4801JG, but it's something to bear in mind in the future.

Regards,

Neal

Hi Neal,

Ya i agree with you, now we are planning to drop this approach.

 

[JohnDanter2]

V31122C is a left outer over those 2 tables btw but aslso F31122

but....I do this all the time. Especially with F3111 and F4108. You need to add MCU to your left outer join expression in the BSVW

Join the tables on DOCO but also on MCU from the F4801 (MMCU to F3112 MCU ) and filter on the F4801 MMCU not the F3112 MCU

The issue you have is any where clause that comes after a view declares joins is classed as a simple join.
see this https://www.jdelist.com/community/threads/bsvw-left-outer-join-behaviour.58465/

As Neal says, check XREF to see where else it's used as you're now affecting other objects behaviour

 

[JohnDanter2]

Sorry, I got the MMCUs the wrong way around, but the rest of the post is correct. Any where clause outside the brackets of the join get treated as a simple join

Could you try an ER filter, so add the form cotrol, dont' setup as a true filter and ask in grid record is fetched

 

[Amith]

Sorry, I got the MMCUs the wrong way around, but the rest of the post is correct. Any where clause outside the brackets of the join get treated as a simple join

Could you try an ER filter, so add the form cotrol, dont' setup as a true filter and ask in grid record is fetched

Hi John,

We cannot join the F4801 (MMCU to F3112 MCU ) as F3112.MCU is storing work center to be moved, so both will be different.
Ya i tried the ER filter aswell like F3112.DOCO > 0 & F3112.MCU = FC MCU. Still its showing all the rows.

Thanks
Amith

 

[JohnDanter2]

Whats the SQL your app is generating when you click Find?

 

[JohnDanter2]

Also.....
Do you really need a left outer on F3112? You can do all you need using the existing view but use a header field to filter on it (not the QBE)

I am not 100% why this is causing issues as I do this all the time, but maybe just try that and move on. Change the view back and just grabn the F3112 fields in the write grid line before event and 'filter' that way

 

[alfredorz]

I agree with the colleagues that you should be care modify standards views, you can use a view share with other objects


The best way is create a custom view and application, and into your application call to P31225.

Regards.

 

[Amith]

Also.....
Do you really need a left outer on F3112? You can do all you need using the existing view but use a header field to filter on it (not the QBE)

I am not 100% why this is causing issues as I do this all the time, but maybe just try that and move on. Change the view back and just grabn the F3112 fields in the write grid line before event and 'filter' that way

Hi John,

Regarding the SQL in logs, its just showing F4801 fields in WHERE clause.
I tried getting the F3112.MCU through fetch and suppress grid line based on value given in form control(MCU). The performance of the application is effected, its taking long time to load the data to grid.

Anyways, we decided to copy the standard and use simple join. Thank you so much for all your inputs.

Thanks
Amith