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

Posted on 2010-09-14
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
  • 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 100

Expert Comment

ID: 33676386
What kind of parameter are you using?

Does the query work in Oracle?

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.


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 500 total points
ID: 33706254
test it out :

select xyz.*
from (
SELECT distinct  
       d.department,            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', > 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

733 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