Limiting results and using a dynamic column name??

[Taoism]

Thanks to everyone who has replied!

Originally posted by chrisrlong
I'm not sure where you found that code, but it is wrong. What you want to do is search for 'Windowing' and you will find many posts (many from me) that will show you how to do it correctly.

- Chris

Heh, you are correct in the code not working... I got the code from this thread (which was code you had posted), but for whatever reason, I managed to lose the outer loop when I re-wrote it. I did figure that one out 'on my own' so to speak before I checked back here and found your reply

I have no idea what these do though:

:EndRow
:StartRow

Binded variables of some sort I assume? But I see no reference to them getting bound (sorry, still new to Oracle and PL/SQL).


And again, I am unfamiliar with this syntax:

USING
  p_EndRow,
  p_StartRow;

I don't know what it is causing to happen in the proc.

I ended up writing it like so:

       OPEN p_fldr_cur FOR
    'SELECT rn,
            fldr_id,
            fldr_name
       FROM(   -- outer
         SELECT inner.*,
                ROWNUM AS rn
           FROM(   -- inner
             SELECT fldr_id,
                    fldr_name
               FROM inf_cat_fldrs
              ORDER BY '||p_order||'
           ) inner
      WHERE ROWNUM <= '||p_limit||'
       ) outer
    WHERE outer.rn >= '||p_offset||' ';

Am I losing something by not using the code blocks I ask about above?

Also, does doing this 'dynamic column' sorting, cause the proc to not be fully compiled before execution? I.E. would I be better off writing a proc per column (horror)...?

Cheers,
Keith.