CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle-apex
Usman YousafZai
Usman YousafZai

Reputation: 1120

Report "Query By Date" APEX

I am working in Oracle APEX.I want to make report of previous month patients treated by doctor from two tables i-e Patient and History.enter image description here

Pat_id is Primary key in Patient table and foreign key in History Table.

I want report that should show me Pat_Name ,Pat_Age ,Treated_By and Date where as i can also select month from LOV(List of Value) and on the basis of that month it should show me the report.

Kindly Help me out Thanks,

Upvotes: 1

Views: 1188

Answers (1)

Tom
Tom

Reputation: 7028

SELECT p.pat_name, p.pat_age, h.treated_by, h.date
  FROM patient p
  JOIN history h
    ON p.pat_id = h.pat_id
 WHERE EXTRACT(MONTH FROM h.date) = TO_NUMBER(:P1_MONTH)

(a really basic join condition really)
If you want ordering, you could include this in the sql, or define this in the report definition of a classic report, or as defaults in an interactive report.

P1_MONTH : a select list. As for its list of values, you can either define the months statically (12 entries with month number as return value and month name as display), or use a query:

SELECT to_char(add_months(to_date('01/01/2012','DD/MM/YYYY'),LEVEL-1),'Month') display_value, LEVEL return_value
FROM dual
CONNECT BY LEVEL <= 12

Set the select list to submit on change.

As an extra note: set your column names to be UPPERCASE in your schemas. Columns are always uppercase unless they are defined otherwise upon creation. Also be careful with using reserved keywords for things such as a column name: DATE may not be the best name for a column... Give it a useful name such as TREATED_ON

Upvotes: 2

Related Questions