OraFAQ Forum: Application Express, ORDS & MOD_PLSQL » Showing Stats on data with 2 Input Date Fields

Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Showing Stats on data with 2 Input Date Fields (Apex 5.1.0.00.45, Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Showing Stats on data with 2 Input Date Fields [message #665928]

Tue, 03 October 2017 03:30

JanCoetzee
Messages: 8
Registered: September 2017

Junior Member

Hi

I have a table with 2 date fields and other columns. I have to be able to show stats on some of these columns between 2 dates that can be selected from a dropdown list.
All of this must be done in Apex. The client must be able to select a Start Date and an End date and then the count of for instance the number of Referrals between 01/SEP/17 and 30/SEP/17 must be shown.
The SQL code I used in Oracle to achieve this is:

select 'Total Referrals' as Details, count(REFERRED) as Total from PD_PATIENT_DETAILS where REFERRED = 'Yes'
and EVENT_DATE BETWEEN to_date(:EVENT_DATE) AND to_date(:EVENT_DATE_END);

I am now struggling to get this to work in Apex. Like I said before I am new to Apex and don't quite know where to start with this.

Report message to a moderator

Re: Showing Stats on data with 2 Input Date Fields [message #665930 is a reply to message #665928]

Tue, 03 October 2017 04:14

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

You need to show the definition of the table. Does it really have a column named "Total Referrals"? Also the error: "struggling to get this to work" is not an Oracle error message with which I am familiar. Use copy/paste, and enclose it all within [code] tags as described here, How to use [code] tags and make your code easier to read
One point - your TO_DATE functions need to include a formatting expression that will tell Oracle how to interpret the bind variable or you may get this sort of thing,

orclx> select to_date('03-OCT-17') from dual;

TO_DATE('03-OCT-17'
-------------------
0003-10-17:00:00:00

orclx>

Report message to a moderator

Re: Showing Stats on data with 2 Input Date Fields [message #665932 is a reply to message #665930]

Tue, 03 October 2017 04:22

JanCoetzee
Messages: 8
Registered: September 2017

Junior Member

Here is the table definition:

CREATE TABLE "PR_PINK_DRIVE"."PD_PATIENT_DETAILS"
( "EVENT_DATE" DATE,
"EVENT_VENUE" VARCHAR2(100 BYTE),
"FIRST_NAME" VARCHAR2(50 BYTE),
"LAST_NAME" VARCHAR2(50 BYTE),
"DATE_OF_BIRTH" DATE,
"ID_NUM" VARCHAR2(50 BYTE),
"AGE" VARCHAR2(50 BYTE),
"GENDER" VARCHAR2(8 BYTE),
"CONTACT_NUM" VARCHAR2(32 BYTE),
"EMAIL" VARCHAR2(50 BYTE),
"STREET_NAME" VARCHAR2(100 BYTE),
"SUBURB" VARCHAR2(100 BYTE),
"CITY" VARCHAR2(100 BYTE),
"POSTAL_CODE" NUMBER(6,0),
"PROVINCE" VARCHAR2(40 BYTE),
"ETHNICITY" VARCHAR2(10 BYTE),
"EVENT_CITY" VARCHAR2(40 BYTE),
"STREET_NUMBER" VARCHAR2(6 BYTE),
"REFERRED" VARCHAR2(250 BYTE),
"FEEDBACK/FOLLOW-UP" NVARCHAR2(250),
"FAMILY_HISTORY_OF_BC" VARCHAR2(250 BYTE),
"REFERRAL_REASON" VARCHAR2(250 BYTE),
"FAMILY_HISTORY_OF_BC_YN" VARCHAR2(5 BYTE),
"BREAST_EXAM" VARCHAR2(5 BYTE),
"BREAST_EDU" VARCHAR2(5 BYTE),
"BREAST_REFERRAL" VARCHAR2(5 BYTE),
"MAMMOGRAM_EXAM" VARCHAR2(5 BYTE),
"MAMMOGRAM_EDU" VARCHAR2(5 BYTE),
"MAMMOGRAM_REFERRAL" VARCHAR2(5 BYTE),
"PSA_EXAM" VARCHAR2(5 BYTE),
"PSA_EDU" VARCHAR2(5 BYTE),
"PSA_REFERRAL" VARCHAR2(5 BYTE),
"PAP_SMEAR_EXAM" VARCHAR2(5 BYTE),
"PAP_SMEAR_EDU" VARCHAR2(5 BYTE),
"PAP_SMEAR_REFERRAL" VARCHAR2(5 BYTE),
"EVENT_DATE_END" DATE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;

This is one of the selects that I have to do on this table and show in Apex:

select
'Total Referrals' as Details,
count(REFERRED) as Total
from PD_PATIENT_DETAILS
where REFERRED = 'Yes'
and EVENT_DATE BETWEEN to_date(:EVENT_DATE) AND to_date(:EVENT_DATE_END);

Report message to a moderator

Re: Showing Stats on data with 2 Input Date Fields [message #665935 is a reply to message #665932]

Tue, 03 October 2017 06:21

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

You have not enclosed your copy/paste within [code] tags.
You have not corrected your code to include a format string for the bind variables.
You have not shown what problem you are having.

Report message to a moderator

Re: Showing Stats on data with 2 Input Date Fields [message #665959 is a reply to message #665935]

Wed, 04 October 2017 14:56

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Well, there's no Total Referrals column in that table so it just won't work.

As of the WHERE clause: EVENT_DATE and EVENT_DATE_END are (should be) date picker items. Set their format mask (for example, to dd.mm.yyyy). Then, in report's query, use TO_DATE (as John has already told you) with the same format mask you used in item's property:

where event_date between to_date(:event_date, 'dd.mm.yyyy') and to_date(:event_date_end, 'dd.mm.yyyy')

Report message to a moderator

Previous Topic:	Field replacement
Next Topic:	change keyboard language when change textfiled

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 10 14:33:05 CST 2025