Dynamic and Static Query. Cursor

From: <namanaresh_at_gmail.com>
Date: 27 Jan 2006 03:38:13 -0800
Message-ID: <1138361893.369947.97880@g14g2000cwa.googlegroups.com>

Hi all,

I have a complicated question which needs to be solved. I was using a dynamic query which was working fine, but due to performance issue it needs to be changed to a static query. But the values returned by a dynamic query is differing from that of a static query. I want the values based on the column LICENSE_ID_C same and if possible the order also to be same.
The queries are
input variables

  V_AGENT_ID_C  IN  TAGENT.AGENT_ID_C%TYPE,
  V_ACCT_ID_C   IN  TAGENT.ACCT_ID_C%TYPE,
  V_PAGE_SIZE   IN  NUMBER,
  V_PAGE_NO   IN  NUMBER,
  V_SORT_COL   IN  VARCHAR2 ,

SELECT_CURSOR OUT AGENT_CURSOR

Dynamic query.
V_SQL := ' SELECT * FROM (SELECT A.*,ROWNUM XNUM FROM (SELECT A.LICENSE_ID_C, A.AGENT_ID_C ,B.PROMPT_DESC_C , A.LICENSE_TYPE_C,

A.LICENSE_NO_C , A.STATE_ID_C ,'||

