QBE for NULL values / Outer join issue

[Gary P]

Hi Listers,

I have a form in our web environment with a grid that runs on a left outer join from table A to table B. I want to use QBE to see records fitting certain criteria, but I have a problem. It seems I can only satisfy one of the below cases, not two.

Case 1:
I want to see records that only match the criteria for table A and a matching record for table B (if I QBE for it). SQL 92 Left outer will give this information just fine. Left outer will give this information, but will also give me records from table A that don't have a record in table B (that is, the field from table B is null). This is a problem because I may QBE for specific records in table B and only want to see those.

Case 2:
I want to see records that fit the criteria for table A but don't have a record in table B (a column in table B is NULL). With a left outer join, I can simply set a QBE column for B to a value I know doesn't exist (no records from B will match that criteria, so it just brings in the NULL records). If I use a SQL 92 left outer, I need to set a QBE column to NULL. However, I haven't been able to find anything on how to do that.

Does anyone know how I can set a QBE value to NULL, or how both cases above can be satisfied?

Thanks,

For system information, we're upgrading to EnterpriseOne 9.0, with the same other information below.

 

[BOster]

You are correct, JDE will not let you do this. JDE will all ways add an "OR COLUMN == NULL" statement to the WHERE clause in the resulting SQL statement when using a left outer join in a BV... very annoying.

You could try using a virtual table (SQL View that has a the same result set as a defined JDE Table) with a left join and additional SQL to return the records you want or some sort of calculated field that flags whether or not a match table B record exists and then QBE this value accordingly.

 

[peterbruce]

Gary,

Another suggestion would be to write ER in the appropriate event that will prevent the data from table B from loading into the grid, unless it is required.

 

[Gary P]

Thank you for your replies. It stinks that there isn't a way to directly QBE for NULL values. The only major problem I've had with JDE is the lack of flexibility it has with the database.

For now, I'm going to keep the left outer join on the tables, as too many records is better than not enough records. If this becomes an issue for the users, I'll set a checkbox that will use set selection in the ER to only show NULL.

Thanks!