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

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
query.txt
cookiejarAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
test it out :

select xyz.*
from (
SELECT distinct  
       p.per_name,
       itla.accreditation_status,        
       d.department,
       mdmat.name            accred_type,
       mtlav.mtl_accreditation_id,
       (SELECT  NAME
        FROM mtl_accred_versions
           WHERE mtl_accreditation_id = mtlav.mtl_accreditation_id
             AND version_status = 'PRODUCTION') title,
      itltl.start_date,
--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,
      itla.expiration_date,    
      itltl.percent_complete,
--If the date delta is zero (started and completed same day), make Days To Qualify (DTQ) = 1:  
      Decode(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))),
        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,
      p.active,
      map.mtl_program_id  
   FROM
      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;
0
 
corobvCommented:
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.  
0
 
corobvCommented:
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.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
mlmccCommented:
What kind of parameter are you using?

Does the query work in Oracle?

mlmcc
0
 
cookiejarAuthor Commented:
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.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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 )

Thanks
0
 
cookiejarAuthor Commented:
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?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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 ??
0
 
cookiejarAuthor Commented:
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.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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...

thanks
0
 
cookiejarAuthor Commented:
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.

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Can you give me your correct sql ( comletely ) ? i will then modify it to get those null records eliminated.

select *
from
( 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.

Thanks,
0
 
cookiejarAuthor Commented:
I have attached the query.  
Using your second recommendation will return the expected results.


query2.txt
0
All Courses

From novice to tech pro — start learning today.