Solved

report returning null

Posted on 2016-11-16
21
50 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
  • 12
  • 8
21 Comments
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
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
 
LVL 73

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 73

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 73

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 73

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 73

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 73

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 73

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 73

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now