Solved

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

Posted on 2010-09-14
13
474 Views
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
query.txt
0
Comment
Question by:cookiejar
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 2

Expert Comment

by:corobv
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.  
0
 
LVL 2

Expert Comment

by:corobv
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.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 33676386
What kind of parameter are you using?

Does the query work in Oracle?

mlmcc
0
 

Author Comment

by:cookiejar
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.
0
 
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 )

Thanks
0
 

Author Comment

by:cookiejar
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?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 ??
0
 

Author Comment

by:cookiejar
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.
0
 
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...

thanks
0
 

Author Comment

by:cookiejar
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.

0
 
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 *
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
 

Author Comment

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


query2.txt
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 33706254
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.

760 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

20 Experts available now in Live!

Get 1:1 Help Now