1 2 Previous Next 28 Replies Latest reply on May 8, 2008 5:35 PM by 562321

    PL/SQL code for handling more than 3 levels of breaks.

    414579
      Hi,

      Would anyone have any PL/SQL code for handling and displaying a table that would be able to handle more than 3 levels of breaks (and be willing to share??) :) I've used the built in 3 levels of breaks, but the report I'm working on requires more, and I've had no luck doing a larger report in pl/sql and displaying my results in a table.

      Thanks in Advance,

      -- Cliff Moon
      -- UTPA Webmaster.
        • 1. Re: PL/SQL code for handling more than 3 levels of breaks.
          Tyler-Oracle
          PL/SQL bad!!!! SQL good!!! The solution is a combination of "CASE" and the analytic function "LAG" (both documented in the 9i and 10g SQL Reference):
          select case when lag(dname,1,'foo') over(order by dname,job,ename) != dname
                      then dname
                      else null end dname_w_break,
                 case when lag(job,1,'foo') over(order by dname,job,ename) != job
                      then job
                      else null end job_w_break,
                 ename
            from emp e, dept d
          where e.deptno = d.deptno
          order by dname,job,ename
          I know this is only 2 columns, but it will work for n number of columns.

          The syntax for lag is lag(column,# rows back,default if null). I chose 'foo' since I knew none of them would = foo and this would make sure the first row had a value.

          2 Caveats. The order by in each of your lag() expressions MUST match the order by for the whole query. Since you HAVE to use order by for this to work, you cannot use column heading sorting.

          Good luck,
          Tyler
          • 2. Re: PL/SQL code for handling more than 3 levels of breaks.
            57403
            Tyler, this is excellent, thanks!
            • 3. Re: PL/SQL code for handling more than 3 levels of breaks.
              414579
              Tyler,

              Thanks for the code. Very very cool.. Worked excellent

              -- Thanks again

              -- Cliff Moon
              -- UTPA Webmaster
              • 4. Re: PL/SQL code for handling more than 3 levels of breaks.
                414579
                Greetings,

                The case/lag function works excellent. Have another question concerning it:

                Would anyone know the syntax for coding in either one of these columns into the syntax of that lag?

                (select max(dateofcommunication) from communications where wpid = p.wpid) lastdate,

                OR...................

                '<img src="/i/red.gif" height="10" width="' || (select avg(percentagecomplete) as avgcomplete from workphases where projectid = p.projectid group by projectid *.1) || '"> ' || (select avg(percentagecomplete) as avgcomplete from workphases where projectid = p.projectid group by projectid *.1) || '%' bar

                I've tried a dozen different ways of trying to code that into the case/lag, but to no avail. The second function is a graphical bar to match the numerical average being returned.

                Is it possible to code these into the case and lag statement even?

                Thanks in Advance,

                -- Cliff Moon
                • 5. Re: PL/SQL code for handling more than 3 levels of breaks.
                  Tyler-Oracle
                  Can you try an example with emp / dept as I can work with this? Even if it doesn't work, add your code or even pseudo-code to my example.

                  Understand that coding a select as a column in a query will run that inline select statement FOR EVERY ROW in your query. So, if your query returns 100 rows, you will have actually run 101 queries. Sound slow?

                  Tyler
                  • 6. Re: PL/SQL code for handling more than 3 levels of breaks.
                    414579
                    Hey Tyler,

                    I'll try and reproduce the error I'm getting with the select as a column. Reason for it, I have a table with Projects, and then another table with logs dealing with each project. Project manager wants to know the last known log date dealing with a certain Project, thus the attempt to get the last date into the query with that select statement. Is there a better way of doing this?

                    Thanks for the prompt response, and I'll work on coming up with a comparable select to the one i'm doing using the emp/dept tables

                    -- Cliff
                    • 7. Re: PL/SQL code for handling more than 3 levels of breaks.
                      414579
                      Hey Tyler,

                      In retrospect, I'm going to add another process to fire when a log is submitted and have it update a column within my project table with the last known date. That'll get rid of the problem. I wasnt thinking about the overhead on the server.

                      Thanks for the reply and that really is an awesome little select statment you threw out there.

                      -- Cliff
                      • 8. Re: PL/SQL code for handling more than 3 levels of breaks.
                        Tyler-Oracle
                        Joining to the log table would likely be much quicker than an inline select. You could do is as an inline view as well:
                        select p.project_id, l.last_update
                          from projects p,
                               (select project_id, max(last_update) last_update
                                  from project_log
                                 group by project_id) l
                        where l.project_id = p.project_id
                        Try to think of doing things in bulk. In this case, do the work of getting the max(last_date) all at once, then join it to the projects table. Make sure you index the id and the last_update columns (composite index in that order comes to mind) of the log to make the group by fast. Might want to make it an IOT as well.

                        Whenever you find yourself doing things row by row (PL/SQL function on a column, inline select, PL/SQL loops for inserts or updates), you are VERY likely creating a performance problem. Oracle is very good at bulk operations, you just have to think like the database.

                        Thanks,
                        Tyler
                        • 9. Re: PL/SQL code for handling more than 3 levels of breaks.
                          414579
                          Tyler,

                          A quick question.. In order to use the case/lag, does each of the fields that are referenced inside the select using the case/lag have to be included in the "over (order by this,that,this) part of the statement". I'm using it to sort on about 10 fields, and it's dropping data when it is really there.. ie (none appears). I've used the same format on each of the statements as the one that you provided, but (and I have seen several examples of this happening) sometimes there is no data where one would expect it.

                          Just a quick question (and once again, thanx for the help)

                          -- Cliff
                          • 10. Re: PL/SQL code for handling more than 3 levels of breaks.
                            Tyler-Oracle
                            Cliff,

                            I thought of an easier way to do the order by part. Essentially, do the order by once in an inline view, then select from that view and use rownum as the order by in all of your lag() columns. You have to do this in an inline view as rownum is computed before the order by is evaluated (try adding rownum to your current query if you don't believe me). Enough jibberish already, show me the code! :
                            select case when lag(dname,1,'foo') over(order by rownum) != dname
                                        then dname
                                        else null end dname_w_break,
                                   case when lag(job,1,'foo') over(order by rownum) != job
                                        then job
                                        else null end job_w_break,
                                   ename
                              from(
                                -- Inline view with order by allows you to use
                                -- rownum in the order by of lag() in outter select.
                                select dname,job,ename
                                  from emp e, dept d
                                 where e.deptno = d.deptno
                                 order by dname,job,ename)
                            Hopefully this will simplify your query a bit so you can debug your missing data issue.

                            Thanks,
                            Tyler
                            • 11. Re: PL/SQL code for handling more than 3 levels of breaks.
                              414579
                              Greetings,

                              Well, I've ascertained why it's happening, but not how to fix it..The reason for losing the data was (and I hope I can explain this sensibly) is this: (and I i'm not sure how to fix it):

                              Example Output:
                              000000......TextField1Row1......01-FEB-99....Blah
                              ................................01-FEB-00....Blah2
                              .............................................Blah3
                              111111......TextField1Row2......<No Data>....Blah4

                              Because record 111111 has the same first date as the previous record, it's not showing. What I guess I was hoping for was that it even though it was the same date as before, that because it was associated with a different record, that it would rewrite it.. I had expected it to skip the date on the "blah3" line because it was the same date, but still associated with the same record "00000".

                              Hoped Example Output:
                              000000......TextField1Row1......01-FEB-99....Blah
                              ................................01-FEB-00....Blah2
                              .............................................Blah3
                              111111......TextField1Row2......01-FEB-00....Blah4

                              ***NOTE***
                              Please assume these periods to be spaces and copy to a text editor to see the actual representation of that report Couldn't figure out how to make it look right in this window.

                              I guess I'm looking for too much??

                              Hope this was understandable, and once again, thanx for the much needed help.

                              -- cliff
                              • 12. Re: PL/SQL code for handling more than 3 levels of breaks.
                                Tyler-Oracle
                                Cliff,

                                Any chance you can duplicate this on htmldb.oracle.com and post your workspace name, app number here?

                                Thanks,
                                Tyler
                                • 13. Re: PL/SQL code for handling more than 3 levels of breaks.
                                  414579
                                  Tyler,

                                  It might be easier if I got your email address, and I'll send you login instructions to our example here. Would that work?

                                  -- cliff
                                  • 14. Re: PL/SQL code for handling more than 3 levels of breaks.
                                    Tyler-Oracle
                                    Can you email the information to htmldb_us@oracle.com ? Only 3 people on my team really check this account.

                                    Would be best if you created an account for me and sent me:
                                    - workspace name
                                    - username
                                    - password
                                    - app number
                                    - page number

                                    You can disable the account when we are done.

                                    Thanks,
                                    Tyler
                                    1 2 Previous Next