I would need some help using Date columns in creating Charts. I'm using APEX 5.0.4. Let me explain what is the problem (in simplified way as possible):
I have Stacked Bar chart with multiple series. For chart series Source I'm using PL/SQL Function Body returning SQL Query. I using this instead of SQL Query because I'm dynamically displaying once only one series and other time multiple series at once, using criteria on the page. So I create my own source string for current scenario using below Function:
Declare
qry VarChar2(4000);
Begin
Case :P10_CRITERIA
When 1 Then -- Display Series1
qry := 'Select
Null Link,
Act\_Id Label,
Act1 Value
From Test\_Chart';
When 2 Then -- Display Series2
qry := 'Select
Null Link,
Act\_Id Label,
Act2 Value
From Test\_Chart';
When 3 Then -- Display Series3
qry := 'Select
Null Link,
Act\_Id Label,
Act3 Value
From Test\_Chart';
Else -- Display All Series
qry := 'Select
Null,
Act\_Id,
Act1, Act2, Act3
From Test\_Chart';
End Case;
Return qry;
End;
Source table look like this (Number @PKey, Date, Number, Number, Number);
My current working function above using for displaying labels column Act_Id, which is number. I would like to display hours here from 00 - 23 for each day. So I change it to below (using column Act_Date):
...
When 2 Then -- Display Series2
qry := 'Select
Null Link,
To\_Char(Act\_Date, "HH24") Label,
Act2 Value
From Test\_Chart';
...
And chart for this SQL criteria doesn't show anymore, but show data normally in SQL Developer where I use ' (single quote) instead of " (double quote). I had to use " instead of ', otherwise SQL won't parse. I think I'm doing something wrong at this place. If I use the same query as SQL Source with ' (single quote) for date formating, it works ok, but only for specific query of course. So I think problem is this " instead of ', but I don't know how to write this portion syntactitly correct. I would please for some help how to write this correct or show me where the actual problem is and how to fix it. Thank you.
BB