return 0 in calculation in query

One of the columns likely contains a null value.  Any math on a null value returns null.

so the outer NVL is causing the 0 to be returned.


Add nvl to all the columns like:

select nvl(sum((nvl(ob_incmpl,0) + nvl(ob_srv,0) +  ...etc...

nvl(
sum(
  (ob_incmpl + ob_srv + ob_rpr + ob_unsrv + ob_partw + str_ccs + str_ccr + str_ccu + str_ccp + str_cci + loss_rsv + dues_in_on_loan + mssl_qty + und_rpr + field_rsv_qty + iss_rsv) * lst_purch_prc
)
,0) TOTAL

Select allOpen in new window

probably one of those items in sum part has null value, and thats why it becomes null
maybe you should add nvl to each and every element...

i did this am still getting null
error123.txt

you are not getting null but 0, means lst_purch_prc is null or 0 for all values

sum(
(nvl(ob_incmpl,0) + nvl(ob_srv,0) + ... + nvl(iss_rsv,0)) * nvl(lst_purch_prc,0)
) TOTAL

Select allOpen in new window

try "select * " and check values...

i did there is values
testdat.sql
data.txt

what does this mean in where part

l.fin_yr = to_number(to_char(to_date(nvl(NULL,NULL), 'yyyy/mm/dd'),'yyyy'))

Select allOpen in new window

That is likely the issue.

If I create your sample tables and run the where clause you posted, I get  "no rows found".

I removed the SUM and multiplication pieces to locate the "bad" data and poof, no data returned.  That means bad where clause.

select nvl(ob_incmpl,0) , nvl(ob_srv,0) , nvl(ob_rpr,0) , nvl(ob_unsrv,0) , nvl(ob_partw,0) , nvl(str_ccs,0) , nvl(str_ccr,0) , nvl(str_ccu,0) , nvl(str_ccp,0) , nvl(str_cci,0)
                   , nvl(loss_rsv,0) , nvl(dues_in_on_loan,0) , nvl(mssl_qty,0) , nvl(und_rpr,0) , nvl(field_rsv_qty,0) , nvl(iss_rsv,0), nvl(lst_purch_prc,0)
   from    item_data    i,
          lmtt_fin_stk l
   where  l.fin_yr                              = to_number(to_char(to_date(nvl(NULL,NULL),                'yyyy/mm/dd'),'yyyy'))
   and    l.unit_cde                            = 10223
   and    nvl(l.ledg_cls,'D')                   = 'D'
   and    l.sin                                 = i.sin
   and    l.stat_ind                            = 'A'
   and    l.lst_rcpt_dte                   is not null
   and    (
              (
                nvl(NULL,'Y') = 'Y'
                and      itm_type_cde                       in ('H','O','Q','S','V','W','Y','Z')
              )
              or      
              (   nvl(NULL,'N') = 'N'
                    and      itm_type_cde      not in ('H','O','Q','S','V','W','Y','Z')
              )
          )
   and    itm_type_cde                          = nvl(NULL,itm_type_cde)
   and    i.niin                                = nvl(NULL,i.niin)
   and    CNTR_BODY_CDE                         = nvl(NULL,CNTR_BODY_CDE)
   and    i.acnt_class_cde                      = nvl(NULL,'E')
   and    nvl(i.asset_ind,'N')                 != 'A' ;

Select allOpen in new window

to_number(to_char(to_date(nvl(sysdate,NULL), 'yyyy/mm/dd'),'yyyy')) from dual

Select allOpen in new window

this return 28...

change that where condition or comment out and see what will happen...

your final query with lots of nvl everywhere, it should return 0 or a number, not null, if at least there is one row...
if you wrap sum with another NVL, it returns 0, even if there is no data

NVL(
  sum(...)
),0) TOTAL

Select allOpen in new window

i must put 1993/01/05 to sysdate

OK then.  What is stopping you?

so, use

to_number(to_char(to_date('1993/01/05', 'yyyy/mm/dd'),'yyyy'))

1993

Select allOpen in new window

but still getting 0

then you dont have data...

instead of sum, put * and see what data you have...
then check sum query...
if you dont have data or null then you will of course get 0

You have to understand that we don't understand your tables or data.  We also don't have the time to dig deep into our setup to fully understand it at your level.

Using your provided test data I ran this query:

select FIN_YR, count(*)
from LMTT_FIN_STK 
group by FIN_YR 
order by FIN_YR;

Select allOpen in new window

Here are the results:

     2001        144
     2002        146
     2003        148
     2004        146
     2005        148
     2006        146
     2007         33
     2008         27
     2009         66
     2010        108
     2011         51
     2012         54
     2013        131
     2014        132
     2015        151
     2016         48
     2017         16
     2018        151

Select allOpen in new window

>>but still getting 0

I would expect the year of 1993 to return a 0.  You don't have any 1993 data.

>>i must put 1993/01/05 to sysdate

This tells me you want a range.  You cannot do that with a '=' in the where clause.

You can use a between query.  Since you know the lower boundary is 1993, no need to all the date conversion stuff.

Just use:
       l.fin_yr between 1993 and to_number(to_char(sysdate,'YYYY'))


Here is the complete query that returns 5039624084 using your data:

select nvl(sum((nvl(ob_incmpl,0) + nvl(ob_srv,0) + nvl(ob_rpr,0) + nvl(ob_unsrv,0) + nvl(ob_partw,0) + nvl(str_ccs,0) + nvl(str_ccr,0) + nvl(str_ccu,0) + nvl(str_ccp,0) + nvl(str_cci,0)
                   + nvl(loss_rsv,0) + nvl(dues_in_on_loan,0) + nvl(mssl_qty,0) + nvl(und_rpr,0) + nvl(field_rsv_qty,0) + nvl(iss_rsv,0)) * nvl(lst_purch_prc,0)),0)TOTAL
   from    item_data    i,
          lmtt_fin_stk l
   where
	 l.fin_yr between 1993 and to_number(to_char(sysdate,'YYYY'))
   and    l.unit_cde                            = 10223
   and    nvl(l.ledg_cls,'D')                   = 'D'
   and    l.sin                                 = i.sin
   and    l.stat_ind                            = 'A'
   and    l.lst_rcpt_dte                   is not null
   and    (
              (
                nvl(NULL,'Y') = 'Y'
                and      itm_type_cde                       in ('H','O','Q','S','V','W','Y','Z')
              )
              or      
              (   nvl(NULL,'N') = 'N'
                    and      itm_type_cde      not in ('H','O','Q','S','V','W','Y','Z')
              )
          )
   and    itm_type_cde                          = nvl(NULL,itm_type_cde)
   and    i.niin                                = nvl(NULL,i.niin)
   and    CNTR_BODY_CDE                         = nvl(NULL,CNTR_BODY_CDE)
   and    i.acnt_class_cde                      = nvl(NULL,'E')
   and    nvl(i.asset_ind,'N')                 != 'A' ;

Select allOpen in new window

i what to return value even if value is passed where  l.fin_yr                              = to_number(to_char(to_date(nvl(:keys.scr_dte_to,:keys.scr_nohlp_dte_to),
                                                                            'yyyy/mm/dd'),'yyyy'))

keys.scr_dte_to=2017/03/31
:keys.scr_nohlp_dte_to=null

even if both value are null i any financial year whic exist what to return

even if both value are null i any financial year whic exist what to return

even if both value are null i any financial year whic exist what to return

even if both value are null i any financial year whic exist what to return

even if both value are null i any financial year whic exist what to return

If you want to take one of two values and return EVERYTHING if both are null then try something like the code below.

It is untested but should be pretty close.

select nvl(sum((nvl(ob_incmpl,0) + nvl(ob_srv,0) + nvl(ob_rpr,0) + nvl(ob_unsrv,0) + nvl(ob_partw,0) + nvl(str_ccs,0) + nvl(str_ccr,0) + nvl(str_ccu,0) + nvl(str_ccp,0) + nvl(str_cci,0)
                   + nvl(loss_rsv,0) + nvl(dues_in_on_loan,0) + nvl(mssl_qty,0) + nvl(und_rpr,0) + nvl(field_rsv_qty,0) + nvl(iss_rsv,0)) * nvl(lst_purch_prc,0)),0)TOTAL
from    item_data    i,
        lmtt_fin_stk l
where
   (
	l.fin_yr = to_number(to_char(to_date(nvl(:keys.scr_dte_to,:keys.scr_nohlp_dte_to),'yyyy/mm/dd'),'yyyy'))
        or nvl((:keys.scr_dte_to,:keys.scr_nohlp_dte_to) is null
   )
   and    l.unit_cde                            = 10223
   and    nvl(l.ledg_cls,'D')                   = 'D'
   and    l.sin                                 = i.sin
   and    l.stat_ind                            = 'A'
   and    l.lst_rcpt_dte                   is not null
   and    (
              (
                nvl(NULL,'Y') = 'Y'
                and      itm_type_cde                       in ('H','O','Q','S','V','W','Y','Z')
              )
              or      
              (   nvl(NULL,'N') = 'N'
                    and      itm_type_cde      not in ('H','O','Q','S','V','W','Y','Z')
              )
          )
   and    itm_type_cde                          = nvl(NULL,itm_type_cde)
   and    i.niin                                = nvl(NULL,i.niin)
   and    CNTR_BODY_CDE                         = nvl(NULL,CNTR_BODY_CDE)
   and    i.acnt_class_cde                      = nvl(NULL,'E')
   and    nvl(i.asset_ind,'N')                 != 'A' ;

Select allOpen in new window

am geting this eror
ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"

   

select * from ils.lmtt_fin_stk 
         where (l.fin_yr = to_number(to_char(to_date(nvl(:keys.scr_dte_to,:keys.scr_nohlp_dte_to),'yyyy/mm/dd'),'yyyy'))
        or nvl((:keys.scr_dte_to,:keys.scr_nohlp_dte_to) is null))

or
   select * from ils.lmtt_fin_stk 
         where (l.fin_yr = to_number(to_char(to_date(nvl(:keys.scr_dte_to,:keys.scr_nohlp_dte_to),'yyyy/mm/dd'),'yyyy'))
        or nvl((:keys.scr_dte_to,:keys.scr_nohlp_dte_to) is null)

Select allOpen in new window

select * from ils.lmtt_fin_stk
         where (l.fin_yr = to_number(to_char(to_date(nvl(:keys.scr_dte_to,:keys.scr_nohlp_dte_to),'yyyy/mm/dd'),'yyyy'))
        or nvl((:keys.scr_dte_to,:keys.scr_nohlp_dte_to) is null))

or
   select * from ils.lmtt_fin_stk
         where (l.fin_yr = to_number(to_char(to_date(nvl(:keys.scr_dte_to,:keys.scr_nohlp_dte_to),'yyyy/mm/dd'),'yyyy'))
        or nvl((:keys.scr_dte_to,:keys.scr_nohlp_dte_to) is null) )

>>am geting this eror

Helena seems to have corrected this but I wanted to mention that was a pretty simple syntax error to correct.  Understand what the SQL is doing and the syntax error should reveal itself.

the query still not working

Define "not working".  Is there still a syntax issue?  Does it cause your keyboard to catch fire?

If you mean it still returns a 0, then you need to explain your exact requirements better.  

Even though you posted it 5 times, I don't understand what you mean by "even if both value are null i any financial year whic exist what to return

OK, do you understand the functions being used?  There is an extra paran on the NVL call.  Understanding the functions will help you track down syntax errors.

Get into the habit of indenting nested function calls and they syntax issues sort of jump out at you:  You will be able to see where you have an extra starting or missing ending paran.

See if this fixes the issue.

select nvl(sum((nvl(ob_incmpl,0) + nvl(ob_srv,0) + nvl(ob_rpr,0) + nvl(ob_unsrv,0) + nvl(ob_partw,0) + nvl(str_ccs,0) + nvl(str_ccr,0) + nvl(str_ccu,0) + nvl(str_ccp,0) + nvl(str_cci,0)
                   + nvl(loss_rsv,0) + nvl(dues_in_on_loan,0) + nvl(mssl_qty,0) + nvl(und_rpr,0) + nvl(field_rsv_qty,0) + nvl(iss_rsv,0)) * nvl(lst_purch_prc,0)),0)TOTAL
from    item_data    i,
        lmtt_fin_stk l
where
   (
		l.fin_yr = to_number(
			to_char(
				to_date(
					nvl(:keys.scr_dte_to,:keys.scr_nohlp_dte_to)
				,'yyyy/mm/dd')
			,'yyyy')
		)
       or nvl(:keys.scr_dte_to,:keys.scr_nohlp_dte_to) is null
   )
   and    l.unit_cde                            = 10223
   and    nvl(l.ledg_cls,'D')                   = 'D'
   and    l.sin                                 = i.sin
   and    l.stat_ind                            = 'A'
   and    l.lst_rcpt_dte                   is not null
   and    (
              (
                nvl(NULL,'Y') = 'Y'
                and      itm_type_cde                       in ('H','O','Q','S','V','W','Y','Z')
              )
              or      
              (   nvl(NULL,'N') = 'N'
                    and      itm_type_cde      not in ('H','O','Q','S','V','W','Y','Z')
              )
          )
   and    itm_type_cde                          = nvl(NULL,itm_type_cde)
   and    i.niin                                = nvl(NULL,i.niin)
   and    CNTR_BODY_CDE                         = nvl(NULL,CNTR_BODY_CDE)
   and    i.acnt_class_cde                      = nvl(NULL,'E')
   and    nvl(i.asset_ind,'N')                 != 'A' ;

Select allOpen in new window