| IGNORE NULL or a work around [message #643504] |
Fri, 09 October 2015 04:36  |
 |
KylerStern
Messages: 3
Registered: October 2015
Location: Wales
|
Junior Member |
|
|
Hi,
I have come stuck on a report i'm building and would appreciate any help available.
The report contains the shifts worked by certain individuals, the fields i'm working with are Date (01-APR-2015) and Chargeable. (Y/N)
I cannot put a condition in to filter on Chargeable 'Y' as other parts of the report read from both Y and N.
Where I'm coming stuck.
I need to retrieve the very first and very last dates for each booking but also need the very first and very last date within that booking that is marked as chargeable 'Y" these are always in blocks. It is usually 1-2 days of chargeable 'N' followed by Chargeable 'Y' but there is no guaranteed pattern per booking.
I have FIRST_VALUES working for the first/last of the overall booking per person but when trying to do the same with just the chargeable 'Y' ones I've come to a stand still.
First I tried to find a function to perhaps merge a case statement with the first_value function along the lines of CASE WHEN CHARGEABLE 'Y' THEN FIRST_VALES project.Date etc etc
but it did not like that and i was unable to find anything on google regarding it or a work around.
My next approach was to case statement the date field based on chargeable. (titled 'test')
so CASE WHEN CHARGEABLE 'Y' THEN project.date ELSE NULL END
This game me a new column with all the dates for chargeable "Y" days and NULL values for the others.
Perfect i thought.
FIRST_VALUE (test) OVER(PARTITION BY ugdiwg ORDER BY jugdiwhwp)
Now this brings up the last value correct switching first for last of course.
But the "first" value now displays as NULL.
I googled to see if i could IGNORE NULLS and apparently I can, I just cannot seem to get it to work.
Everything i've read tells me it should look like
FIRST_VALUE (test IGNORE NULLS) OVER(PARTITION BY ugdiwg ORDER BY jugdiwhwp)
But it does not work.
Any thoughts on the best way to get what i'm after?
Table example
...DATE...........Chargeable
01-APR-2015..........N
02-APR-2015..........N
03-APR-2015..........Y
I need FIRST_VALUE to bring back 03-APR-2015 as the first chargeable booking without losing the non chargeable data as its picked up in a different FIRST_VALUE
Thanks for any help out there.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: IGNORE NULL or a work around [message #643515 is a reply to message #643513] |
Fri, 09 October 2015 06:55  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
You use Discoverer. Unfortunately, nobody of the regular forum members uses that tool (at least, as far as I know); we've had one, but she didn't visit the forum for ages. Perhaps she'd know what is going on. I don't.
On the other hand, some Oracle tools I use don't fully support Oracle SQL or PL/SQL. For example, Forms (up to 10g, I think) don't know analytic functions so you have to create a workaround. The usual one is to create a stored (database) function which works properly; then you pass certain parameters to it (so that the function would know what to return).
This sentence:KylerStern
I need to retrieve the very first and very last dates for each booking but also need the very first and very last date within that booking that is marked as chargeable 'Y"
sounds as if you could use pure aggregate MIN and MAX functions. Here's an example:
SQL> alter session set nls_Date_format = 'dd.mm.yyyy';
Session altered.
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
DATUM DATE
UGDIWG NUMBER
CHARGEABLE VARCHAR2(1)
SQL> select * from test;
DATUM UGDIWG C
---------- ---------- -
01.02.2015 1 N
15.03.2015 1 Y
03.04.2015 1 Y
09.10.2015 2 N
31.12.2015 2 Y
02.02.2016 2 Y
14.02.2016 2 N
7 rows selected.
SQL>
If I understood what you are saying, you'd want to return:
- for UGDIWG = 1 (regardless of CHARGEABLE value), min(datum) = 01.02.2015, max(datum) = 03.04.2015
(CHARGEABLE = 1) , min(datum) = 15.03.2015, max(datum) = 03.04.2015
- for UGDIWG = 2 (regardless of CHARGEABLE value), min(datum) = 09.10.2015, max(datum) = 14.02.2016
(CHARGEABLE = 1) , min(datum) = 31.12.2015, max(datum) = 02.02.2016
Queries that returns these values are:
SQL> SELECT ugdiwg, MIN (datum), MAX (datum)
2 FROM test
3 GROUP BY ugdiwg;
UGDIWG MIN(DATUM) MAX(DATUM)
---------- ---------- ----------
1 01.02.2015 03.04.2015
2 09.10.2015 14.02.2016
SQL> SELECT ugdiwg, MIN (datum), MAX (datum)
2 FROM test
3 WHERE chargeable = 'Y'
4 GROUP BY ugdiwg;
UGDIWG MIN(DATUM) MAX(DATUM)
---------- ---------- ----------
1 15.03.2015 03.04.2015
2 31.12.2015 02.02.2016
SQL>
As of a function I mentioned, here's how it might look like:SQL> CREATE OR REPLACE FUNCTION f_minmax (par_ugdiwg IN NUMBER,
2 par_chargeable IN VARCHAR2,
3 par_min_max IN VARCHAR2)
4 RETURN DATE
5 IS
6 l_min DATE;
7 l_max DATE;
8 BEGIN
9 SELECT MIN (datum), MAX (datum)
10 INTO l_min, l_max
11 FROM test
12 WHERE ugdiwg = par_ugdiwg
13 AND (chargeable = par_chargeable OR par_chargeable IS NULL);
14
15 return
16 CASE
17 WHEN par_min_max = 'MIN' THEN l_min
18 WHEN par_min_max = 'MAX' THEN l_max
19 END;
20
21 END;
22 /
Function created.
SQL>
Suppose you want to get MAX(datum) for UGDIWG = 2 which is CHARGEABLE (Y):SQL> select f_minmax(2, 'Y', 'MAX') from dual;
F_MINMAX(2
----------
02.02.2016
Or, MIN(datum) for UGDIWG = 1, regardless of CHARGEABLE value:
SQL> select f_minmax(1, null, 'MIN') from dual;
F_MINMAX(1
----------
01.02.2015
SQL>
I presume that you might call such a function from Discoverer and get values you need.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
