I have only worked with Oracle and Postgresql. Any time I have a problem like this, I create a view on the database that does half the query. Or in your case, the ‘view’ could do all the heavy lifting.
It’s an idea. Ted > On Feb 14, 2022, at 1:13 AM, ocs--- via Webobjects-dev > <webobjects-dev@lists.apple.com> wrote: > > Hi there, > > lately, I am encountering wrong fetches (with FrontBase, if important). I log > out my qualifier and SQL, and the gist of the problem is a join in the > OR-part of the qualifier. I want to fetch rows where there's an empty > attribute, OR when there's a specific join: > > 06:52:06.510 DEBUG -> ((savedPresentationEndDate = null) or > (lastValidPriceOfferCache.user = (model.DBUser)'[rc/Registration > centre#1000004]')) > > Alas, SQL-level, it boils down to something entirely different: > > 06:52:06.535 DEBUG "DBAuction"@318794136 expression took 1 ms: SELECT ... > FROM "T_AUCTION" t0, "T_PRICE_OFFER" T1 WHERE (T1."C_CREATOR_ID" = 1000004 OR > t0."C_PRESENTATION_END_DATE" is NULL) AND t0."C_LAST_VALID_PRICE_OFFER_CACHE" > = T1."C_UID" > > The SQL generator properly sets up the OR checking the right target PK, but > then, instead of placing the join into the OR-part where it belongs to, it > forces the join absolute to the entire condition — even to the NULL-check > which should be completely independent. > > That self-evidently is not what I need here: if t0."C_PRESENTATION_END_DATE" > is NULL, I want to fetch the row regardless of whatever vaue there is or is > not in the C_LAST_VALID_PRICE_OFFER_CACHE foreign key. Actually, if there > happens to be NULL in C_PRESENTATION_END_DATE, I would prefer if the > SQL-level would not try to join at all, for it is self-evidently superfluous. > I've tried manually > > SELECT * FROM "T_AUCTION" t0, "T_PRICE_OFFER" T1 WHERE > (t0."C_PRESENTATION_END_DATE" is NULL) OR (T1."C_CREATOR_ID" = 1000004 AND > t0."C_LAST_VALID_PRICE_OFFER_CACHE" = T1."C_UID") > > which is what, I believe, the SQL generator should have created from the > qualifier, and it seems to work (at least, it produces no error; I cannot > easily check whether the rows returned are OK. but they seem to t the first > look). > > Can perhaps anybody see how to fix the problem? (But for by generating > directly my SQL at the application level, which I can do if need be, but > would rather not, unless really the only way.) > > Thanks, > OC > > _______________________________________________ > Do not post admin requests to the list. They will be ignored. > Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) > Help/Unsubscribe/Update your Subscription: > https://lists.apple.com/mailman/options/webobjects-dev/tedpet5%40yahoo.com > > This email sent to tedp...@yahoo.com
_______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
