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 previous 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.


 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
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: Fri Mar 29 09:49:53 CDT 2024