Re: Developer2000 Reports 3.0 and SQL

Hi,
Given you have a :from_date and a :to_date parameter fields, and your table contains a FROM_DATE and TO_DATE containing the given period - and then the following assumption can be said being true: A datarecord is set to be in range, when it's period range is at least partly within the selected period.

Then you just say:

SELECT ... (so and so column)
FROM .... (table/view name)
WHERE ( :FROM_DATE BETWEEN FROM_DATE AND TO_DATE OR

        :TO_DATE BETWEEN FROM_DATE AND TO_DATE
      )

A better (faster) way of doing this is using a UNION, but given you don't confuse your optimizer with too many strange things, like selecting from a view, it should do this by itself.

Btw. there is no difference in your select, syntaxwise, regarding the use of views or tables. They are "the same" when it comes to constructing SQL.

• Peter H. Larsen Oracle Consultant

Cynthia Langevin wrote:
>
> Hi,
>
> Can you tell me how I would check that physician's privileges that are
> contained in a start_date and end_date within a view would be found
> to be within a date_range listed in the parameter form for the user to
> select dates to run the report for?
>
> For example, the date range might be for 01/01/1998 to 01/02/1998 or
> longer and the physician's start_date and end_date for being privileged
> to work at a facility falls within this date range on the parameter
> form?
>
> IS this done in the SQL where clause in the data model or does it
> require functions for the user parameters on the parameter form?
>
> Examples of the where clause are:
> PS physician privilege start_date
> PE physician privilege end_date
> RS range start_date entered by the user
> RE range end_date entered by the user
>
> where (PS < RE) AND (PE < RE) or (PS <= RE) and (PE <= RE)
>
> Thanks in advance for your help.
>
> You can email me direct at clangevin_at_tcipro.com or I will check here
> also daily.
>
> Cynthia

--

Peter H. Larsen, Oracle and Applications specialist Dynamic Resources Inc, Alexandria
Phone: 703-914-9800 #103
Fax: 703-914-9804
Email: plarsen_at_dc.dynares.com
(please remove the nospam from the header when replying to this email)

The above views are my own and does not reflect the views of my employer. Received on Mon Jun 29 1998 - 14:15:21 CDT