Link to home
Start Free TrialLog in
Avatar of chalie001
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
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Which reporting tool are you using ? Have  you mapped the columns properly to the report from database ?
Avatar of chalie001
chalie001

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
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
Avatar of Sean Stuber
A.COURSE_NO           = NVL(:PAR_CNO,A.COURSE_NO)

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

NULL = NULL is false.
but when i test the query in sql developer is working check the query
User generated imagedatabasequery.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
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
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?
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)
i only what to return one value
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.
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
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)
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

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)
check the database query i have hard coded the value
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
(  
    ((: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)