Home » Developer & Programmer » Reports & Discoverer » IGNORE NULL or a work around
IGNORE NULL or a work around [message #643504] Fri, 09 October 2015 04:36 Go to next message
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 #643507 is a reply to message #643504] Fri, 09 October 2015 05:13 Go to previous messageGo to next message
John Watson
Messages: 8048
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

I don't understand what you want to achieve. Can you provide a test case? That means the CREATE TABLE statement for your table, and a few INSERT statements to some rows into it. Then show the output you want, and describe the rules for getting it.
Re: IGNORE NULL or a work around [message #643510 is a reply to message #643507] Fri, 09 October 2015 05:31 Go to previous messageGo to next message
KylerStern
Messages: 3
Registered: October 2015
Location: Wales
Junior Member
Hi, thanks for the quick response
I don't understand SQL and no clue what a create table statement is
the second link is invalid.

I cant post any screenshots as the information is sensitive. I'm really not sure how to make it anymore clear than.

1) IGNORE NULLS is not working in my calculation - FIRST_VALUE (test IGNORE NULLS) OVER(PARTITION BY ugdiwg ORDER BY jugdiwhwp)
google says I have it correctly positioned but discoverer does not like it.

2) My table
...DATE...........Chargeable
01-APR-2015..........N
02-APR-2015..........N
03-APR-2015..........Y

I need a formula to extract the first chargeable date into a new column so it would look like.

...DATE...........Chargeable........NEW FORMULA
01-APR-2015..........N..............03-APR-2015
02-APR-2015..........N..............03-APR-2015
03-APR-2015..........Y..............03-APR-2015

With 03-APR-2015 being the first chargeable result that is what I need to populate in the new column. (there are 100ds of dates after it that are also all chargeable)
This way when I place a condition to only show the first row I will have the result i need in that field.

Exactly the same as FIRST_VALUE based on just DATE shows as (below) due to the 1st being the 1st overall date.

...DATE...........Chargeable........NEW FORMULA
01-APR-2015..........N..............01-APR-2015
02-APR-2015..........N..............01-APR-2015
03-APR-2015..........Y..............01-APR-2015



Re: IGNORE NULL or a work around [message #643511 is a reply to message #643510] Fri, 09 October 2015 05:39 Go to previous messageGo to next message
John Watson
Messages: 8048
Registered: January 2010
Location: Global Village
Senior Member
Sorry about the link, here you go:
How to use [code] tags and make your code easier to read

You cannot hope to develop a query such as this if you do not know a simple CREATE TABLE statement, or even simpler INSERT statements. You need to go back to basics,
http://docs.oracle.com/database/121/TDDDG/toc.htm
before attempting he query. THat doc is a pretty good tutorial.
Re: IGNORE NULL or a work around [message #643512 is a reply to message #643511] Fri, 09 October 2015 05:55 Go to previous messageGo to next message
KylerStern
Messages: 3
Registered: October 2015
Location: Wales
Junior Member
Thanks for the link but that document does not answer why my IGNORE NULLS is not being accepted by discoverer or how to if its even possible to collect the first date in a column based on another columns content.
Whilst my skill and knowledge aren't great on the system I would really appreciate some help to the questions I am asking rather than trying to politely tell me that I'm not worth your time and should come back later, i do not have later I'm afraid.

Whilst my examples aren't in your standard format, or copy and pasted sql, all I need to know is why does IGNORE NULLS not work here???
FIRST_VALUE (project.test IGNORE NULLS) OVER(PARTITION BY project.ugdiwg ORDER BY project.jugdiwhwp)
when project.test contains...
NULL
NULL
03-APR-2015

I want the FIRST_VALUE to pick up 03-APR-2015 as the result not NULL.


Thank you.
Re: IGNORE NULL or a work around [message #643513 is a reply to message #643512] Fri, 09 October 2015 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 66684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: IGNORE NULL or a work around [message #643515 is a reply to message #643513] Fri, 09 October 2015 06:55 Go to previous message
Littlefoot
Messages: 21509
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.
Previous Topic: Oracle Web Report exports Error into a XLS with copying Screen in it instead of showing on Screen.
Next Topic: Oracle Report 10g Run from Shorcut
Goto Forum:
  


Current Time: Thu Nov 14 17:41:50 CST 2019