Solved

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

Posted on 2010-09-14
13
481 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
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.

 

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

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

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…
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

809 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