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
chalie001Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
>>> check the database query i have hard coded the value

I did, but since there is a functional difference in the syntax of what you posted, I wanted to see your intended parameter usage so I could make the parameters behave like your hardcoding.

change this

(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)


to

(  
    ((: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)


if you need to handle null values of :par_idno  like you do the null values of the other parameters, then follow the same model with those.
0
 
Pawan KumarDatabase ExpertCommented:
Which reporting tool are you using ? Have  you mapped the columns properly to the report from database ?
0
 
chalie001Author Commented:
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
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
chalie001Author Commented:
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
0
 
sdstuberCommented:
A.COURSE_NO           = NVL(:PAR_CNO,A.COURSE_NO)

that condition will not work if A.COURSE_NO is null.

NULL = NULL is false.
0
 
chalie001Author Commented:
but when i test the query in sql developer is working check the query
querydatabasequery.sql
queryinreportbuilder.sql
0
 
sdstuberCommented:
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
0
 
chalie001Author Commented:
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
0
 
sdstuberCommented:
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?
0
 
sdstuberCommented:
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)
0
 
chalie001Author Commented:
i only what to return one value
0
 
chalie001Author Commented:
i only what value for the :PAR_IDNO passed cureently is returning everything i what it to do as in database query
0
 
sdstuberCommented:
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.
0
 
chalie001Author Commented:
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
0
 
sdstuberCommented:
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)
0
 
chalie001Author Commented:
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

Open in new window

0
 
chalie001Author Commented:
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_ID_NO)
0
 
chalie001Author Commented:
check the database query i have hard coded the value
0
 
chalie001Author Commented:
ok will test and get back to you
0
 
sdstuberCommented:
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
0
 
chalie001Author Commented:
(  
    ((: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)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.