Solved

report returning null

Posted on 2016-11-16
21
134 Views
Last Modified: 2016-11-18
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
Comment
Question by:chalie001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 8
21 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41889474
Which reporting tool are you using ? Have  you mapped the columns properly to the report from database ?
0
 

Author Comment

by:chalie001
ID: 41889500
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
 

Author Comment

by:chalie001
ID: 41889504
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 74

Expert Comment

by:sdstuber
ID: 41889678
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
 

Author Comment

by:chalie001
ID: 41889752
but when i test the query in sql developer is working check the query
querydatabasequery.sql
queryinreportbuilder.sql
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 41889766
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
 

Author Comment

by:chalie001
ID: 41889774
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 41889779
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 41889782
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
 

Author Comment

by:chalie001
ID: 41889804
i only what to return one value
0
 

Author Comment

by:chalie001
ID: 41889810
i only what value for the :PAR_IDNO passed cureently is returning everything i what it to do as in database query
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 41889842
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
 

Author Comment

by:chalie001
ID: 41890260
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 41890265
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
 

Author Comment

by:chalie001
ID: 41890271
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
 

Author Comment

by:chalie001
ID: 41890288
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
 

Author Comment

by:chalie001
ID: 41890290
check the database query i have hard coded the value
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 41890299
>>> 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
 

Author Comment

by:chalie001
ID: 41890305
ok will test and get back to you
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 41890312
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
 

Author Closing Comment

by:chalie001
ID: 41891158
(  
    ((: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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question