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
View the original post :
http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836604#3836604
Reply to the post :
http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836604
-------------------------------------------------------
This SF.Net email is sponsored by: Oracle 10g
Get certified on the hottest thing ever to hit the market... Oracle 10g.
Take an Oracle 10g class now, and we'll give you the exam FREE.
http://ads.osdn.com/?ad_id=3149&alloc_id=8166&op=click
_______________________________________________
JBoss-user mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-user
