Anchors Aweigh!

Sometimes Oracle Reports can seem like a black box at times.Take for instance the rendering algorithm. I've read description of how items are placed on the page and there's the idea of implicit and explicit anchoring. Explicit anchoring is when the user actually creates an anchor, thus overiding any implicit anchor.
If only you could display the implicit anchors...if only.....

Well, it turns out this has been available all this time. To access Anchoring Information, go to Tools / Options / Navigator / Layout and then click on Anchoring Information. The Object Navigator will now display a new node called 'Anchoring Information' for each object. Both parent and child anchors are listed.

I can't claim this is poorly documented, but I can honestly say I never knew this was available. For my own piece of mind, I polled several other experienced developers and not a single one knew we could display the implicit anchors. So don't beat yourself up over it.

Our journey continues...

Why KScope11?

That is the question. After all we do have other outstanding options out there. But we do keep coming back to KScope and in greater and greater numbers. Today I talk with Iloon Ellen-Wolff, an Oracle Engineer working with the APEX team and currently living in the Netherlands. She took time out of her busy day to answer a few questions of mine. Thank you, Iloon.

ML: Hello again! It was great meeting you in person in Washington, DC last June. Believe it or not we're already for KScope11. In your opinion, what’s the best thing about Kscope conferences?


IEW: The high quality of the presentations. Not a sales event, but an event for the people working with the product.

ML: Who do you most look forward to seeing a presentation from at Kscope and why?

IEW: To meet the presenter face to face and very important for my own network


ML: Why Kscope and not somewhere else?

IEW: The high quality of the event.  The best presenters of the world are available to share knowledge.


ML: Have you ever been to Long Beach or visited the Queen Mary?
IEW: I never visited Long Beach before. Therefore I am looking forward to meet everybody again in this inspiring environment.


ML: Your all time favorite Kscope moment is…
IEW: To meet and great everybody again!

Thanks again, Iloon! I'd encourage you to follow Iloon on Twitter - iloonellen and also to visit her blog at http://iloonellen.blogspot.com/


Our journey continues...

Iron Sharpens Iron...Take the PL/SQL Challenge

Are you taking the PL/SQL Challenge? Well, you should be. No excuses.

Although I first registered back last April, I didn't play on a regular basis until just recently. It's fun and takes only a minute or two to complete. You get email reminders and email notifications explaining how you rank among all others taking the quiz that day.

I listed my abilities as Advanced, but I've yet to see a quiz question I thought was easy. Because each question can have multiple answers, it makes it difficult to get a perfect score.

C'mon, get on board today.  http://plsql-challenge.blogspot.com/

Our journey continues...

Fee Fi Fo SQL

A solution that has eluded me for months now is one where First In - First Out (FIFO) inventory costing can be represented in a single SQL statement. I knew it could be done with a pipelined function, but for my own personal satisfaction I had to see it done with SQL Analytics.

It wasn't until I looked at the problem from the bottom up did the solution really present itself. For example, if you know how much inventory you have on hand, you simply need to spread it over your most recent incoming shipments.

Here's an emp example using salary and hiredate. Vary the on hand amount when prompted and you'll see it spreads out the on-hand amount on a first in - first out basis. Success!


SELECT
   empno,hiredate,sal,
   LEAST(sal, remain_bal) remaining_qty
FROM
 ( SELECT
      empno,hiredate,sal, on_hand,
      NVL(LAG(run_ttl) OVER (ORDER by hiredate DESC,
                                      empno DESC),on_hand) as remain_bal
   FROM
    ( SELECT empno,hiredate,sal, on_hand,
             on_hand - SUM(sal) OVER (ORDER BY hiredate DESC,
                                               empno DESC) AS run_ttl
      FROM
       ( SELECT empno,hiredate,sal, 
                :on_hand AS on_hand
           FROM scott.emp
          ORDER BY 2 DESC,1 DESC )
  ORDER BY 2 DESC,1 DESC ))
WHERE LEAST(sal, remain_bal) > 0


Here's some sample output...

On_Hand: 1800
empno hiredate  sal     remaining_qty
7876  5/23/1987 1100.01    1100.01
7788  4/19/1987 3000        699.99

On_Hand: 5500
empno hiredate    sal   remaining_qty
7876  5/23/1987 1100.01    1100.01
7788  4/19/1987 3000       3000.00
7934  1/23/1982 1300       1300.00
7902  12/3/1981 3000         99.99

There you have it. Of course if your needs are Last In - First Out (LIFO) you'll simply need to reverse the order of the incoming shipments.

Our journey continues...