J, Have a look at www.orionsupport.com for some advice on the things you want to do. Have a look at "Advanced finders" However there are some things you can't do with CMP yet as I see below. That is: the return values in the select part of your query must equal the fields of your entity bean. So for now, you can forget the GROUP BY things, as far as I know.. but maybe someone else has a solution for that. Suc6, Eddie ----- Original Message ----- From: "J.D. Bertron" <[EMAIL PROTECTED]> To: "Orion-Interest" <[EMAIL PROTECTED]> Cc: "J.D. Bertron" <[EMAIL PROTECTED]> Sent: Wednesday, September 26, 2001 5:26 PM Subject: Can someone summarize answers findBy query questions ? > Hi, > Can someone on this board summarize the answers to the > > "How do I set up a finder method with an underlying query like --- ?" > > 1- For "Like" queries. > SELECT ACCOUNTS.ID, ACCOUNTS.DESCRIPTION > FROM ACCOUNTS > WHERE ACCOUNTS.DESCRIPTION LIKE "%blah%" > > 2- For queries involving subqueries (JOINS) to a dependant table. > SELECT ACCOUNTS.ID, ACCOUNTS.DESCRIPTION, ACCOUNTS.ZIPCODE > FROM ACCOUNTS, REGIONS > WHERE ACCOUNTS.ZIPCODE = REGIONS.ZIPCODE AND REGIONS.ID = ? > > 3- For queries returning columns NOT originally in the bean: > SELECT ACCOUNT.ID, ACCOUNT.DESCRIPTION, COUNT(*) AS ORDERS > FROM ACCOUNTS, ACCT_ORDERS > WHERE ACCOUNTS.ID = ACCT_ORDERS.ID AND > GROUP BY ACCOUNT_ID, ACCOUNT.DESCRIPTION > > or queries NOT returning ALL columns in the bean: > SELECT ACCOUNT.ID, ACCOUNT.DESCRIPTION > FROM ACCOUNTS, ACCT_ORDERS > WHERE ACCOUNTS.ID = ACCT_ORDERS.ID AND > GROUP BY ACCOUNT_ID, ACCOUNT.DESCRIPTION > HAVING COUNT(*) > ? > > 4- Summarize the answer to all these ORDER BY questions > > > 5- Clarify solutions to the problem of degenerate finder signatures. > Your bean has many attributes, and you want to give your client > the ability to find beans based on a set of conditions (filter) > of their choosing. Combinations of conditions could > result in a degenerate number of finders and finder signatures. > A more desirable result is to have Orion handle empty finder method > parameters so that they are ignored in the final query. > > Example: findByFilters( String accountID, String zipCode, String > description) > When passed only the accountID and zipCode, the query filters only > on these two columns in the table > and ignores the description column. When passed in only a zipCode > the query ignores both the accountID and description. > > I'm personally anxious to get answers to #3 and #5. I'm sure others would > be interested in > a nice document with clear answers and examples. > > J.D. Bertron > > > > > > > > >
