Small SQL puzzle - discrete top 3

Being the APEX geek I am I've created a page that lists all the entrants so far to the Packt Publishing Phonegap ebook giveway

Check it out here
http://apex.oracle.com/pls/apex/f?p=73000:22

I'll post more about how I've extrapolated records from derived column values, but I am looking for suggestions to improve the 'prize?' column

This is the current calculation - basically highlighting first three records in output.
case when row_number() over (order by dbms_random.value()) <= 3 then 'maybe?' end prize
But what if two of those records share the same name?

Here is some test data, where socrates, plato & yeats need to win the prize

with data as
 (select 'socrates'  name, 1 entry, 1 ord from dual union all
  select 'plato'     name, 1 entry, 2 ord from dual union all
  select 'socrates'  name, 2 entry, 3 ord from dual union all
  select 'yeats'     name, 1 entry, 4 ord from dual union all
  select 'aristotle' name, 1 entry, 5 ord from dual)
select case when row_number () over (order by ord)  <= 3 then 'maybe?' end prize
  ,name
from data
order by ord

Go nuts

Win a free ebook of PhoneGap 3 by Packt

PhoneGap 3 Beginner's Guide

Packt publishing have generously given me the opportunity to give away 3 copies of their e-book PhoneGap 3 Beginner's Guide, by Giorgio Natili.

Details on how to enter are further below.

Update (2013-12-19): Created APEX page displaying current entrants and puzzle regarding selection process.

You don't think you need to be an APEX developer to appreciate this book, but I will be reviewing this book in greater detail over the new year as I plan to use it to help me deploy Oracle APEX applications as a hybrid mobile application.

Looking through the contents, I see plenty opportunities to improve your APEX applications by using PhoneGap - a free, open licensed distribution of Apache Cordova.

Chapter 1: Getting Started with PhoneGap
Exactly that - understand what PhoneGap is and what you need depending on your device.

Chapter 2: Building and Debugging on Multiple Platforms
A little deeper preparation for how we might debug issues, even desktop debugging since APEX developers are building hybrid applications.

Chapter 3: Getting Started with Mobile Applications
A good look at some web development basics everyone should be familiar with - JavaScript, CSS.

Chapter 4: Architecting Your Mobile App
Might be a bit heavy for us APEX developers, but we may end up being able to hook in our APEX landing page using this information.

Chapter 5: Improving the User Interface and Device Interaction
I think some lessons in here will be deeper than we might get out of an Oracle-centric book.

Chapter 6: Using Device Storage and the Contacts API
Even though we're database developers, I think we're going to be able to provide some clever ideas with HTML5 storage. The Contacts API looks like a great lead in to communicating with the device.

Chapter 7: Accessing Device Sensors
The clear advantage your hybrid APEX applications will have over web deployments - access to hardware sensors.

Chapter 8: Using Location Data with PhoneGap
I think this is more functional than current HTML5 capabilities.

Chapter 9: Manipulating Files
As interesting as this looks - don't jump the gun, I think the chapter you're really interested in is next up.

Chapter 10: Capturing and Manipulating Device Media
Another marquee features for APEX applications - eg: easily uploading photos taken with the device.

Chapter 11: Working with PhoneGap Plugins
Just like APEX has some awesome plugins, so does Phonegap.
They'll probably feed your APEX application ideas even further.

Appendix A: Localizing Your App
We may get away with not needing this, but handy nonetheless. Besides, I've had limited exposure to localisation requirements.

Appendix B: Publishing Your App
No point having an app you can't publish, right? Luckily with our APEX hybrid, this may be a process that can be avoided after initial release.
All versioning can be done within your APEX environment.

Appendix C: Pop Quiz Answers
Pop quiz, hot shot - have you been comprehending what you're reading?

For your chance to win

1) Please post a comment below and mention which chapter you think would help you the most.

2) Double your chances by tweeting about this giveaway.

3) If you include a link to your tweet in the comments, I'll give you three entries.

I'll feed all entries into an Oracle table and run some SQL to randomly determine the three winners
- as oppopsed to using Random.org - come on, I'm an Oracle geek!
I'll post the method I will use to deduce the winners sometime soon, possibly over the new year.

