JBossDeveloper

I apologize.

Let's bring this social NPE to an end:

I apologize too for sounding anything else than helping. I neither know what "revendicative" in German is [babelfish and dict.leo.org doesn't help here] nor does I know _how_ to write or say something that it is sounding condescending in english - in German, you could be very, very sure, i’ll know. I want never ever accusing people - IMNSHO I accusing the design of RDBMSes, because I, like many other out here, make money with healing this sucking designs by implementing J2EE Apps on top of it, instead of repair this crap - but it is definitely no fun. And even I will have fun with my day-to-day work, seriously.

So at last I have to apologize to not be an native english speaker. Nice. And, yes, here I am still a newbeee. As shown. My fault.

OTOH it makes me wondering, that at most young people are afraid of beeing criticised. Often there is no understanding for the difference of saying: "Your ER model sucks" and "You sucks". How less could one stand on his own if he feels attacked if one attacks his ER model? And how arrogant and ignorant ?

Ok, let's take this case straight:

Precondition:

We have one table in a relational DB with four colums:

ID Name City Street Zip

ID is the PK
Name is a NOT NULL column
City is a nullable column
Street is a nullable column
Zip is a nullable column

I understand the problem as follows:

ID Name City Street Zip
1 Miller Frisco
2 Frank Way 10000
3 Paul Frisco 10000
4 Smith Ave.

Simple case Query: Give me all Name, where the Zip is 10000 (Zip is $1) or Zip is not existing ($1 is null)

This is a self-outer-join on the table.

EJBQL: SELECT DISTINCT OBJECT(t) FROM TheTable t WHERE t.Zip IS NULL OR t.Zip =?1

The “table.col = ?1 OR (table.col IS NULL AND null=?1)” does not work, because there will occur a short-circuit to “table.col = ?1“, because of ‘NULL=anything’ is always false.

The “business key” - a concept which does not exist on DB level - consists now on City, Street and Zip. The query should deliver all three columns, regardless if they are NULL or not.

Right so far ?

Problem Query: Give me all Name where Zip is 10000 or not existent and give me all Name where City is Frisco or not existent.

So expected result should show:

Name
Miller
Frank
Paul

What I would suggest in this case is to build a Frisco_view and Zip10000_view:

Beforehand: This is a workaround for not changing the underlying DB-model, otherwise there should be a ID_CityTable and a ID_ZipTable - there should than performed outer joins with the TheTable, but this is DB and DB-version (oracle I know here) dependend, because the outer joins (left, right, full) are implemented differently in syntax and function by the vendors.

Now the ugly workaround:

Create View Frisco_view as
Select ID, City from TheTable where City=Frisco or City IS NULL;

Create View Zip10000_view as
Select ID, Zip from TheTable where Zip=10000 or Zip IS NULL;

Create View MultipleSelfOuterJoin_Frisco_10000_Query as
Select tt.ID, tt.Name, zv.Zip, fv.City from
TheTable tt, Frisco_view fv, Zip10000_view zv where
(tt.ID = fv.ID) and (tt.ID = zv) and (fv.ID = zv.ID)

Select Name from MultipleSelfOuterJoin_Frisco_10000_Query;

Depending on the RDBMS, it might happen that the views beeing materialized by the optimizer.

... and, yes, there have DDL statements dynamically, at runtime, to be performed. It is a workaround.

Ref: Joe Celko’s SQL Puzzles & Answers, Morgan Kaufmann 1997, ISBN1-55860-453-7; Puzzle 14: Telephone

BTW: the Subject itself is a oxymoron: CMR should provide results where something is existent _and_ not existent according to SQL. Most OR tools are struggling with this structural weakness of the relational world.

bax

revendicative means "claiming your truth at the expense of those of others"
This forum is for helping each other to make everyone find its truth (means the way where its application works). Blaming J2EE, JBoss, ER designers or ER models got nothing to do with this forum.
So, finallly, make a choice between a DynamicSQL statement or adding views, or whatever belongs to Erik.
Nothing we can do more than that.

Thanks.

Then I apologize for sounding revendicative. Never want to draw upon the objective truth, know that this is not existing. My truth is simply my truth, not more - but not less.

bax

Thanks for your kind reply :)))

Mhhhm, i do not see an end here either.

The Java vs. DB NULL problem is an chicken-and-egg problem. The NULL in the DB is not the Java NULL, so it must be converted in both directions. That for you have to test if it is NULL. Circle closed.

Do you have the full control over the Table and Relation layout?
If you provide more detailed information about what you are modeling, I am sure there is a way like the address splitting approach. Not the one-size-fits-it-all, but here we have a special case, which should be solved in a general manner. Have done it the last 25 years ;-)

bax

Uhu. Aha.

bax