chalie001
asked on
report returning null
hi i have a report in 11g is returning null but when i test the report query in sqlplus is returning everything what could be the problem
Which reporting tool are you using ? Have you mapped the columns properly to the report from database ?
ASKER
oracle report yes i did only when i edit the query it does not work in report builder but the same query working in sql plus
ASKER
this is the report https://drive.google.com/open?id=0B3dKl5pXBYMqaFcyR0x1Rm54WlU this is the part i chnage
WHERE A.UNIT_CDE = TO_CHAR(10223) AND
(A.COURSE_NO IS NULL /*= NVL(:PAR_CNO,A.COURSE_NO) */ OR
A.PERS_FORCE_NO IS NULL) /*= NVL(:PAR_FNO,A.PERS_FORCE_ NO) ) */ and
A.PERS_ID_NO = NVL('6410170001080',A.PERS _ID_NO) AND
WHERE A.UNIT_CDE = TO_CHAR(10223) AND
(A.COURSE_NO IS NULL /*= NVL(:PAR_CNO,A.COURSE_NO) */ OR
A.PERS_FORCE_NO IS NULL) /*= NVL(:PAR_FNO,A.PERS_FORCE_
A.PERS_ID_NO = NVL('6410170001080',A.PERS
A.COURSE_NO = NVL(:PAR_CNO,A.COURSE_NO)
that condition will not work if A.COURSE_NO is null.
NULL = NULL is false.
that condition will not work if A.COURSE_NO is null.
NULL = NULL is false.
ASKER
but when i test the query in sql developer is working check the query
databasequery.sql
queryinreportbuilder.sql
databasequery.sql
queryinreportbuilder.sql
you are running two different queries with two different conditions.
why do you expect them to return the same results?
--- from databasequery.sql
(A.COURSE_NO IS NULL /*= NVL(:PAR_CNO,A.COURSE_NO) */ OR
A.PERS_FORCE_NO IS NULL) /*= NVL(:PAR_FNO,A.PERS_FORCE_ NO) ) */ and
is not the same thing as
--- from queryinreportbuilder.sql
(A.COURSE_NO = NVL(:PAR_CNO,A.COURSE_NO) OR
A.PERS_FORCE_NO = NVL(:PAR_FNO,A.PERS_FORCE_ NO) ) and
why do you expect them to return the same results?
--- from databasequery.sql
(A.COURSE_NO IS NULL /*= NVL(:PAR_CNO,A.COURSE_NO) */ OR
A.PERS_FORCE_NO IS NULL) /*= NVL(:PAR_FNO,A.PERS_FORCE_
is not the same thing as
--- from queryinreportbuilder.sql
(A.COURSE_NO = NVL(:PAR_CNO,A.COURSE_NO) OR
A.PERS_FORCE_NO = NVL(:PAR_FNO,A.PERS_FORCE_
ASKER
ok so how can make this work
WHERE A.UNIT_CDE = TO_CHAR(:PAR_UNIT) AND
(A.COURSE_NO = NVL(:PAR_CNO,A.COURSE_NO) OR
A.PERS_FORCE_NO = NVL(:PAR_FNO,A.PERS_FORCE_ NO) ) and
A.PERS_ID_NO = NVL(:PAR_IDNO,A.PERS_ID_NO ) AND
(B.PERS_FORCE_NO = A.PERS_FORCE_NO OR
B.PERS_ID_NO = A.PERS_ID_NO) AND
B.UNIT_CDE = A.UNIT_CDE
WHERE A.UNIT_CDE = TO_CHAR(:PAR_UNIT) AND
(A.COURSE_NO = NVL(:PAR_CNO,A.COURSE_NO) OR
A.PERS_FORCE_NO = NVL(:PAR_FNO,A.PERS_FORCE_
A.PERS_ID_NO = NVL(:PAR_IDNO,A.PERS_ID_NO
(B.PERS_FORCE_NO = A.PERS_FORCE_NO OR
B.PERS_ID_NO = A.PERS_ID_NO) AND
B.UNIT_CDE = A.UNIT_CDE
what do you want it to do differently than it does now?
I'm going to guess that the problem is you want NULL parameters to mean "get everything" and non-null parameters to mean "get this value"
is that correct?
I'm going to guess that the problem is you want NULL parameters to mean "get everything" and non-null parameters to mean "get this value"
is that correct?
if my guess is correct, then change conditions like this..
A.COURSE_NO = NVL(:PAR_CNO,A.COURSE_NO)
to
(:PAR_CNO is null OR A.COURSE_NO = :PAR_CNO)
A.COURSE_NO = NVL(:PAR_CNO,A.COURSE_NO)
to
(:PAR_CNO is null OR A.COURSE_NO = :PAR_CNO)
ASKER
i only what to return one value
ASKER
i only what value for the :PAR_IDNO passed cureently is returning everything i what it to do as in database query
your database query sql file, your pl/sql developer example and your report sql file all do something different.
if you want your repoort to act like the database query file, then pass in as a parameter the same value that you have hardcoded in your database query file.
if you wnat your report to act like the pl/sql developer example, then change the query to match the query you used in pl/sql developer and then change the hard coded value to be a parameter and pass in the same value to the parameter.
if you want your repoort to act like the database query file, then pass in as a parameter the same value that you have hardcoded in your database query file.
if you wnat your report to act like the pl/sql developer example, then change the query to match the query you used in pl/sql developer and then change the hard coded value to be a parameter and pass in the same value to the parameter.
ASKER
hi i dont what to hard code value i what to use pass value/paramete the hard coded value was for testing
i what
WHERE A.UNIT_CDE = TO_CHAR(:PAR_UNIT) AND
(A.COURSE_NO = NVL(:PAR_CNO,A.COURSE_NO) OR
A.PERS_FORCE_NO = NVL(:PAR_FNO,A.PERS_FORCE_ NO) ) and
A.PERS_ID_NO = NVL(:PAR_IDNO,A.PERS_ID_NO ) AND
(B.PERS_FORCE_NO = A.PERS_FORCE_NO OR
B.PERS_ID_NO = A.PERS_ID_NO) AND
B.UNIT_CDE = A.UNIT_CDE
to do the same as this
WHERE A.UNIT_CDE = TO_CHAR(10223) AND
(A.COURSE_NO IS NULL /*= NVL(:PAR_CNO,A.COURSE_NO) */ OR
A.PERS_FORCE_NO IS NULL) /*= NVL(:PAR_FNO,A.PERS_FORCE_ NO) ) */ and
A.PERS_ID_NO = NVL('6410170001080',A.PERS _ID_NO) AND
(B.PERS_FORCE_NO = A.PERS_FORCE_NO OR
B.PERS_ID_NO = A.PERS_ID_NO) AND
but without hard coded values
i what
WHERE A.UNIT_CDE = TO_CHAR(:PAR_UNIT) AND
(A.COURSE_NO = NVL(:PAR_CNO,A.COURSE_NO) OR
A.PERS_FORCE_NO = NVL(:PAR_FNO,A.PERS_FORCE_
A.PERS_ID_NO = NVL(:PAR_IDNO,A.PERS_ID_NO
(B.PERS_FORCE_NO = A.PERS_FORCE_NO OR
B.PERS_ID_NO = A.PERS_ID_NO) AND
B.UNIT_CDE = A.UNIT_CDE
to do the same as this
WHERE A.UNIT_CDE = TO_CHAR(10223) AND
(A.COURSE_NO IS NULL /*= NVL(:PAR_CNO,A.COURSE_NO) */ OR
A.PERS_FORCE_NO IS NULL) /*= NVL(:PAR_FNO,A.PERS_FORCE_
A.PERS_ID_NO = NVL('6410170001080',A.PERS
(B.PERS_FORCE_NO = A.PERS_FORCE_NO OR
B.PERS_ID_NO = A.PERS_ID_NO) AND
but without hard coded values
what values are you going to use for the three parameters here... to mimic the results you want ?
A.COURSE_NO = NVL(:PAR_CNO,A.COURSE_NO) OR
A.PERS_FORCE_NO = NVL(:PAR_FNO,A.PERS_FORCE_ NO) ) and
A.PERS_ID_NO = NVL(:PAR_IDNO,A.PERS_ID_NO )
A.COURSE_NO = NVL(:PAR_CNO,A.COURSE_NO) OR
A.PERS_FORCE_NO = NVL(:PAR_FNO,A.PERS_FORCE_
A.PERS_ID_NO = NVL(:PAR_IDNO,A.PERS_ID_NO
ASKER
so the whole query must be like this
SELECT A.UNIT_CDE UNIT,
A.HOME_UNIT_CDE HOME_UNIT,
DECODE(A.PERS_FORCE_NO,NULL,RPAD((' ID No.: '||A.PERS_ID_NO||' '||A.RANK_NME||' '||A.PERS_INIT||' '
||A.PERS_SURNAME),70)||DECODE(A.COURSE_NO,NULL,
RPAD(('Personnel Cat: '||A.PERS_CAT
||' '||C.CDE_DESC),57),
RPAD((' Course No.: '||A.COURSE_NO
||' '||E.COURSE_NME),57)),
RPAD(('Force No: '||A.PERS_FORCE_NO||' '||A.RANK_NME||' '||A.PERS_INIT||' '
||A.PERS_SURNAME),70)||DECODE(A.COURSE_NO,NULL,RPAD(('Personnel Cat: '||A.PERS_CAT||' '
||C.CDE_DESC),57),
RPAD((' Course No.: '||A.COURSE_NO||' '||E.COURSE_NME),
57))) FNO_ID,
B.LEDG_CLS LEDG_CLS,
B.RTN_CDE RTN_CDE,
SUBSTR(R.CDE_DESC,1,15) CDE_DESC1,
SUBSTR(R.CDE_DESC,16,15) CDE_DESC2,
B.ISS_UNIT_CDE ISS_UNIT,
S.ENG_ABBR_NME INAME,
B.STK_ACNT_NO SNO,
DECODE(L.SIN,NULL,NULL,LPAD(I.NSC,4,'0')||'-'||SUBSTR(LPAD(I.NIIN,9,'0'),1,2)||'-'
||SUBSTR(LPAD(I.NIIN,9,'0'),3,3)||'-'||SUBSTR(LPAD(I.NIIN,9,'0'),6,4))
ICN,
B.UIC UIC,
B.ITM_SIZE ITM_SIZE,
DECODE(L.SIN,NULL,'MAJOR ERROR - NO LEDGER FOUND',DECODE(N.ITM_NME,NULL,F.ITM_NME,N.ITM_NME))
ITM_NME,
D.ITM_NME SID,
NULL SER_NO,
B.QTY_ACTUAL QTY_ACT,
B.QTY_SHORT QTY_SHORT,
B.QTY_CAPT QTY_CAPT,
B.LOAN_RTN_DTE LOAN_RTN_DTE,
B.LAST_RTN_DTE LAST_RTN_DTE,
B.LAST_ISS_DTE ISS_DTE,
B.LAST_INSP_DTE INSP_DTE
FROM ILS.NAMES F,
ILS.NAMES D,
ILS.NAMES N,
ILS.ITEM_DATA I,
ILS.LEDGER L,
ILS.UNT S,
ILS.CDE_TABLE_ENTRY R,
ILS.UNT U,
ILS.ACNT_LN B,
ILS.SOI_ID E,
ILS.CDE_TABLE_ENTRY C,
ILS.ACNT_ID A
WHERE A.UNIT_CDE = TO_CHAR(:PAR_UNIT) AND
(A.COURSE_NO = NVL(:PAR_CNO,A.COURSE_NO) OR
A.PERS_FORCE_NO = NVL(:PAR_FNO,A.PERS_FORCE_NO) ) and
A.PERS_ID_NO = NVL(:PAR_IDNO,A.PERS_ID_NO) AND
(B.PERS_FORCE_NO = A.PERS_FORCE_NO OR
B.PERS_ID_NO = A.PERS_ID_NO) AND
B.UNIT_CDE = A.UNIT_CDE AND
C.CDE_TAB_ID (+) = 'MPER' AND
C.CDE_TAB_VAL (+) = A.PERS_CAT AND
U.UNIT_CDE = A.UNIT_CDE AND
R.CDE_TAB_ID (+) = 'MRET' AND
R.CDE_TAB_VAL (+) = B.RTN_CDE AND
S.UNIT_CDE (+) = B.ISS_UNIT_CDE AND
L.STK_ACNT_NO (+) = B.STK_ACNT_NO AND
L.UNIT_CDE (+) = B.UNIT_CDE and
I.SIN (+) = NVL(L.SIN,0) AND
(i.agency_ser_cntrl_ind = 'N' or
i.ser_cntr_ind = 'N' or
i.itm_type_cde not in ('V','S','O')) and
E.COURSE_NO (+) = A.COURSE_NO AND
(N.SIN (+) = NVL(L.SIN,0) AND
N.ITM_NME_TYPE (+) = '1' AND
N.NAME_SEQ (+) = 1) AND
(F.SIN (+) = L.SIN AND
F.ITM_NME_TYPE (+) = 'Z' AND
F.NAME_SEQ (+) = 1) AND
(D.SIN (+) = L.SIN AND
D.ITM_NME_TYPE (+) = '8' AND
D.NAME_SEQ (+) = 1)
UNION ALL
SELECT A.UNIT_CDE UNIT,
A.HOME_UNIT_CDE HOME_UNIT,
DECODE(A.PERS_FORCE_NO,NULL,RPAD((' ID No.: '||A.PERS_ID_NO||' '||A.RANK_NME||' '||A.PERS_INIT||' '
||A.PERS_SURNAME),70)||DECODE(A.COURSE_NO,NULL,
RPAD(('Personnel Cat: '||A.PERS_CAT
||' '||C.CDE_DESC),57),
RPAD((' Course No.: '||A.COURSE_NO
||' '||E.COURSE_NME),57)),
RPAD(('Force No: '||A.PERS_FORCE_NO||' '||A.RANK_NME||' '||A.PERS_INIT||' '
||A.PERS_SURNAME),70)||DECODE(A.COURSE_NO,NULL,RPAD(('Personnel Cat: '||A.PERS_CAT||' '
||C.CDE_DESC),57),
RPAD((' Course No.: '||A.COURSE_NO||' '||E.COURSE_NME),
57))) FNO_ID,
B.LEDG_CLS LEDG_CLS,
B.RTN_CDE RTN_CDE,
SUBSTR(R.CDE_DESC,1,15) CDE_DESC1,
SUBSTR(R.CDE_DESC,16,15) CDE_DESC2,
B.ISS_UNIT_CDE ISS_UNIT,
S.ENG_ABBR_NME INAME,
B.STK_ACNT_NO SNO,
DECODE(L.SIN,NULL,NULL,LPAD(I.NSC,4,'0')||'-'||SUBSTR(LPAD(I.NIIN,9,'0'),1,2)||'-'
||SUBSTR(LPAD(I.NIIN,9,'0'),3,3)||'-'||SUBSTR(LPAD(I.NIIN,9,'0'),6,4))
ICN,
B.UIC UIC,
B.ITM_SIZE ITM_SIZE,
DECODE(L.SIN,NULL,'MAJOR ERROR - NO LEDGER FOUND',DECODE(N.ITM_NME,NULL,F.ITM_NME,N.ITM_NME))
ITM_NME,
D.ITM_NME SID,
SS.SER_NO SER_NO,
B.QTY_ACTUAL QTY_ACT,
B.QTY_SHORT QTY_SHORT,
B.QTY_CAPT QTY_CAPT,
B.LOAN_RTN_DTE LOAN_RTN_DTE,
B.LAST_RTN_DTE LAST_RTN_DTE,
B.LAST_ISS_DTE ISS_DTE,
B.LAST_INSP_DTE INSP_DTE
FROM ILS.NAMES F,
ILS.NAMES D,
ILS.NAMES N,
ILS.SSS_ER_CORE SSS,
ILS.SSS_ENTRY_INDEX SS,
ILS.ITEM_DATA I,
ILS.LEDGER L,
ILS.UNT S,
ILS.CDE_TABLE_ENTRY R,
ILS.UNT U,
ILS.ACNT_LN B,
ILS.SOI_ID E,
ILS.CDE_TABLE_ENTRY C,
ILS.ACNT_ID A
WHERE A.UNIT_CDE = TO_CHAR(:PAR_UNIT) AND
(A.COURSE_NO = NVL(:PAR_CNO,A.COURSE_NO) OR
A.PERS_FORCE_NO = NVL(:PAR_FNO,A.PERS_FORCE_NO) and
A.PERS_ID_NO = NVL(:PAR_IDNO,A.PERS_ID_NO) ) AND
(B.PERS_FORCE_NO = A.PERS_FORCE_NO OR
B.PERS_ID_NO = A.PERS_ID_NO) AND
B.UNIT_CDE = A.UNIT_CDE AND
C.CDE_TAB_ID (+) = 'MPER' AND
C.CDE_TAB_VAL (+) = A.PERS_CAT AND
U.UNIT_CDE = A.UNIT_CDE AND
R.CDE_TAB_ID (+) = 'MRET' AND
R.CDE_TAB_VAL (+) = B.RTN_CDE AND
S.UNIT_CDE = B.ISS_UNIT_CDE AND
L.STK_ACNT_NO = B.STK_ACNT_NO AND
L.UNIT_CDE = B.UNIT_CDE and
ss.sin = NVL(l.sin,0) and
ss.sin_change_reas is null and
ss.sin_change_dte is null and
sss.scin = NVL(ss.scin,0) and
I.SIN = NVL(L.SIN,0) AND
(i.agency_ser_cntrl_ind = 'Y' or
(i.ser_cntr_ind = 'Y' and
i.itm_type_cde in ('V','S','O'))) and
SSS.pers_located_at = NVL(:PAR_FNO,:PAR_IDNO) and
sss.rtn_cde = b.rtn_cde and
E.COURSE_NO (+) = NVL(A.COURSE_NO,'') AND
(N.SIN (+) = L.SIN AND
N.ITM_NME_TYPE (+) = '1' AND
N.NAME_SEQ (+) = 1) AND
(F.SIN (+) = L.SIN AND
F.ITM_NME_TYPE (+) = 'Z' AND
F.NAME_SEQ (+) = 1) AND
(D.SIN (+) = L.SIN AND
D.ITM_NME_TYPE (+) = '8' AND
D.NAME_SEQ (+) = 1)
ORDER BY 3,11,20,5
ASKER
it will be like this
A.COURSE_NO = NVL(null,A.COURSE_NO) OR
A.PERS_FORCE_NO = NVL(null,A.PERS_FORCE_NO) ) and
A.PERS_ID_NO = NVL('33444555555',A.PERS_I D_NO)
A.COURSE_NO = NVL(null,A.COURSE_NO) OR
A.PERS_FORCE_NO = NVL(null,A.PERS_FORCE_NO) ) and
A.PERS_ID_NO = NVL('33444555555',A.PERS_I
ASKER
check the database query i have hard coded the value
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok will test and get back to you
if your parameters will usually be populated, then you may want to reverse the order of comparison
so
((:par_cno IS NULL AND a.course_no IS NULL) OR (:par_cno = a.course_no))
might be reversed to
((:par_cno = a.course_no) or (:par_cno IS NULL AND a.course_no IS NULL))
this is because oracle does short-circuit evaluation of booleans.
if you will usually have a value, then checking for null first is a waste of time.
if you will usually have nulls then checking for null first is a good idea.
the end performance difference is minimal, but if executed across millions of rows the cumulative effect of tiny improvements can become noticable
so
((:par_cno IS NULL AND a.course_no IS NULL) OR (:par_cno = a.course_no))
might be reversed to
((:par_cno = a.course_no) or (:par_cno IS NULL AND a.course_no IS NULL))
this is because oracle does short-circuit evaluation of booleans.
if you will usually have a value, then checking for null first is a waste of time.
if you will usually have nulls then checking for null first is a good idea.
the end performance difference is minimal, but if executed across millions of rows the cumulative effect of tiny improvements can become noticable
ASKER
(
((:par_cno IS NULL AND a.course_no IS NULL) OR (:par_cno = a.course_no))
OR
((:par_fno IS NULL AND a.pers_force_no IS NULL) OR (:par_fno = a.course_no))
)
AND a.pers_id_no = NVL(:par_idno, a.pers_id_no)
((:par_cno IS NULL AND a.course_no IS NULL) OR (:par_cno = a.course_no))
OR
((:par_fno IS NULL AND a.pers_force_no IS NULL) OR (:par_fno = a.course_no))
)
AND a.pers_id_no = NVL(:par_idno, a.pers_id_no)