Thought inc.

The Dynamic O/R Mapping Company
     

CocoBase Technical Support Forums
For access to developer site with software download and doc's, please request CocoBase download and password
will be emailed to you. Response to posts will appear when answered by THOUGHT Support team.
NOTE:You must register separately with forum in order to post your questions, please click on register icon below.
Home   Frequently Asked Questions   Search   Edit your profile   Registration is free!  
Email This Page to Someone!
Show a Printable Version
CocoBase Enterprise O/R Forums > CocoBase Questions
Complex Queries with Cocobase v5.0
< Previous Thread     Next Thread >
Author
Thread    Post New Thread     Post A Reply
mahesh_subbu
Member

Registered: Jun 2007
Posts: 2

Hi,

We have been using cocobase v4.5 in our previous project & we didn't have scenario's dealing with complex queries involving unions, multiple joins, subselect's etc. I have two questions reg. cocobase usage for our new project.

1) Now for our new project, we are getting cocobase v5.0 & would like to know whether it supports union operation.

2) In our new migration project, we use extensively complex queries involving unions, multiple joins, subselects in every sql query. Can u pls. suggest on how do i create cocobase maps for complex queries as given below. How do i handle these scenario's in cocobase?

SELECT * FROM
(SELECT A.*, ROWNUM RNUM
FROM(
SELECT ENTITY_ID, ENTITY_STATUS, MAX(RECENT_DATE), ITEM_COUNT FROM ((
SELECT ENTITY_ID, ENTITY_STATUS, MAX(RECENT_DATE) RECENT_DATE, COUNT(*) ITEM_COUNT FROM(
SELECT ORDERS_T.ORD_NBR ENTITY_ID, ORDERS_T.ORDER_ORD_STATUS_CD ENTITY_STATUS,max(ITEM_ACTVY_DT_TM) RECENT_DATE
FROM USERACCESS_ACCOUNTAGENCY_V,
ORDERS_CNTC_T,
ORDERS_T,
GUI_USER_I,
ACCOUNT_LCTN_T,
ORDERS_ITEM_T,
ORDER_STATUS_R,
ORDER_ITEM_USER_ACTIVITY_T
WHERE
( ORDERS_T.ORDER_ORD_ID = ORDERS_CNTC_T.ORDER_ORD_ID (+)) AND
( ORDERS_T.ORDER_ORD_ID = ORDERS_ITEM_T.ORDER_ORD_ID ) AND
( ORDERS_T.ACCOUNT_AGCY_ID = USERACCESS_ACCOUNTAGENCY_V.ACCOUNT_AGCY_ID ) AND
( ORDERS_T.ENTERER_GUI_USER_ID = GUI_USER_I.GUI_USER_ID) AND
( ACCOUNT_LCTN_T.ACCOUNT_LCTN_ID = ORDERS_T.ACCOUNT_LCTN_ID )
AND ORDERS_T.COM_CONTR_CD = :CONTR_CD
AND ORDERS_T.ORDER_ORD_STATUS_CD = ORDER_STATUS_R.ORDER_ORD_STATUS_CD
AND ORDERS_ITEM_T.ORDER_ITEM_ID = ORDER_ITEM_USER_ACTIVITY_T.ORDER_ITEM_ID
AND ORDER_ITEM_USER_ACTIVITY_T.GUI_USER_ID = :USER_ID
GROUP BY (ORDERS_T.ORD_NBR,ORDERS_T.ORDER_ORD_STATUS_CD,ORDER_ITEM_USER_ACTIVITY_T.ORDER_ITEM_ID)) GROUP BY (ENTITY_ID, ENTITY_STATUS)
)
UNION
(
SELECT ENTITY_ID, ENTITY_STATUS,RECENT_DATE, COUNT(ORDERS_ITEM_T.ORDER_ORD_ID) ITEM_COUNT FROM(
SELECT ORDERS_T.ORD_NBR ENTITY_ID,ORDERS_T.ORDER_ORD_STATUS_CD ENTITY_STATUS, max(ORD_ACTVY_DT_TM) RECENT_DATE, ORDERS_T.ORDER_ORD_ID ORD_ID
FROM USERACCESS_ACCOUNTAGENCY_V,
ORDERS_CNTC_T,
ORDERS_T,
GUI_USER_I,
ACCOUNT_LCTN_T,
ORDER_STATUS_R,
ORDER_ORD_USER_ACTVY_T
WHERE ORDERS_T.ORDER_ORD_ID = ORDER_ORD_USER_ACTVY_T.ORDER_ORD_ID and
( ORDERS_T.ORDER_ORD_ID = ORDERS_CNTC_T.ORDER_ORD_ID (+)) AND
( ORDERS_T.ACCOUNT_AGCY_ID = USERACCESS_ACCOUNTAGENCY_V.ACCOUNT_AGCY_ID ) AND
( ORDERS_T.ENTERER_GUI_USER_ID = GUI_USER_I.GUI_USER_ID) AND
( ACCOUNT_LCTN_T.ACCOUNT_LCTN_ID = ORDERS_T.ACCOUNT_LCTN_ID )
AND ORDERS_T.COM_CONTR_CD = :CONTR_CD
AND ORDERS_T.ORDER_ORD_ID = ORDER_ORD_USER_ACTVY_T.ORDER_ORD_ID
AND ORDERS_T.ORDER_ORD_STATUS_CD = ORDER_STATUS_R.ORDER_ORD_STATUS_CD
AND ORDER_ORD_USER_ACTVY_T.GUI_USER_ID = :USER_ID
GROUP BY (ORDERS_T.ORD_NBR,ORDERS_T.ORDER_ORD_STATUS_CD,ORDERS_T.ORDER_ORD_ID)) A, ORDERS_ITEM_T
WHERE A.ORD_ID = ORDERS_ITEM_T.ORDER_ORD_ID
GROUP BY (ENTITY_ID, ENTITY_STATUS,RECENT_DATE)
))
GROUP BY (ENTITY_ID, ENTITY_STATUS,ITEM_COUNT) ORDER BY 3 DESC) A
WHERE ROWNUM <= 10
) WHERE RNUM >= 1



