Current Position:Home > Using SQL view object to create ADF table

Using SQL view object to create ADF table

Update:10-11Source: network consolidation
Advertisement
Hi,
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 (:

The Best Answer

Advertisement
Hi,
For the given situation you can create a Query Based View Object with the following query
SELECT
COUNT(TEMP1.DT) REQUEST,
TO_CHAR(TEMP1.DT, 'Mon') MONTH
FROM
TEMP1
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 :)
  • Using SQL view object to create ADF table Update:10-11

    Hi, 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 re

  • ADF - Multple View Objects in one ADF table - Jdeveloper 11g Update:10-11

    Hi, I'm using Jdeveloper 11g, is there a way to use multiple view objects in one ADF table? And if so, do you have an example? In my case the view objects are not based on a database table they were created based on an ESB wsdl, one is for person and

  • Bind a new view object to same adf table at run time Update:10-11

    Hello, I have an adf table, that changes based on the type of data the user selects. For example, lets say we have a page that consists of type drop down box at the top and an ADF table at the bottom, which lists data based on the type that is select

  • Error while creating adf table in jdev 11.1.1.2.0 Update:10-11

    Hi I am getting below error while running the jsf page. ======= <UIXInclude><_setupIncludeContext> Illegal call to setup the context of an include that is already in context <UIXInclude><_tearDownIncludeContext> Illegal call to tea

  • Create ADF Table at runtime Update:10-11

    Hi, I am trying to create ADF table at run time. Initially I tried to include all the tables at design time. Then I turned the setVisible property to false. Then , based on the condition, I turned it to true at runTime, so that to give the effect of

  • Not Able to create ADF Table In the Multiple model enviorment Update:10-11

    Hi All I am not able to create ADF Table or ADF Read-only Table I am having following thinks in my JWS Project 2, Model Project(Different different name) 2,faces-config.xml(Different different name) 1 web Project (View Controller) and one Databinding

  • How to Create adf table from java bean Update:11-30

    Hi, How to Create adf table from java class (Not from ADF BC). Thanks [email protected] -- you have to follow what shay said. Do the following in Model layer :: create a table property java class with your columns setters and getters like : *public class gr

  • How to Create ADF table from backing Bean Update:11-30

    Currently I am working on Jdeveloper11g. I have a requirement to create Table dyanamically .For that i will get info about table from config file. <?xml version="1.0" encoding="UTF-8"?> <Pages>           <page1>      

  • Unable to create ADF Table based on view object Update:11-30

    Hi I am using Jdeveloper 11.1.1.5 I have created a view object based on the entity object. I am trying to drag and drop view object from data controls in to a blank page and choosing table -> ADF Table. The table is not getting created and at the sam

  • How to change the default behaviour of View Object in oracle adf Update:10-11

    Hi, I have created a view object from an entity object and placed it as a table with multiple lines on my page. When I run my page, by default, it loads all the rows based on the sql in the VO. My requirement is when I load my page, I don't want to r