thamilto0410
asked on
PLSQL procedure help with decode and null input params
Good Day,
I need some help. What is below is part of a stored procedure.
select count(distinct a.note_id)
from table1 a, table2 b, table3 c
where a.note_id=b.note_id(+)
and a.note_id=c.note_id(+)
and b.category_cd = decode(null, '', b.category_cd, null)
and a.start_dt >= to_date('01/01/2010', 'MM/DD/YYYY')
and a.end_dt <= to_date('12/31/2099', 'MM/DD/YYYY')
and a.archive_dt = decode('', '', a.archive_dt, '')
and a.active_flag = decode(null, '', a.active_flag, null)
and a.created_by = decode(null, '', a.created_by, null)
and c.nr = decode(null, '', c.nr, null)
and upper(a.comments) like '%' || upper(decode(null, null, a.comments, null)) || '%'
and (upper(a.note_title) like '%' || upper(decode('NOTE', '', a.note_title, 'NOTE')) || '%'
or upper(a.note_title) like '%' || upper(decode('NOTE', '', a.note_body, 'NOTE')) || '%') order by a.start_dt desc;
Some info: all input params except 1 can be null. Which is why I phrased it as above. I am brand new to procedures and decode.
Am I using the wrong function with decode? It needs to be in the where clause and the procedure gets called from a webform. All input parameters can be blank but 1.
Field types are almost all varchars except archive_dt which is date and nr which is a number field. And also note_body is a clob.
Could it be my parameters in decode?
Not getting accurate record count. Can someone assist.
I need some help. What is below is part of a stored procedure.
select count(distinct a.note_id)
from table1 a, table2 b, table3 c
where a.note_id=b.note_id(+)
and a.note_id=c.note_id(+)
and b.category_cd = decode(null, '', b.category_cd, null)
and a.start_dt >= to_date('01/01/2010', 'MM/DD/YYYY')
and a.end_dt <= to_date('12/31/2099', 'MM/DD/YYYY')
and a.archive_dt = decode('', '', a.archive_dt, '')
and a.active_flag = decode(null, '', a.active_flag, null)
and a.created_by = decode(null, '', a.created_by, null)
and c.nr = decode(null, '', c.nr, null)
and upper(a.comments) like '%' || upper(decode(null, null, a.comments, null)) || '%'
and (upper(a.note_title) like '%' || upper(decode('NOTE', '', a.note_title, 'NOTE')) || '%'
or upper(a.note_title) like '%' || upper(decode('NOTE', '', a.note_body, 'NOTE')) || '%') order by a.start_dt desc;
Some info: all input params except 1 can be null. Which is why I phrased it as above. I am brand new to procedures and decode.
Am I using the wrong function with decode? It needs to be in the where clause and the procedure gets called from a webform. All input parameters can be blank but 1.
Field types are almost all varchars except archive_dt which is date and nr which is a number field. And also note_body is a clob.
Could it be my parameters in decode?
Not getting accurate record count. Can someone assist.
which fields are your parameters?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Again thank you SO MUCH! It is help like what you both offered that keeps some of us as members because we cannot always find what we need in the search with the additional assistance I got I learned something new and will use it in the future I am sure.
you don't want this to work after your dead ?
and how do you know you'll only live until 2099 ?
the decode you use is indeed wrong.
the syntax is here:
http://docs.oracle.com/database/122/SQLRF/DECODE.htm#SQLRF00631
what are you trying to with this line ?
and a.archive_dt = decode('', '', a.archive_dt, '')
you are comparing a column with itself ... it's bound to be equal to itself within the same line
there are no input parameter in this query