Home » Developer & Programmer » Reports & Discoverer » ORA-24323 Value not allowed in the Report Builder
 ORA-24323 Value not allowed in the Report Builder [message #138179] |
Tue, 20 September 2005 10:43  |
pandeabh
Messages: 2
Registered: September 2005
Location: Mumbai
|
Junior Member |
|
|
Hi All,
I have one report which is running in the production instance, now i want to modify that report. But when i'm opening that report in the report buider of the deve instance with minute changes it throws an error ORA-24323 Value not allowed and it drops the connection to that instance.
If i run that same query with hard coded values instead of bind variables it is not giving any error. And if i replace any of that hard coded value with parameter then it throws this error.
Can anyone pls help me.
1. AND ai.org_id = NVL(ai.org_id,ai.org_id) -- will run successfully
2. AND ai.org_id = NVL(1485,ai.org_id) -- will run successfully
3. AND ai.org_id = NVL(:org_id,ai.org_id) -- will throw an error
Pls reply ASAP, thanks in advance
|
|
|
|
|
|
|
|
| Re: ORA-24323 Value not allowed in the Report Builder [message #182527 is a reply to message #138179] |
Sun, 16 July 2006 05:08  |
samruddhi
Messages: 1
Registered: July 2006
Location: i
|
Junior Member |
|
|
Hi,
i got the same error that to in a simple select query. this query is running fine in toad. but when i copy it into REPORT BUILDER it is firing ora-24323 error.
please tell me its solution.
here is the query-
SELECT Q1_POL_SYS_ID,
Q1_END_NO_IDX,
Q1_END_SR_NO,
Q1_POL_NO,
Q1_ASSR_NAME,
Q1_DIVN_CODE,
Q1_DEPT_CODE ,
Q1_PROD_CODE,
Q1_CLM_SYS_ID,
Q1_CLM_NO,Q1_INTM_DT,
Q1_LOSS_DT,
Q1_LOSS_CODE,
Q1_DIVN_NAME,
Q1_DEPT_NAME,Q1_PROD_NAME,Q1_NAT_OF_LOSS,--EST_PROV_AMT,REV_PROV_AMT,B.PAID_AMT,
SUM(NVL(EST_PROV_AMT,0)) Q1_EST_PROV_AMT,
SUM(NVL(REV_PROV_AMT,0)) Q1_REV_PROV_AMT,
SUM(NVL(PAID_AMT,0)) Q1_PAID_AMT,
((SUM(NVL(EST_PROV_AMT,0)) -
SUM(NVL(REV_PROV_AMT,0)))-
(SUM(NVL(PAID_AMT,0)))) Q1_OS_AMT
FROM
(SELECT PS_POL_SYS_ID Q1_POL_SYS_ID,
PS_END_NO_IDX Q1_END_NO_IDX,
PS_END_SR_NO Q1_END_SR_NO,
PS_POL_NO Q1_POL_NO,
PS_ASSR_NAME Q1_ASSR_NAME,
CLS_POL_DIVN_CODE Q1_DIVN_CODE,
CLS_DEPT_CODE Q1_DEPT_CODE ,
pgipk_code_desc.FN_GET_PROD_NAME( CLS_PROD_CODE) Q1_PROD_CODE,
CLS_SYS_ID Q1_CLM_SYS_ID,
CLS_CLM_NO Q1_CLM_NO,
CLS_INTM_DT Q1_INTM_DT,
TRUNC(CLS_LOSS_DT) Q1_LOSS_DT,
CLS_LOSS_CODE Q1_LOSS_CODE,
PGIPK_CODE_DESC.FN_GET_DIVN_NAME(CLS_POL_DIVN_CODE )
Q1_DIVN_NAME,
PGIPK_CODE_DESC.FN_GET_DEPT_NAME(CLS_DEPT_CODE)
Q1_DEPT_NAME,
PGIPK_CODE_DESC.FN_GET_PROD_NAME(CLS_PROD_CODE) Q1_PROD_NAME,
PGIPK_CODE_DESC.FN_GET_PC_DESC('NAT_OF_LOSS',CLS_LOSS_CODE) Q1_NAT_OF_LOSS,
SUM(DECODE(PCES_PROV_TYPE,1,NVL(PCES_PROV_LC_1,0),0) +
DECODE(PCES_PROV_TYPE,2,NVL(PCES_PROV_LC_1,0),0)) EST_PROV_AMT,
SUM(DECODE(PCES_PROV_TYPE,3,NVL(PCES_PROV_LC_1,0),0) +
DECODE(PCES_PROV_TYPE,4,NVL(PCES_PROV_LC_1,0),0)) REV_PROV_AMT
FROM PGIS_CLAIM A,
PGIS_CLM_EST B,
PGIS_PREM_SUMM C
WHERE PCES_CLS_SYS_ID = CLS_SYS_ID
AND PCES_POL_SYS_ID = PS_POL_SYS_ID
AND PCES_END_NO_IDX = PS_END_NO_IDX
AND PCES_END_SR_NO = PS_END_SR_NO
AND CLS_DIVN_CODE >= NVL(:REP_VALUE_1,0)
AND CLS_DIVN_CODE <= NVL(:REP_VALUE_2,0)
AND CLS_DEPT_CODE >= NVL(:REP_VALUE_3,0)
AND CLS_DEPT_CODE <= NVL(:REP_VALUE_4,0)
AND CLS_PROD_CODE >= NVL(:REP_VALUE_5,0)
AND CLS_PROD_CODE <= NVL(:REP_VALUE_6,0)
AND TRUNC(PCES_PROV_APPR_DT) <= NVL(:REP_VALUE_7 , SYSDATE)
/*AND PCES_SR_NO = (SELECT MAX(PCES_SR_NO)
FROM PGIS_CLM_EST
WHERE PCES_CLS_SYS_ID = CLS_SYS_ID
AND TRUNC(PCES_PROV_APPR_DT) <= :REP_VALUE_7)*/
AND (PCES_CLOSE_YN = '0'
OR (PCES_CLOSE_YN = '1' AND TRUNC(PCES_CLOSE_DT) > NVL(:REP_VALUE_7 , SYSDATE)))
--AND PCES_PROV_REF_TYPE = '1'
GROUP BY
PS_POL_SYS_ID,
PS_END_NO_IDX,
PS_END_SR_NO,
PS_POL_NO,
PS_UW_YEAR,
PS_FM_DT || ' To ' || PS_TO_DT,
PS_ASSR_NAME,
CLS_POL_DIVN_CODE,
CLS_DEPT_CODE,
CLS_PROD_CODE,
CLS_LOSS_CODE,
CLS_SYS_ID,
CLS_CLM_NO,
CLS_INTM_DT,
CLS_LOSS_CODE,
TRUNC(CLS_LOSS_DT)) A,
(SELECT CLS_SYS_ID Q1_CLM_SYS_ID_B,
CLS_CLM_NO Q1_CLM_NO_B,
SUM(NVL(CS_PAID_AMT_LC_1,0)) PAID_AMT
FROM PGIT_CLM_SETL,--PGIS_CLM_PAYM , PGIT_CLM_EST
PGIS_CLM_EST,
PGIS_CLAIM
WHERE CS_POL_SYS_ID = PCES_POL_SYS_ID
AND CS_CE_SYS_ID = PCES_SYS_ID
AND CS_POL_END_NO_IDX = PCES_END_NO_IDX
AND CS_POL_END_SR_NO = PCES_END_SR_NO
AND CLS_SYS_ID = PCES_CLS_SYS_ID
AND CLS_DIVN_CODE >= NVL(:REP_VALUE_1,0)
AND CLS_DIVN_CODE <= NVL(:REP_VALUE_2,0)
AND CLS_DEPT_CODE >= NVL(:REP_VALUE_3,0)
AND CLS_DEPT_CODE <= NVL(:REP_VALUE_4,0)
AND CLS_PROD_CODE >= NVL(:REP_VALUE_5,0)
AND CLS_PROD_CODE <= NVL(:REP_VALUE_6,0)
AND TRUNC(CS_APPR_DT) <=NVL( :REP_VALUE_7 ,0)
AND (PCES_CLOSE_YN = '0'
OR (PCES_CLOSE_YN = '1' AND TRUNC(PCES_CLOSE_DT) > NVL(:REP_VALUE_7, SYSDATE) ))
--AND PYM_PROV_REF_TYPE = '1'
GROUP BY CLS_SYS_ID,
CLS_CLM_NO ) B
WHERE Q1_CLM_SYS_ID = Q1_CLM_SYS_ID_B
AND Q1_CLM_NO = Q1_CLM_NO_B
GROUP BY
Q1_POL_SYS_ID,
Q1_END_NO_IDX,
Q1_END_SR_NO,
Q1_POL_NO,
Q1_ASSR_NAME,
Q1_DIVN_CODE,
Q1_DEPT_CODE,
Q1_PROD_CODE,
Q1_CLM_SYS_ID,
Q1_CLM_NO,
Q1_INTM_DT,
Q1_LOSS_DT,
Q1_LOSS_CODE,
Q1_DIVN_NAME,
Q1_DEPT_NAME,
Q1_PROD_NAME,
Q1_NAT_OF_LOSS
ORDER BY
Q1_CLM_SYS_ID
regards,
Samruddhi
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Jun 03 17:52:57 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
