SQL & PL/SQL

I'm on 10gR2

Trying my best here to do everything in a single SQL rather than write PL/SQL. I know it's possible, i'm just not that good at SQL past oracle 8i! I've searched the forums and have found lots of examples for date ranges using connect by and level, but nothing specifically relating to value ranges.

Basically, i've got a table with employee numbers and approval limits:

  with  a as (select 12345 emp_no,1000 val from dual 
                 UNION select 12346,5000 from dual UNION select 12347,9999 from dual)
  select * from a 

returns

EMP_NO  VAL
------- ---------------
12345   1000
12346   5000
12347   9999

I'd like an SQL statement which returns all rows less than the &parameter but also the row of the "child" who as a value "bigger" than the greatest value of the value less than the parameter.

It's probably easier to show examples.

If &parameter was 2342 then show

EMP_NO  VAL
------- ---------------
12345   1000
12346   5000

If &parameter was 7898 then show

EMP_NO  VAL
------- ---------------
12345   1000
12346   5000
12347   9999

If &parameter was 10 then show

EMP_NO  VAL
------- ---------------
12345   1000

If &parameter was 788777 then show

No rows returned 

Essentially I'm trying to return the approval chain that an invoice will have to go through before it can be approved up to the &parameter amount.

Tried with connect by, even lead function. Requesting assistance from the gurus to get it right. Hope this might also assist others.

Edited by: user13007502 on 08-Feb-2012 04:56

Edited by: user13007502 on 08-Feb-2012 04:58