List Info

Thread: Re: Complicated SQL output for report
Re: Complicated SQL output for report
country flaguser name
United States		 2008-04-08 12:50:32 

  


  

    
Thanks ! I just got to know from forum that using row_number (Analytic function )will do the trick. Row_number will give a unique number to all the records in the partition. So everything happens in one SQL and efficient . 




 


On Tue, Apr 8, 2008 at 9:55 AM, Sell, Jonathan C. < selljccf.org">selljccf.org&gt; wrote:








I would execute a PL/SQL procedure (perhaps in a before-report trigger) which would loop through a cursor from your source table/view, picking out the desired records and writing them to a staging table.&nbsp; The Report proper would then select from the staging table.&nbsp; Personally I always launch reports from a Form and generate the report data before calling Reports.



 



Jon




















Jonathan C. Sell  |  Surgery Technology Coordinator   |  Division of Surgery






Cleveland Clinic&nbsp; |  9500 Euclid Ave.   |  Cleveland, OH 44195 ; | (216)  444-1203





 











From:  ml-errorsfatcity.com" target="_blank">ml-errorsfatcity.com [mailto: ml-errorsfatcity.com" target="_blank">ml-errorsfatcity.com] On Behalf Of Amlan Das

Sent: Tuesday, April 08, 2008 12:15 PM
To: Multiple recipients of list ODTUG-WEBDEV-L
Subject: Complicated SQL output for report











 




Hi All,
I need a little SQL hint as to how to get the desired output for a report.
&nbsp;
1) Please assume I have a view  v_permit which has the data and structure like the following. This stores the information for People who have been issued a permit every month.&nbsp; One person can be issued first permit, second permit, third permit based on effective dates.

&nbsp;  
 &nbsp;  Title ; &nbsp;  First Name   ; Last Name   ; Effective date   ; End date
 ; &nbsp;  Mr &nbsp; &nbsp;   ; John   ; &nbsp; &nbsp; &nbsp; &nbsp; Doe &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp; Jan 1, 2008   ; &nbsp; Feb 1, 2008  
 &nbsp; &nbsp; Mr &nbsp; &nbsp;   ; John   ; &nbsp; &nbsp; &nbsp; &nbsp; Doe &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;  feb 2, 2008   ; &nbsp; Mar 1.2008

&nbsp;   ; Mr &nbsp; &nbsp;   ; John   ; &nbsp; &nbsp; &nbsp; &nbsp; Doe &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;  Mar2, 2008   ; &nbsp; Apri 1.2008
&nbsp; &nbsp;  Mr &nbsp; &nbsp;   ; John   ; &nbsp; &nbsp; &nbsp; &nbsp; Doe &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;  Apr 25, 2008   June1.2008
 &nbsp; &nbsp; Ms &nbsp; &nbsp;   Nicole&nbsp; &nbsp; &nbsp; &nbsp;   Smith ; &nbsp; &nbsp; &nbsp; &nbsp;   feb1, 2008   ; &nbsp;  Mar 25, 2008

&nbsp; &nbsp;  Ms &nbsp; &nbsp;   Nicole&nbsp; &nbsp; &nbsp; &nbsp;   Smith ; &nbsp; &nbsp; &nbsp; &nbsp;   Apr1, 2008   ; &nbsp; June 25, 2008
 ; &nbsp;  Ms &nbsp; &nbsp;   Nicole&nbsp; &nbsp; &nbsp; &nbsp;   Smith ; &nbsp; &nbsp; &nbsp; &nbsp;   June1, 2008   ;  Mar 25, 2008
 ; &nbsp;  Ms &nbsp; &nbsp;   Nicole&nbsp; &nbsp; &nbsp; &nbsp;   Smith ; &nbsp; &nbsp; &nbsp; &nbsp;   July 25, 2008   Aug 30, 2008

2) We want to write a Oracle reports and the reports parameter screens has three check boxes that allows the user to select first permit, second or third permit or a combination of all issued to every person. Evrything is based on effective date

&nbsp;E.g, if the user sends First permit as a parameter, the result set should return
&nbsp; &nbsp;  Title ; &nbsp;  First Name   ; Last Name   ; Effective date   ; End date 
 &nbsp; &nbsp; Mr &nbsp; &nbsp;   ; John   ; &nbsp; &nbsp; &nbsp; &nbsp; Doe &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp; Jan 1, 2008   ; &nbsp; Feb 1, 2008  

 &nbsp;   Ms &nbsp; &nbsp;   Nicole&nbsp; &nbsp; &nbsp; &nbsp;   Smith ; &nbsp; &nbsp; &nbsp; &nbsp;   feb1, 2008   ; &nbsp;  Mar 25, 2008
 ;E.g, if the user sends Second permit as a parameter, the result set should return
&nbsp; &nbsp;  Mr &nbsp; &nbsp;   ; John   ; &nbsp; &nbsp; &nbsp; &nbsp; Doe &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;  feb 2, 2008   ; &nbsp; Mar 1.2008

&nbsp;   ; Ms &nbsp; &nbsp;   Nicole&nbsp; &nbsp; &nbsp; &nbsp;   Smith ; &nbsp; &nbsp; &nbsp; &nbsp;   Apr1, 2008   ; &nbsp; June 25, 2008
 ;E.g, if the user sends First and Second permit as a parameters, the result set should return
&nbsp; &nbsp; &nbsp; Mr &nbsp; &nbsp;   ; John   ; &nbsp; &nbsp; &nbsp; &nbsp; Doe &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp; Jan 1, 2008   ; &nbsp; Feb 1, 2008  

 &nbsp;   ; Mr &nbsp; &nbsp;   ; John   ; &nbsp; &nbsp; &nbsp; &nbsp; Doe &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;  feb 2, 2008   ; &nbsp; Mar 1.2008
&nbsp; &nbsp; &nbsp; Ms &nbsp; &nbsp;   Nicole&nbsp; &nbsp; &nbsp; &nbsp;   Smith ; &nbsp; &nbsp; &nbsp; &nbsp;   feb1, 2008   ; &nbsp;  Mar 25, 2008
 ; &nbsp; &nbsp; Ms &nbsp; &nbsp;   Nicole&nbsp; &nbsp; &nbsp; &nbsp;   Smith ; &nbsp; &nbsp; &nbsp; &nbsp;   Apr1, 2008   ; &nbsp; June 25, 2008

&nbsp;
 
 
3) I believe, I will have to get all the records in the  result set and somehow take a count of results returned for all the persons. And then based on the parameter, some how get Ist, 2nd and third letters. But I haven't been able to figure that out.

&nbsp;Can you guys help me with a little direction here 


===================================

P Please consider the environment before printing this e-mail




Cleveland Clinic is ranked one of the top hospitals

in America by U.S. News & World Report (2007).  
Visit us online at http://www.clevelandclinic.org for
a complete listing of our services, staff and
locations.


Confidentiality Note:  This message is intended for use
only by the individual or entity to which it is addressed

and may contain information that is privileged,
confidential, and exempt from disclosure under applicable
law.  If the reader of this message is not the intended
recipient or the employee or agent responsible for
delivering the message to the intended recipient, you are
hereby notified that any dissemination, distribution or
copying of this communication is strictly prohibited.  If
you have received this communication in error,  please
contact the sender immediately and destroy the material in
its entirety, whether electronic or hard copy.  Thank you.






-- 
Amlan 

  

  
  
   
     
    






 [1]











   
 





about | contact  Other archives ( Real Estate discussion Medical topics )