'A.RES_STATE_I , H.COST_CENTER_1, H.COST_CENTER_2, A.LIC_STATUS_C ,
A.LIC_REQ_DATE_D, A.LIC_EFF_DATE_D, ' ||
'A.LIC_TERM_IND_I, A.RENEWAL_DATE_D, A.USER_DEFINED_C,
A.TERM_DATE_D, E.ATTRIBUTE_DESC_T, A.LIC_CUST_DATE_1, ' ||
'A.LIC_CUST_DATE_2, A.LIC_CUST_1, A.LIC_CUST_2, A.LIC_CUST_3,
A.LIC_CUST_4, A.LIC_CUST_5, I.PK_COMP_PERIOD_ID_C, ' || 'A.COMMENT_C,A.PE_IND_I,TI.AGENT_IMAGE_ID_C,DECODE(TI.IMAGE_NAME_C,NULL,'''',''Yes'') AS IMG_UPLOADED ' ||
'FROM TPRODUCER_LICENSE A, TPROMPT_MAPPER B, TATTRIBUTE E , TAGENT
F, TORDER_XDB_COST_CENTER H, VCE_COMPLIANCE_PERIOD I, ' ||
'TAGENT_IMAGES TI ' ||

     'WHERE A.AGENT_ID_C = ' || V_AGENT_ID_C  ||
       ' AND A.LIC_STATUS_C = E.ATTRIBUTE_ID_C (+) AND A.AGENT_ID_C =

F.AGENT_ID_C AND F.ACCT_ID_C = NVL(' || V_ACCT_ID_C || ',F.ACCT_ID_C) ' ||

'AND A.LICENSE_ID_C = H.LICENSE_ID_C (+) AND A.LICENSE_ID_C = I.FK_LICENSE_ID_C (+) AND A.LICENSE_TYPE_C = B.PROMPT_BUS_CODE_C(+) ' ||

'AND A.LICENSE_ID_C = TI.LICENSE_ID_C (+) ' ||
'AND (TI.MODIFIED_Z = (SELECT MAX(TI2.MODIFIED_Z) ' ||

                          'FROM TAGENT_IMAGES TI2 ' ||
                'WHERE TI2.LICENSE_ID_C =A.LICENSE_ID_C) ' ||
   'OR TI.MODIFIED_Z IS NULL) ' ||
       'AND A.END_Z IS NULL ORDER BY  ' || V_SORT_COL || ',

A.LICENSE_ID_C ASC )A) WHERE (XNUM>(( ' || V_PAGE_SIZE ||
' *( ' || V_PAGE_NO || ' -1)))) AND (XNUM< (( ' || V_PAGE_SIZE
|| ' * ' || V_PAGE_NO || ')+1))'; OPEN SELECT_CURSOR FOR V_SQL;

static query is
OPEN SELECT_CURSOR FOR
SELECT * FROM (SELECT A.*,ROWNUM XNUM FROM (SELECT A.LICENSE_ID_C,

A.AGENT_ID_C ,B.PROMPT_DESC_C , A.LICENSE_TYPE_C, A.LICENSE_NO_C ,
A.STATE_ID_C ,
A.RES_STATE_I , H.COST_CENTER_1, H.COST_CENTER_2, A.LIC_STATUS_C ,
A.LIC_REQ_DATE_D, A.LIC_EFF_DATE_D,
A.LIC_TERM_IND_I, A.RENEWAL_DATE_D, A.USER_DEFINED_C, A.TERM_DATE_D,
E.ATTRIBUTE_DESC_T, A.LIC_CUST_DATE_1,
A.LIC_CUST_DATE_2, A.LIC_CUST_1, A.LIC_CUST_2, A.LIC_CUST_3,
A.LIC_CUST_4, A.LIC_CUST_5, I.PK_COMP_PERIOD_ID_C,
A.COMMENT_C,A.PE_IND_I,TI.AGENT_IMAGE_ID_C,DECODE(TI.IMAGE_NAME_C,NULL,'','Yes')

AS IMG_UPLOADED
FROM TPRODUCER_LICENSE A, TPROMPT_MAPPER B, TATTRIBUTE E , TAGENT F, TORDER_XDB_COST_CENTER H, VCE_COMPLIANCE_PERIOD I, TAGENT_IMAGES TI
WHERE A.AGENT_ID_C = V_AGENT_ID_C

AND A.LIC_STATUS_C = E.ATTRIBUTE_ID_C (+)
AND A.AGENT_ID_C = F.AGENT_ID_C
AND F.ACCT_ID_C = NVL(V_ACCT_ID_C,F.ACCT_ID_C)
AND A.LICENSE_ID_C = H.LICENSE_ID_C (+)
AND A.LICENSE_ID_C = I.FK_LICENSE_ID_C (+)
AND A.LICENSE_TYPE_C = B.PROMPT_BUS_CODE_C(+)
AND A.LICENSE_ID_C = TI.LICENSE_ID_C (+)

AND (TI.MODIFIED_Z = (SELECT MAX(TI2.MODIFIED_Z) FROM TAGENT_IMAGES TI2 WHERE TI2.LICENSE_ID_C =A.LICENSE_ID_C) OR TI.MODIFIED_Z IS NULL) AND A.END_Z IS NULL
ORDER BY DECODE(V_SORT_COL,'A.STATE_ID_C DESC', A.STATE_ID_C,'A.LICENSE_NO_C DESC', A.LICENSE_NO_C,'RES_STATE_I DESC', RES_STATE_I, 'LIC_STATUS_C DESC', LIC_STATUS_C, 'LIC_REQ_DATE_D DESC', LIC_REQ_DATE_D, 'LIC_EFF_DATE_D DESC', LIC_EFF_DATE_D, 'RENEWAL_DATE_D

DESC', RENEWAL_DATE_D, 'TERM_DATE_D DESC', TERM_DATE_D, 'LIC_CUST_1
DESC', LIC_CUST_1, 'COST_CENTER_1 DESC', COST_CENTER_1,'COST_CENTER_2
DESC', COST_CENTER_2, 'PROMPT_DESC_C DESC', PROMPT_DESC_C, 'PE_IND_I
DESC', PE_IND_I, 'COMMENT_C DESC', COMMENT_C , A.STATE_ID_C) DESC,

A.LICENSE_ID_C ASC )A)
WHERE (XNUM>(( V_PAGE_SIZE *( V_PAGE_NO -1)))) AND (XNUM< (( V_PAGE_SIZE * V_PAGE_NO )+1)); Received on Fri Jan 27 2006 - 05:38:13 CST