Regards,
Mahesh

06-17-2007 09:27 PM
Click Here to See the Profile for mahesh_subbu    Find more posts by mahesh_subbu        Edit/Delete Message    Reply w/Quote    IP: Logged
admin
Administrator

Registered: Apr 2001
Posts: 19

CocoBase 5 supports this in the same manner as cb 4.x: either
by hardcoding the sql into sql maps or by using CBQuery api. CBQuery supports UNION, INTERSECT and MINUS operations. Basically, each UNION argument would be a CBQuery instance q1 and q2, and then one has to call:

q1.addSetQuery(CBQuery.UNION,q2);

That's how it would work. The other option is to simply create a sql map in the CB5 workbench by issuing a 'build' and edit it so it can provide a sql similar to the one below. The SQL editor in CB5 is very similar to the one in CB45 once it is engaged.

You have access to all of the same features in CB5, that you had in CB45, you just have new GUI workbench tooling, and additional JPA/EJB3 API support available.

Does that help?

THOUGHT Support

06-18-2007 11:52 AM
Click Here to See the Profile for admin    Find more posts by admin        Edit/Delete Message    Reply w/Quote    IP: Logged
All times are PST (US)    Post New Thread     Post A Reply
Forum Jump:
< Previous Thread     Next Thread >

Forum Rules:
Who Can Read The Forum? Any registered user or guest.
Who Can Post New Topics? Any registered user.
Who Can Post Replies? Any registered user.
Changes: Messages can be edited by their author. Messages can be deleted by their author.
Posts: HTML code is OFF. Smilies are OFF. vB code is OFF. [IMG] code is OFF.

Admin Options:
Open / Close Thread
Move Thread
Delete Thread
Edit Thread

< Contact Us - THOUGHT Inc. >

Copyright © Jelsoft Enterprises Limited 2000.
Copyright 2001 All Rights Reserved, THOUGHT Inc.