Solved

PLSQL procedure help with decode and null input params

Posted on 2016-11-14
5
44 Views
Last Modified: 2016-12-21
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.
0
Comment
Question by:thamilto0410
5 Comments
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41886855
why are you limiting your end date to the year 2099 ?
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
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 41886860
which fields are your parameters?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 41886923
Yes, your decode is wrong.  I also don't really know exactly what you are trying to do with them but they are definitely wrong.  I don't think you need them so I'll not attempt to debug them for you.

If I read between the lines:
You have a procedure and some of the parameters can be null and you want the query to be able to execute properly?

I also have no idea where your parameters are in that query.

Let's try it this way:
If you have a parameter named p_my_input and want to use it in the where clause and account for the NULL then try this:

select ...
where (p_my_input is null or a.column_name like '%' || p_my_input || '%') and (...the rest of them...)

Note the parenthesis.  They are important because of the OR
1
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 41886956
-- very ancient join syntax
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(+)

-- standard join syntax, seperates from where clause
select count(distinct a.note_id)
    from table1 a
    left join table2 b on a.note_id=b.note_id
    left join table3 c on a.note_id=c.note_id

where  a.start_dt >= to_date('01/01/2010', 'MM/DD/YYYY')  
        and  a.end_dt <= to_date('12/31/2099', 'MM/DD/YYYY')

        and  (case when p_cat IS NULL OR b.category_cd = p_cat )    -- each parameter can be handled this way (as also shown immediately above
1
 
LVL 1

Author Closing Comment

by:thamilto0410
ID: 41887125
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
alter database link to change the password 2 45
query in Oracle forms Builder 2 41
Oracle - Create Procedure with Paramater 16 56
passing parameters to sql script oracle 4 26
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

914 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

13 Experts available now in Live!

Get 1:1 Help Now