I thought OJB supported IN sub-select queries? Still new to it, so I am not
sure, but I thought I remembered seeing comments about it on the mailing
list.
If so, you could rewrite this query as:
SELECT *
FROM ACCOUNTTYPE
WHERE baseCode IN (SELECT baseCode FROM ACCOUNTTYPE WHERE ID = 3)
Not (usually) as effecient as a join, but completely db-nuetral...
|-----Original Message-----
|From: Charles Anthony [mailto:[EMAIL PROTECTED]
|Sent: Friday, February 28, 2003 2:49 AM
|To: '[EMAIL PROTECTED]'
|Subject: Help Needed For A Complex Query
|
|
|Hi All,
|
|I need to do a "self-join", on 'relationships' that are
|not defined in my
|repository.xml. Is this possible, and if so how ?
|
|Here is a very much simplified version of what I want to
|do. Here's a sample
|of the table (the class is very similar)
|
|Table ACCOUNTTYPE
|id, baseCode, variationCode
|1, "A", null
|2, "A", "B"
|3, "A", "C"
|4, "B", null
|5, "B", "A"
|6, "B", "B"
|7, "B", "C"
|
|I want to find all account types that have the same baseCode as the
|accountType with an id of 3. i.e. I want to return
|accounttypes 1,2,3
|
|In SQL I would do :
|SELECT PARENT.*
| FROM ACCOUNTTYPE
| INNER JOIN ACCOUNTTYPE PARENT ON ACCOUNTTYPE.baseCode =
|PARENT.baseCode
|WHERE (ACCOUNTTYPE.ID = 3)
|
|Can I do the same in a PB Query ? Failing that, in an OQL query ?
|
|I realise I can drop "down" to a SQL query, if I have to,
|but I'd really
|rather not if possible - our app must be completely
|cross-db compatible, and
|join's syntaxes vary across DBs.
|
|Oh, and the repository.xml cannot really be modified to include new
|relationships (it is automatically generated from XDE by a
|process we wrote,
|and I don't want to have to manually 'fix' it each tim)
|
|Am I asking for the impossible ?
|
|Thanks,
|
|Charles.
|
|
|This email and any attachments are strictly confidential
|and are intended
|solely for the addressee. If you are not the intended
|recipient you must
|not disclose, forward, copy or take any action in reliance
|on this message
|or its attachments. If you have received this email in
|error please notify
|the sender as soon as possible and delete it from your
|computer systems.
|Any views or opinions presented are solely those of the
|author and do not
|necessarily reflect those of HPD Software Limited or its
|affiliates.
|
| At present the integrity of email across the internet
|cannot be guaranteed
|and messages sent via this medium are potentially at risk.
| All liability
|is excluded to the extent permitted by law for any claims
|arising as a re-
|sult of the use of this medium to transmit information by or to
|HPD Software Limited or its affiliates.
|
|
|
|-----------------------------------------------------------
|----------
|To unsubscribe, e-mail: [EMAIL PROTECTED]
|For additional commands, e-mail: [EMAIL PROTECTED]
|
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
