Return of all rows from a query where the turnus is equal to 1

Question

I have a Oracle Apex function which is giving a sequence of rows in a date range. The items picked in the sequence are soming from a Identifier called TURNUS. For examle:

Date Range: 01-NOV-2016 to 30-NOV-2016
TURNUS is: 7
Return is:
01-NOV-2016
07-NOV-2016
14-NOV-2016
21-NOV-2016
29-NOV-2016

If I change TURNUS to 1, I get a return of:
01-NOV-2016

What I want to see is:
01-NOV-2016
02-NOV-2016
...
30-NOV-2016

To receive the result I am using a query in my Frontend of the Oracle APEX App which is picking data from a view I created in my database.

Let's start with the table of content:

The Database table is:

CREATE TABLE  "GROUPS" 
(   "PK_ID" NUMBER, 
"NAME_OF_GROUP" VARCHAR2(120), 
"SEATS" NUMBER, 
"DATE_FROM" DATE, 
"TURNUS" VARCHAR2(120), 
"CREATE_DATE" DATE, 
"CREATED_BY" VARCHAR2(120), 
"FK_PUBLIC_FUNDING_BODY" NUMBER, 
"VALID_TILL" DATE, 
"DELETE_FLAG" NUMBER, 
"DESCRIPTION" VARCHAR2(300), 
"DATETIME_FROM" TIMESTAMP (6), 
"DATETIME_TILL" TIMESTAMP (6), 
"TIMEFROM" TIMESTAMP (6), 
"TIMETILL" TIMESTAMP (6), 
"DURATION" NUMBER, 
"FK_ROOM_ID" NUMBER, 
"DELETE_DATE" DATE, 
"GROUP_DELETE_REASON" VARCHAR2(255), 
 CONSTRAINT "GROUPS_PK" PRIMARY KEY ("PK_ID")
 USING INDEX  ENABLE
 )

After this I created a view:

CREATE OR REPLACE FORCE VIEW "VIEW_GROUP_DATERANGE" (
"DAY", "PK_ID", "TURNUS", "NAME_OF_GROUP",
"FK_PUBLIC_FUNDING_BODY", "TIME_FROM", "TIME_TILL", 
"DATETIME_FROM", "DATETIME_TILL", "D_FROM", "D_TO") AS 
with 
T_from_to as (
select
trunc(DATETIME_FROM) as d_from, 
trunc(DATETIME_TILL) as d_to,
DATETIME_FROM as DATETIME_FROM,
DATETIME_TILL as DATETIME_TILL,
PK_ID,
TURNUS,
NAME_OF_GROUP,
FK_PUBLIC_FUNDING_BODY
FROM GROUPS
),
T_seq as (
select level-1 as delta
from dual
connect by level-1 <= (select max(d_to-d_from) from T_from_to)
)
select d_from + delta as DAY, PK_ID, TURNUS, NAME_OF_GROUP,
FK_PUBLIC_FUNDING_BODY, TO_CHAR(DATETIME_FROM,'HH.MI') as TIME_FROM,
TO_CHAR(DATETIME_TILL,'HH.MI') as TIME_TILL, DATETIME_FROM, DATETIME_TILL,
d_from, d_to 
from T_from_to, T_seq
where d_from + delta <= d_to
order by day
/

Query in Frontend:

SELECT rownum, ID, DAY, d_from, d_to, TURNUS,
NAME_OF_GROUP
FROM VIEW_GROUP_DATERANGE
WHERE ID= 1 (Variable ID of the Group)
GROUP BY rownum, ID, DAY, d_from, d_to, TURNUS, NAME_OF_GROUP
HAVING MOD(rownum,TURNUS) = 0 OR rownum = 2-1
ORDER BY rownum

The result is showing correctly except of TURNUS = '1'. It just returns one row. I'm currently running out of ideas to get a sequence which returns any single row in a date range when TURNUS is '1'.

I thought and tried some CASE statements but didn't succeed.

Thank you very much in advance.

`having rownum = 2-1` is the same as `rownum = 1`, giving you one row only... I think you want `row_number()` — JohnHC, Dec 09 '16 at 09:14
Please show some sample data and desired results - your script is very hard to read - why do you use group by without any aggregate functions? What are the values in Turnus? what is the desired result with the rownum? So many missing pieces to your puzzle... — Emil Holub, Dec 09 '16 at 09:23
I seriously doubt that you've posted your real code - I bet your view isn't called `VIEW`, for starters. Also, you've used `VIEW` twice - once in the query that presumably selects from `VIEW` and once in the definition of `VIEW` itself. So please post your *real* code, and try to format it in a sensible way. — Frank Schmitt, Dec 09 '16 at 11:32
Actually I was hoping to get some hints on my previous request. Instead I lost my reputation. Anyway I think I wasn't clear enough and deserve it. -I changed the entire request and publish anything I work with. Hope I can get some useful hints instead of being punished further. ;) — fscherbaum, Dec 09 '16 at 11:56

score 0 · Accepted Answer · answered Dec 10 '16 at 13:59

I solved it by adding another column into my view called "MOD_TURNUS" which is having a case statement

T_from_to as (
select
 trunc(DATETIME_FROM) as d_from, 
  trunc(DATETIME_TILL) as d_to,
  DATETIME_FROM as DATETIME_FROM,
  DATETIME_TILL as DATETIME_TILL,
  PK_ID,
  TURNUS,
  CASE WHEN TURNUS > 1 THEN 1 ELSE 0 END as MOD_TURNUS,
  NAME_OF_GROUP,
  FK_PUBLIC_FUNDING_BODY
FROM GROUPS
),
T_seq as (
select level-1 as delta
from dual
connect by level-1 <= (select max(d_to-d_from) from T_from_to)
)
select d_from + delta as DAY, PK_ID, TURNUS, 
CASE WHEN TURNUS > 1 THEN 1 ELSE 0 END as MOD_TURNUS, 
NAME_OF_GROUP,  FK_PUBLIC_FUNDING_BODY, TO_CHAR(DATETIME_FROM,'HH.MI') as TIME_FROM, 
TO_CHAR(DATETIME_TILL,'HH.MI') as TIME_TILL, DATETIME_FROM, DATETIME_TILL, d_from, d_to
from T_from_to, T_seq
where d_from + delta <= d_to
order by day

In my Frontend query I changed the HAVING statement to and added MOD_TURNUS to Group function.

HAVING MOD(rownum,TURNUS) = MOD_TURNUS

...finally so simple

Return of all rows from a query where the turnus is equal to 1

1 Answers1