Hi Steven,
Yes OJB does support sub-select queries but - as you say - it isn't very
efficient. Furthermore, I have to join the results of this query to results
of another similair query via a many-many indirection table - in other
words, the full query is far more complex than the example given.
What I've done for now is to do a direct SQL query, but use "equals" instead
of joins as in the following.
SELECT PARENT.*
FROM ACCOUNTTYPE , ACCOUNTTYPE AS PARENT
WHERE PARENT.baseCode =ACCOUNTTYPE.baseCode
AND (ACCOUNTTYPE.ID = 3)
Like your solution, this too is db-neutral but I suspect more efficient than
the subquery. Thanks for the suggestion, though.
Cheers,
Charles.
>-----Original Message-----
>From: Ebersole, Steven [mailto:[EMAIL PROTECTED]
>Sent: 28 February 2003 15:36
>To: 'OJB Users List'
>Subject: RE: Help Needed For A Complex Query
>
>
>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]
>
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]
