Return matched rows for an entered parameter value in an outer join

Posted on 2010-09-14
Medium Priority
Last Modified: 2013-11-11
In an outer join query, if the user enters a value in a paramter, it returns all of the records. What I would like it to do is to return only the rows that match the entered value.  If 'ALL' is entered,  return all rows.  Is there a way that I can reconstruct my query to do this?

NOTE: We have function that decodes the value entered by the user, if the user enters ALL the function returns %.  If they enter a value it returns the entered value.

See the atached query
Question by:cookiejar
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
  • 5
  • 5
  • 2
  • +1

Expert Comment

ID: 33676186
You can use this:

( {?parameter} = 'ALL' Or {TABLE.FIELDNAME} in {?parameter} )

If your field is a numeric field the just use 0 or a number that won't be in the field for the all records option.

( {?parameter} = 0 Or {TABLE.FIELDNAME} in {?parameter} )

Or in your case the %.

( {@Value} = '%'  Or {TABLE.FIELDNAME} = {@VALUE} )

Just remember to put the parenthesis () when you use the OR expression.  

Expert Comment

ID: 33676277
Sorry, just a note: my example is for directly in the select criteria in a Crystal report.  There is a little modification to convert it to a Query format, but the basics should be the same.  

I can test it out and give you the exact format if you need it.
LVL 101

Expert Comment

ID: 33676386
What kind of parameter are you using?

Does the query work in Oracle?

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 33677246
I am trying to get it to work in ORACLE.  I am able to filter in Crystal but would like to filter it in ORACLE.  In ORACLE is where it is returning all rows, even though a specific value is entered in the value.  I would like to get a response from an ORACLE expert.
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33678331
the last line of it will look like as given below :

 and nvl(mtl_p.mtl_program_id,'%') LIKE  decode(parameter_value,'ALL','%',parameter_value )

the parameter_value is a plsql variable. not sure whether you can use that in your crystal report query.

Does & work like sql*plus in your work which can prompt for user value entered and take it accordingly or & cannot be used in your query.. if & can work, then we can also try :

 and nvl(mtl_p.mtl_program_id,'%') LIKE  decode(&&param,'ALL','%',&&param )


Author Comment

ID: 33678372
I pasted the example that you gave me but it brings back the matching rows and the rows that have a null mtl_program_id. Is it because of the left outer join?
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33678400
ok.. so that is working. which one did you use ? && or plsql input variable ?

yes, i can think that could be due to left outer joins. Just take the query and run in toad / sqlplus to understand for a given sample values to see whether that query is getting nulls there.

if we get nulls there, then we consider that as % because we have used NVL(..) right.

does it get nulls there when you give a parameter value say 13 or when you give the parameter value as ALL ??

Author Comment

ID: 33683545
For example when I enter 13,  it returns the records with 13 and also nulls. It behaves as if I am always entering %.  I am running the SQL in Toad and inputting the program_id.
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33687791
it is due to left outer join. i have already told you on how you can fix it. i will not be able to help to exactly fit it because i do not have your tables/data.

ok..give me some more information... what column is coming as null in your output because i see that column "mtl_p.mtl_program_id" is not there in your select list at all...


Author Comment

ID: 33692992
Sorry,  when I sent the select statement I deleted some columns out of the query and the mtl_program_id was one of them.  Forgive my ignorance, but I used your recommendation:  and nvl(mtl_p.mtl_program_id,'%') LIKE  decode(&&param,'ALL','%',&&param ).  AS I mentioned, it does return the matched row when I enter a value  but  it also returns those that have no value.  Maybe I missed something in your recommended fix.

LVL 28

Expert Comment

by:Naveen Kumar
ID: 33697712
Can you give me your correct sql ( comletely ) ? i will then modify it to get those null records eliminated.

select *
( whatever is your correct query ) x
where x.mtl_program_id is not null;

This is just another way to get rid of your issue as i do not have your data model/designs to fix the actual left outer join issue.


Author Comment

ID: 33702916
I have attached the query.  
Using your second recommendation will return the expected results.

LVL 28

Accepted Solution

Naveen Kumar earned 2000 total points
ID: 33706254
test it out :

select xyz.*
from (
SELECT distinct  
       mdmat.name            accred_type,
       (SELECT  NAME
        FROM mtl_accred_versions
           WHERE mtl_accreditation_id = mtlav.mtl_accreditation_id
             AND version_status = 'PRODUCTION') title,
--Use Granted On date if Completed date is missing:
      Decode(itltl.completed_date,NULL,itla.granted_on_date,itltl.completed_date) comp_or_grant_date,
--If the date delta is zero (started and completed same day), make Days To Qualify (DTQ) = 1:  
        Decode(itltl.completed_date,NULL,itla.granted_on_date,itltl.completed_date)) - Trunc(itltl.start_date))),
        0,1, To_Number(Decode(itltl.start_date,NULL,NULL,Trunc(
        Decode(itltl.completed_date,NULL,itla.granted_on_date,itltl.completed_date)) - Trunc(itltl.start_date))))  DTQ,
      itltl.status_date     last_update,
      itltl.status             tl_status,
      itl_accreditations   itla
   JOIN   mtl_accred_versions  mtlav
      ON  mtlav.mtl_accred_version_id = itla.mtl_accred_version_id
   JOIN  mdm_accred_types     mdmat    
     ON  mtlav.mdm_accred_type_id    = mdmat.mdm_accred_type_id
   JOIN   mtl_accreditations mtl_a  
     ON mtl_a.mtl_accreditation_id      = mtlav.mtl_accreditation_id
   JOIN departments     d
    ON itla.emp_id       = d.emp_id
    AND  (d.department LIKE '%TRC%')
    AND instr('QUALLICENSECERT', mdmat.name) > 0
    AND (itla.accreditation_status = 'ACTIVE' OR    (itla.status_reason = 'EXPIRED' and itla.granted_on_date >= d.assignment_start_date))    
  JOIN role_check_vw rc
   ON d.department = rc.department
   AND rc.usr_user_id = '152'  
  LEFT JOIN   itl_tasklists        itltl  
     ON   itla.itl_accreditation_id   = itltl.itl_accreditation_id
     AND itla.emp_id       = itltl.per_emp_id
  LEFT JOIN per_person_programs pp
   ON d.emp_id                     = pp.emp_id  
  LEFT JOIN mtl_accred_programs map
   ON  mtl_a.mtl_accreditation_id  =   map.mtl_accreditation_id  
   AND map.mtl_program_id        =  pp.mtl_program_id
   AND nvl(map.mtl_program_id,'%') LIKE   decode('6ed2e42a','ALL','%','6ed2e42a')
) xyz
where xyz.mtl_program_id is not null;

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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
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.
Suggested Courses

771 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