0

I have done the following code for an assignment:

DECLARE
   Ledger_rec   DDI.LEDGER_VIEW%ROWTYPE;

   PROCEDURE Var2Count(v_col IN NUMBER)
      IS
      BEGIN
         DBMS_OUTPUT.PUT(TO_CHAR(NVL(v_col,0), '999,999'));
      END;
BEGIN
   DBMS_OUTPUT.PUT_LINE('                  Total Head Count by Registration Date');
   DBMS_OUTPUT.NEW_LINE;
   DBMS_OUTPUT.PUT_LINE('REGDATE               101     102     103     104     105     106     107     108');
   DBMS_OUTPUT.PUT_LINE('--------             -----   -----   -----   -----   -----   -----   -----   -----');
   FOR Ledger_rec IN
      (
       SELECT * FROM
         (
          SELECT REGDATE, ADULTCNT, CHILDCNT, ROOMNUM
          FROM DDI.LEDGER_VIEW
         )
         PIVOT
         (
          SUM(CHILDCNT + ADULTCNT)
          FOR ROOMNUM IN ('101' AS r101,
                             '102' AS r102,
                             '103' AS r103,
                             '104' AS r104,
                             '105' AS r105,
                             '106' AS r106,
                             '107' AS r107,
                             '108' AS r108)
         )
       ORDER BY REGDATE
       )
    LOOP
       DBMS_OUTPUT.PUT(RPAD(Ledger_rec.REGDATE, 16));
       Var2Count(Ledger_rec.r101);
       Var2Count(Ledger_rec.r102);
       Var2Count(Ledger_rec.r103);
       Var2Count(Ledger_rec.r104);
       Var2Count(Ledger_rec.r105);
       Var2Count(Ledger_rec.r106);
       Var2Count(Ledger_rec.r107);
       Var2Count(Ledger_rec.r108);
       DBMS_OUTPUT.NEW_LINE;
    END LOOP;

EXCEPTION
   WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('No data found.');
END;

This is working except there is one thing I am missing from the assignment description:

Use the dynamic variable, (CHILDCNT + ADULTCNT) HEADCNT, for the aggregate SUM(HEADCNT)

So, I think my code should read SUM(HEADCNT). How do I assign and use this dynamic variable?

1 Answer 1

1

You can define HEADCNT inside first block of parentheses and reuse it.

   SELECT * FROM
     (
      SELECT REGDATE, ADULTCNT, CHILDCNT, CHILDCNT + ADULTCNT HEADCNT, ROOMNUM
      FROM DDI.LEDGER_VIEW
     )
     PIVOT
     (
      SUM(HEADCNT)
      FOR ROOMNUM IN ('101' AS r101,
                         '102' AS r102,
                         '103' AS r103,
                         '104' AS r104,
                         '105' AS r105,
                         '106' AS r106,
                         '107' AS r107,
                         '108' AS r108)
     )
   ORDER BY REGDATE
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.