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
|