Note: the closing date for this giveaway is 31st Dec 2013

Don't forget to include some form of contact information so I can contact you if you've won the prize.

Good luck!

3 product releases from Oracle Dec 2013

So far this December Oracle has released version upgrades for three of their products:

1. Joel Kallman announced Oracle APEX 4.2.4 is now available. It appears to be mainly fixes & enhancements to Package Applications, so maybe it's time to give them all a go & see if they may be useful to you. [bugs fixed]
   
   I'd also like to revisit a few and see if templates have been made out of some of their cooler dynamic PL/SQL regions. I've reverse engineered their hover functionality using a template & a list, but it would be nice to have these available in themes.
   
   Bring on APEX5
   
2. Jeff Smith gave use 10 reasons to upgrade to SQL Developer 4.0. I haven't used it much so far, but it seems many of the hiccups from the early adopter downloads have been addressed - plus a gazillion new features.
   
3. At the start of the month Jeff also announced Oracle SQL Developer Data Modeler 4.0 is available.

Do good things come in threes? No, they do not, but in this case we have a correlated co-incidence that we should certainly take advantage of ;-)

Count number of rows returned in APEX report

Often I want to configure an APEX page based on how many rows have been returned in a (typically classic) report region. There are a number of methods for determining this row count, and some have advantages over others.

1) Computation while rendering the page.

This often isn't best for performance & maintenance since you effectively need to do a count(*) over the SQL you already have. 

Analytics can deliver this value within the same SQL. Add the following column to your query

,count(*) over (order by null) cnt

And remove the column from sight of the user with the following CSS.

#emp thead th#CNT
,#emp tbody td[headers=CNT]
  {display:none;}

You can't declaratively set it hidden otherwise you cannot reference it with jQuery.

You can then use this jQuery to return the count.

$('#p1_notes tbody td[headers=CNT]:first').text()

It returns the text value of the CNT column from the first row.

2) Add substitution strings to Region Footer

If you're using pagination on a classic report you can include substitution strings such as #ROWS_FETCHED#.

You don't necessarily want to see this displayed on the page, so you could use

<div id="emp_count" style="display:none;">#TOTAL_ROWS#</div>

Then interrogate this value using

$('#emp_count').text()

Trouble is these don't get updated as you paginate through your results, and are usually only handy when operating with one page of results (or no results). Then the "No data found" and "More data found" message attributes can come into play.

3) jQuery function call

Length() returns the number of elements matching your selector. Note size() is deprecated as of jQuery 1.8

This method will work regardless of pagination usage. All you need is to identify something in your report you can count.

In this sample page there are a number of options. There is one button per record, so you could count the number of .uButtonGreen

A safer and faster method would be to nominate a column within my table. So in this case I defined a static region ID for my report region (emp)
, and nominated to count the number of employee name cells.

$s('P6_ROW_COUNT', $('table#report_emp td[headers="ENAME"]').length);

To help decide on my selector I used the browser's inspect element tool

Firefox inspect element

Press the "Show count" button on my sample page to see notification of row count. Change pagination to see varying results.

I'm really starting to love jQuery.

Build option use case - replace condition never

I first blogged about build options three years ago while still using APEX 3.x - I can tell by the green ;-)

I still use them regularly, but not often for versioning as they're allocated at a component level and not for attribute modifications - perhaps in later releases we'll see an extension of their use.

What I do see all the time is components with a condition type of "never". This is probably because it's the quickest way to turn off features that are broken or those you're not ready to completely remove for whatever reason.

The issue with this is they get forgotten. Future maintainers see these components in limbo and wonder if they should be removed or enabled, particularly if they lack comments regarding their status.

I have encountered valid circumstances when a report region is purposely set to "never" - where a link is available to export the relevant data as CSV.

Instead I would suggest creating a build option that looks like this:

Build option - to be removed

The 'Status' of 'Exclude' means I don't see the components at runtime - just like a condition of "never".
'Default on Export' being the same as current status means it will stay excluded as the application moves between environments.

One major advantage I find is the accompanying utilisation report. At some point during the project I went through everything I've allocated 'to be removed' and decided if it truly was time to sent these components to /dev/null.

Build option utilisation report

Do you have any interesting uses for build options?