cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From urugn urugn <ur...@yahoo.com>
Subject Handling search for null relationship
Date Wed, 18 Jul 2007 23:01:15 GMT
Hi guys.

Let me start by saying that am very greateful. This
coz of what the cay team has made things simple and
first.

Am just having a small problem which i think might
have a simple solution, but can't figure it out. Am
trying to query from a table T1 that has two objects
T2 and T3 where if one object(lets say T2) is null the
other is not null. Each of this two objects contain a
similar object in them lets say T4 which i want to
access through a search query. Here is the code thats
is in place.



StringBuffer b = new StringBuffer();
        b.append("policy.client.clientName
likeIgnoreCase $clientName ");
        b.append("or marine.client.clientName
likeIgnoreCase $clientName ");
        b.append("and claimDetail.settled = $settled
and claimDetail.recovery = $recovery ");

Expression e1 = Expression.fromString(b.toString());
        java.util.Map map = new java.util.HashMap();
        map.put("clientName", 
searchField.getText().toLowerCase()+"%" );
      Expression e2 = e1.expWithParameters(map);

SelectQuery query = new
SelectQuery(eBrokerClaim.class, e2);
            
            List list = context.performQuery(query);
         ......
        ................

I get the following debug.

02:12:18,888  INFO QueryLogger:423 - --- will run 1
query.
02:12:18,889  INFO QueryLogger:377 - --- transaction
started.
02:12:18,891  INFO QueryLogger:300 - SELECT
t0.CLAIM_ASSESSOR_UNIQUE, t0.CLAIM_PRIORITY_CHEQUE,
t0.CLAIM_CAUSE, t0.CLAIM_NUMBER, t0.CLAIM_TYPE_UNIQUE,
t0.CLAIM_PRIORITY_CREDITNOTE, t0.CLAIM_POST_DATE,
t0.CLAIM_PRIORITY_DV, t0.CLAIM_PRIORITY_HIGH,
t0.CLAIM_ID, t0.CLAIM_PRIORITY_INBOUND,
t0.CLAIM_INSURER_INTIMATION_DATE,
t0.CLAIM_INTIMATION_EMAIL, t0.CLAIM_INTIMATION_FAX,
t0.CLAIM_INTIMATION_LETTER, t0.CLAIM_INTIMATION_PHONE,
t0.CLAIM_INTIMATION_DATE, t0.CLAIM_LETTER_UNIQUE,
t0.CLAIM_LOSS_DATE, t0.CLAIM_LOSS_ESTIMATE,
t0.CLAIM_PRIORITY_LOW, t0.CLAIM_MARINE_UNIQUE,
t0.CLAIM_PRIORITY_MEDIUM, t0.CLAIM_PARTICULAR,
t0.CLAIM_POLICY_UNIQUE, t0.CLAIM_REFERENCE,
t0.CLAIM_REPORTED_BY, t0.CLAIM_REPORTED_TO,
t0.CLAIM_TIME FROM EBROKER.EBROKER_CLAIMS t0,
EBROKER.EBROKER_POLICIES t1, EBROKER.EBROKER_CLIENTS
t2, EBROKER.EBROKER_MARINES t3,
EBROKER.EBROKER_CLIENTS t4,
EBROKER.EBROKER_CLAIMS_DETAILS t5 WHERE
t0.CLAIM_POLICY_UNIQUE = t1.POLICY_ID AND
t1.POLICY_CLIENT_UNIQUE = t2.CLIENT_ID AND
t0.CLAIM_MARINE_UNIQUE = t3.MARINE_ID AND
t3.MARINE_CLIENT_UNIQUE = t4.CLIENT_ID AND t0.CLAIM_ID
= t5.CLAIM_DETAIL_ID AND (((UPPER(t2.CLIENT_NAME) LIKE
UPPER(?)) AND (t0.CLAIM_MARINE_UNIQUE IS NULL)) OR
((t0.CLAIM_POLICY_UNIQUE IS NULL) AND
(UPPER(t4.CLIENT_NAME) LIKE UPPER(?)) AND
(t5.CLAIM_SETTLED = ?) AND (t5.CLAIM_RECOVERY = ?)))
[bind: 'p%', 'p%', 'false', 'false']
02:12:18,897  INFO QueryLogger:351 - === returned 0
rows. - took 7 ms.
02:12:18,898  INFO QueryLogger:384 - +++ transaction
committed.

To explaim the above query.

EBROKER.EBROKER_CLAIMS (eBrokerClaim) is my T1 that is
a child of EBROKER.EBROKER_POLICIES (eBrokerPolicy)
and EBROKER.EBROKER_MARINES (eBrokerMarine) related to
it (lets say T2 and T3 respectively) and T4 would be
EBROKER.EBROKER_CLIENTS (eBrokerClient).


Well i get no result.
My problem which i can actually see it is here. 

WHERE t0.CLAIM_POLICY_UNIQUE = t1.POLICY_ID AND
t1.POLICY_CLIENT_UNIQUE = t2.CLIENT_ID AND
t0.CLAIM_MARINE_UNIQUE = t3.MARINE_ID AND
t3.MARINE_CLIENT_UNIQUE = t4.CLIENT_ID

from the above debug how can i make the second AND sql
statement to be an OR this is beacuse inside
eBrokerClaim, there is no way eBrokerPolicy and
eBrokerMarine can exist together in the same row. One
is null while the other is not null. Both contain
eBrokerClient of which am searching thru DataObject
path names.

What would be the cayenne query statement i should use
to avid querying the policy AND the marine inside the
claim. This brings no result beacuse as i said marine
and policy cant exist together in a claim table row.
Please help

Regards Urugn



       
____________________________________________________________________________________
Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, photos &
more. 
http://mobile.yahoo.com/go?refer=1GNXIC

Mime
View raw message