Oracle Reports 10g - User Parameters 1

[ejeffcott]

I have 2 user parameters:

One that allows user to select the office.

Here is the code:

select DISTINCT e.lastname

from t_entity e INNER JOIN t_entityrelationship er
on e.entityid = er.childentityid
INNER JOIN t_relationshiptype r
on er.childrelationshiptypeid = r.relationshiptypeid

where er.parentrelationshiptypeid in
  (select r.relationshiptypeid 
    from t_relationshiptype r
      where UPPER(r.relationshiptypedesc) = 'CASE')

and UPPER(r.relationshiptypedesc) = 'OFFICE'
and e.isPrimary = 'Y'

and the other allows the user to select Attorney:

select DISTINCT e.initials

from t_entity e INNER JOIN t_entityrelationship er
on e.entityid = er.childentityid
INNER JOIN t_relationshiptype r
on er.childrelationshiptypeid = r.relationshiptypeid

where er.parentrelationshiptypeid in
  (select r.relationshiptypeid
    from t_relationshiptype r
      where UPPER(r.relationshiptypedesc) = 'CASE')

and UPPER(r.relationshiptypedesc) = 'ATTORNEY'
and e.isPrimary = 'Y'

I reference them in the main query like so:

where office.office = :office
or :office is NULL
and WLFattorney.initials = :attorney	
or :attorney is NULL

The problem I'm running into is that the user can select any attorney and it processes corretly. However, if user selects an office, it brings back all offices.

The only thing i can think of is that attorney & office are apart of a hierarchial operation: Attorney -> Lawfirm -> Office.

However, the parameters are not associated (and i don't even know if you can link paramaters) so I don't believe that the office/attorney relationship matters.

I'm new to oracle and sql, I apologize if this is not clear.

Thank you.

 

[nagornyi]

Could you please clarify why you are givinig those two queries while talking about the parameters. The queries are not referencing any parameters. What is the main query? How are they related?

 

[ejeffcott]

Instead of entering static values for the parameters, I entered a Select query to define each user parameter.

The query in which my report is based is define below

select DISTINCT maybe.casename, maybe.casenumber, maybe.intake, maybe.DOI, maybe.SOL, maybe.venue, maybe.casetype, maybe.feesplit, maybe.totalfee,
	replace(maybe.casesummary, chr(13)|| chr(10),'') casesummary, office ||'  '|| maybe.status as casestatus,
	WLFattorney.initials, comments.commentdt, comments.comments, referralattorney.referralname
	
	from
			(select c.casename as casename, c.casenumber as casenumber, c.casesummary as casesummary, c.intakedt as intake, c.incidentdt as DOI,
					c.statueoflimitationdt as SOL, c.venue as venue, cs.casestatusdesc ||' - '|| css.casesubstatusdesc as status, c.wlfpercentage ||'/'||c.referralpercentage as feesplit,
					ct.casetypedesc ||'-'|| cst.casesubtypedesc as casetype, c.caseid, c.totalfeepercentage as totalfee
					
					from t_case c INNER JOIN t_casestatus cs
					on cs.casestatusid = c.casestatusid
                                                                                LEFT OUTER JOIN t_casesubstatus css
                                                                                on c.casesubstatusid = css.casesubstatusid
					INNER JOIN t_casetype ct
					on ct.casetypeid = c.casetypeid
					LEFT OUTER JOIN t_casesubtype cst
					on cst.casesubtypeid = c.casesubtypeid
					
					   where UPPER(cs.casestatusdesc) like '%MAYBE%'
					   and UPPER(ct.casetypedesc) != 'PHARMACEUTICAL'
			) maybe 

INNER JOIN

			(select e.lastname as office, er.caseid
					
					from t_entity e INNER JOIN t_entityrelationship er
					on e.entityid = er.childentityid
					INNER JOIN t_relationshiptype r
					on er.childrelationshiptypeid = r.relationshiptypeid
					
					   where er.parentrelationshiptypeid in
					   		 (select r.relationshiptypeid
							 		 from t_relationshiptype r
									 	  where UPPER(r.relationshiptypedesc) = 'CASE')
					   and UPPER(r.relationshiptypedesc) = 'OFFICE'
					   and e.isPrimary = 'Y'
					   and e.lastname != 'MT'
			) office 
                                                 
     on maybe.caseid = office.caseid
     LEFT OUTER JOIN 
			
			(select e.initials as initials, er.caseid
			
					from t_entity e INNER JOIN t_entityrelationship er
					on er.childentityid = e.entityid
					INNER JOIN t_relationshiptype r
					on er.childrelationshiptypeid = r.relationshiptypeid 
					
					   where er.parentrelationshiptypeid in
					   		 (select r.relationshiptypeid
							 		 from t_relationshiptype r
									 	  where UPPER(r.relationshiptypedesc) = 'CASE')
						and UPPER(r.relationshiptypedesc) = 'ATTORNEY'
						and e.isPrimary = 'Y'
			) WLFattorney

     on maybe.caseid = WLFattorney.caseid
     LEFT OUTER JOIN
			
			(select e.firstname ||' '|| lastname as referralname, er.caseid
			
					from t_entity e INNER JOIN t_entityrelationship er
					on e.entityid = er.childentityid
					INNER JOIN t_relationshiptype r
					on er.childrelationshiptypeid = r.relationshiptypeid
					
					   where er.parentrelationshiptypeid in
					   		 (select r.relationshiptypeid
							 		 from t_relationshiptype r
									 	  where UPPER(r.relationshiptypedesc) = 'CASE')
						and UPPER(r.relationshiptypedesc) = 'REFERRAL ATTORNEY'
			) Referralattorney
   
     on maybe.caseid = Referralattorney.caseid
     LEFT OUTER JOIN			
			(select c.commentdt, c.comments as comments, cc.caseid
								 
					from t_casecomment cc INNER JOIN t_comment c
					on cc.commentid = c.commentid
			)comments
  
     on maybe.caseid = comments.caseid		

     where office.office = :office
     or :office is NULL
     and WLFattorney.initials = :attorney	
     or :attorney is NULL
                                       
                                           
			
			order by comments.commentdt desc

I am trying to create parameters that would allow the user to choose by office or attorney.

I hope this clears things up.

 

[nagornyi]

You probably meant this in the where clause:

where
(office.office = :office or office.office is NULL)
and 
(WLFattorney.initials = :attorney or WLFattorney.initials is NULL)

 

[ejeffcott]

Thank you! This worked perfectly.

One more question, do you know of any good writte resources where I can learn to use Oracle Forms.

I am having a difficult time figuring out the basics to the tool.

Again, thank you.

 

[nagornyi]

The central place for information about Forms is here

http://www.oracle.com/technology/products/forms/index.html

For questions about Forms you better go to forum259