• 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!

Is my QBE broken?

Adrian_Chimirel

Legendary Poster
Hi,

First thing first, I'm glad to see you were working (hard) the week I've had a (too) short vacation. Bravo!
Now let's get back to business; please, be patient (as you always are, anyway) and share with me the following:
In Xe, I'm looking for A WAY to fix the QBE on a Find/Browse form.
Whenever the user QBEs a field, the grids gets not only the successful querried records, BUT those with NULL values, too :(
Taking a snapshot onto the debug log, the SELECT clause looks like this:
"... WHERE (T1.ALADD1 LIKE 'DALLAS%' OR T1.ALADD1 IS NULL) ..."
The Form's View is a 3 tables (T0=F0101, T1=F0115 & T2=F0116) Left Outer Join, something like:
F0101.AN8 *= F0115.AN8
F0101.AN8 *= F0116.AN8
F0101.EFTB *= F0116.EFTB and it works fine in B732 :)

Am I missing something here, like a switch or checkbox, somewhere, on the BSVW /Form Designer or ... ?
OR is it just (another) bug to be promptly, Denver(ly) reported?

TIA,



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

Zoltan_Gyimesi

Legendary Poster
Hi Adrian,

First of all, Welcome Aboard!

I am always confused about Right and Left Outer joins, which is which. Is the Left Outer when the Left (main) record exists but the joined does not necessarily and in that case will be defaulted?

If my memory serves me well, then once upon a time I have had similar problems on B7331 (or B7332?) with outer join. Maybe I have read (or haven't?) something about the QBE on Outer Joins on the KG(?) or on the Forums/Lists(?). Unfortunately, I do not remember now exactly, but be sure, I will report it you when it will come into my mind.

I am sure, you very well know, how to work around it with some ER in the Grid Record Fetched or Write Grid-Line Before event using some IF statement and the SuppresGridLine function.

Finally some question (as it is usual :)
Q1.) Does it happens only for the fields of the Outer record or for the fields of the "Left" record too?
Q2.) Could be interesting, how does it work when you try to put one field on the gray header as filter field. Have you tried? If yes, what was the result?
Q3.) Is it really true that the EXACTLY same scenario works differently on B7321 and XE?
Q4.) Have you checked the KG already?

On the other hand, I can create a "philosophy" why does QBE work this manner on Outer fields:
1.) QBE says: Filter on this value
2.) Outer Join says: Bring up Left record even if Outer record does not exist
3.) Engine asks: What to do now? Maybe XE is the right and B7321 is the false?
4.) What is your answer?

Welcome again!

Zoldy
(Zoltán)
P.S.: Here is a final solution from Gordius: Disable the QBE on the Form :)))

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 

Toni_Nanneman

Active Member
We found this problem in many applications when testing Xe. Most calls to
JDE reported that it is working as designed. We did however have a SAR put
in for one application but they said they'd get to it when they could. For
the most part we've had to modify our QBE searches to request specific
values.

Toni Nanneman
Holiday Retirement Corp
503-370-7071 x7262
Toni.Nanneman@hrc-cc.com



XE sp15; coexist on AS/400 with A73 cum 12 x3
 

Adrian_Chimirel

Legendary Poster
Thank you Zoldy & Toni,
I just left the Search SAR/KG screens :) It is a 'bug', indeed. Who knows, the SP17 might fix it ...?
I'll have to work around it, and write some code :)
Thank you,
Ady

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

Zoltan_Gyimesi

Legendary Poster
Hi Adrian,

Let me some further question because this issue interest me too.

1.) Wold you please point to where is this bug described on the KG (SAR, Search text and options, document number, etc.)?
2.) Is it an OW release or SP level related problem?
3.) Is it problem only for the fields of the "Outer" record or for the fields of the "Left" record too?
4.) Have you tried the filter field method on the header? If yes, how does it work? Does it work properly?
5.) Please, let us know how did you work around this problem after you have done it.

Thanks for your updates in advance,

Zoldy
(Zoltán)

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 

vbojan

Well Known Member
Hi Adrian,

I have a same problem no B733.2. You can fix that with a filter and checkbox field. Put one filter field for F0115 and one for F0116. Make them both “GreaterThanOrEqual”. Put a checkbox like “Only Existing Records”. If checkbox is on put blank into filter fields otherwise put Null into filter fields. Blank is greater than Null and “GreaterThanOrEqual” than blank should exclude non-matching records from grid. At least on B733.2, SP10, SQL 7.0, NT 4.0.

Hope this helps,

Bojan.
 

Adrian_Chimirel

Legendary Poster
Servus Zoltán,

I'll be brief here; can't wait to get Bojan's ideas ...
1.) & 3.) SAR 5413402 Left Outer Join/Null "... when doing selection on a field from a child table, an additional statement of
[OR Field Is Null] is appended to the generated SQL statement. ..." is of type 2 - Enhancement! and its status 11 - Pending Review puts you on the waiting list.
2.) SAR 5231393 Left Outer Join & Row Security System at least shows the common sense to be of type 1 - Correction, and is supposed to be addressed in the future SP17 & B9
4.) See Bojan's reply
5.) I tried to modify the Record Is Fetched event with those If/Suppress and don't ask me about the results; apart from the performance issue (and you may have a cigarette waiting for results - only 200 records, that's ridiculous), there's no way to see the records we want.
ex: If QC Add Line 1 <> blank AND BC Add Line 1 = blank Suppress GridLine
PS1. We need to enable QBE for 8 fields from F0116 (address related) and 3 from F0115 (of course, phone info)

I definitely will keep the Forum posted with my results :)

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

Adrian_Chimirel

Legendary Poster
Hi Bojan,

I created only one filter on F0116 and added the CheckBox. When ChkBx is ON, I blanked the filter.
Unfortunately, the Select statement still gets appended (twice now) with (OR field IS NULL) ... :(
" ... WHERE (Filter=> " " OR Filter IS NULL) AND (QBEfield ='xyz%' OR QBEfield IS NULL) ... "
Is it because of Oracle? I really can't say. Thank you anyway, it worthed to give it a try.

Warm regards,


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

vbojan

Well Known Member
Hi Adrian,

I am really sorry to hear that. Can be translator for Oracle or different SP or… There is another idea. On MS SQL you can make a query you need that is looking like a JDE table. Put a calculated fields like “If IsNull(F0116.ALAN8) Then 1 Else 0 Endif AS XXEV01”(this is not correct T-SQL syntax, just a description) where “XX” is table prefix for the new JDE table you have to create. Somebody says that you don’t need to generate that table new but I always generate virtual table and delete that table later trough MS SQL. After query has been generated make a Business View based on virtual table and use that Business View for your form. Make a filter on XXEV01 field “equal” 0. Of course you can’t add new record but we talking about Find/Browse form. Unfortunately I don’t have Oracle and I can’t test this. Main idea is to let database system (Oracle in your case) handle nulls instead of JDE (JDE is not really good with nulls) and set another non-null parameter that JDE is able to handle. Please don’t hesitate to let me know if you have some question about this.

Good luck with nulls,

Bojan.
 
Top