• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 161
  • Last Modified:

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
0
chalie001
Asked:
chalie001
  • 12
  • 8
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
sdstuberCommented:
>>> 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
 
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 12
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now