1 Reply Latest reply: Jun 22, 2012 5:46 AM by anitat RSS

    Using SQL view object to create ADF table


      I have created a column called "Month" (which extracts month from the date column) and another column to count the no. of requests.

      i want to create an ADF table with 2 columns, a column showing the month and another is showing the no. of requests for that month.

      However, now I only managed to achieve the ADF table to show the overall total requests, which means if i add up all the requests for all the months and i get 500

      My ADF table shows this:

      Jan: 500
      Feb: 500
      Mar: 500

      How should I create the view or what should I do to make it such that the no. of request is based on the month?

      Please advice.

      Thanks (:
        • 1. Re: Using SQL view object to create ADF table

          For the given situation you can create a Query Based View Object with the following query
          TO_CHAR(TEMP1.DT, 'Mon') MONTH
          GROUP BY
          TO_CHAR(TEMP1.DT, 'Mon')

          where DT is the date column and temp1 is the name of the database table.

          Following are the steps that i followed to get this query :

          i have taken the following sample table :

          create table temp1
          (srno number primary key,
          dt date)

          *Note you may use any existing column instead of srno or use dt only (i took an extra column as u know we need a primary key /row id)

          the following is the sample data

          insert into temp1 values (1,sysdate);
          insert into temp1 values (2,sysdate);
          insert into temp1 values (3,add_months(sysdate,1));
          insert into temp1 values (4,add_months(sysdate,1));
          insert into temp1 values (5,add_months(sysdate,3));
          insert into temp1 values (6,add_months(sysdate,5));

          the table appears as follows
          SRNO DT
          ------- ---------
          1 22-JUN-12
          2 22-JUN-12
          3 22-JUL-12
          4 22-JUL-12
          5 22-SEP-12
          6 22-NOV-12

          To start with ADF View Object Creation (Using Jdeveloper 11.1.2):
          Create the view object using Create View Object wizard

          In Step 1. Name window
          set the value for Name : Viewab (you can use any of ur choice)
          In the data source section : select query

          In Step 2. Query window
          a. Click Query builder (it will pop up sql statment window)
          b. In the SQL Statement window
          in quick-pick objects section -> select temp1 table -> shuttle the columns from available list to selected list
          in select clause section -> select srno column from select list-> choose count() function from function drop down list -> insert function -> set alias to REQUEST-> click validate
          now select dt column from select list -> choose to_char() function -> click insert function -> alter the function to to_Char(temp1.DT,'Mon') -> set alias to Month -> click validate
          in the group by clause section -> Click the green symbol to add -> from the expression palette insert dt column -> insert the to_char function -> alter the function to to_char(temp1.DT,'Mon') -> click validate
          in the Entire SQL Query section -> click test query -> in the test query window -> click query result-> you will see the result -> click close -> click ok
          Click next

          Step 3: Bind Variables
          Click Next

          Step 4: Attribute Mappings
          click Finish

          So the view object is ready :)