Query to convert Column data into a Row

The following query is very useful for converting the data in a single column into a row so the all the values can be extracted from a table into variables of different name. Using cursor it is difficult to get all the values from single column into variables of different name. 

Example of sample data available - 

Required format into which data needs to be converted -

Query which can be used for above purpose - 


SELECT  MAX(DECODE(level,1,regexp_substr(str,'[^,]+',1,level))) AS val1 ,
 MAX(DECODE(level,2,regexp_substr(str,'[^,]+',1,level))) AS val2 ,
 MAX(DECODE(level,3,regexp_substr(str,'[^,]+',1,level))) AS val3 ,
 MAX(DECODE(Level,4,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val4 ,
 MAX(DECODE(level,5,regexp_substr(str,'[^,]+',1,level))) AS val5 ,
 MAX(DECODE(level,6,regexp_substr(str,'[^,]+',1,level))) AS val6 ,
 MAX(DECODE(level,7,regexp_substr(str,'[^,]+',1,level))) AS val7 ,
 MAX(DECODE(level,8,regexp_substr(str,'[^,]+',1,level))) AS val8 ,
 MAX(DECODE(Level,9,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val9 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val10 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val11 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val12 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val13 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val14 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val15
   INTO l_value_id1 ,
 l_value_id2 ,
 l_value_id3 ,
 l_value_id4 ,
 l_value_id5 ,
 l_value_id6 ,
 l_value_id7 ,
 l_value_id8 ,
 l_value_id9 ,
 l_value_id10 ,
 l_value_id11 ,
 l_value_id12 ,
 l_value_id13 ,
 l_Value_Id14 ,
 l_value_id15
   FROM (
         SELECT Listagg (column_to_convert,',') Within Group (Order By column_to_convert) Str
    FROM Table_Name
   WHERE 1 = 1
   GROUP BY group_by_column
   ) Tmp

   CONNECT BY regexp_substr(str,'[^,]+',1,level) IS NOT NULL;

ADF Interview Questions - Set 8

Question: What is Query Collection in VO?

Answer: Query Collection is mainly used for caching the result of the executed query VO. A VO can have multiple query collections depending on the rowsets.

Example: If we have 2 rowsets where DEPTID =10 and DEPTID=20. So the results of the executed VO with these bind variables are stored in Query Collection.

Now we understood what are the other layers that are to be used before hitting the data source? Now let us see how it will be executed with an example: 

Example:  Let’s consider a java code snippet where we execute a VO and retrieve the values 

          ViewObject vo = ApplicationModule.findViewObject(“EmployeesVO”);

           vo.executeQuery(); 

           Row row = vo.first();                                          // gets the first row

     Steps involved in executing this VO are as follows: 

1.     ADF client starts interaction with the Application Module. 

2.     ADF Client looks for the requested View Object (EmployeesVO) instance in AM.

3.     Now before executing the VO, it does as below: 

a.     Checks for relevant Query Collection for Search Parameters (Bind Variables), if available uses the existing query collection.

b.    If not present created a new Query Collection based on the Search Parameters.

4.     Once the QC is initialized, the new Rowset instance calls the prepareRowSetQuery() on VO. This is used for placing your customcode before Querycollection. (Eg: the Query for the rowset with the bind variable).

5.     This is followed by executeQueryForCollection(), which delegates the call for query collection instance and JDBC call for particular Database. 

6.     Now when a client tries to get the first row in result set by calling first() , as the result set retrieves the first row it displays the first row.

Question: How and When Groovy Expression is used?

Answer: Groovy is interpreted at runtime; it can be treated as metadata and customized. So, you could have an application that has customized business rules in Groovy and those customizations stored in Oracle ADF’s Metadata Services (MDS).

Oracle ADF provides a number of different declarative points into which you can drop Groovy code:

1.     Values for view and entity objects attributes

2.     Validation rules on entity object attributes

3.     Expressions for error messages

4.     Values for view object bind variables

5.     View object transient attribute validation.

Examples:

1: For the transient attribute AnnualSalary you could define a Groovy expression for the default to be: Sal * 12

2: On the Sal attribute you might want to define that a salesman must have a salary of less than 1000: if (Job == "SALESMAN") {return newValue < 1000} else return true.

ADF Business Components view objects also provide the ability to use Groovy expressions. As with entity objects, view objects can support:

1.     Referencing attributes in the same view object

2.     Referencing attributes in other view objects

3.     Transient attribute validation

4.     Referencing methods in the Java class that backs the view object

5.     Referencing built in calls such as sum and min.

Question: What are the steps to configure MDS? What are different kinds of MDS you configured? Which file hold details of configured MDS?

Answer: To apply MDS to your application follow this:

1. Pages should be (.jspx) not (.jsp).

2. In your project properties --> ADF View check on "Enable User Customizations" then select "Across Sessions Using MDS".

3.Create a new class as below. This class has two main functions:

- getName() which return the folder name that hold all users changes.

- getValue() which return the folder name for the login user. This folder should be unique for every login user and will saved inside the getName() folder.

4. In Application Resources --> Descriptors --> ADF META-INF open adf-config.xml.

5. In adf-config file go to MDS Configurations and add your class

6. In adf-config file go to View tab and add all Tags.

7. Now you apply MDS to your application.

8. If you run your application in Integrated Weblogic there will be no problem, you should only specify the path to store the user changes (changes will stored in file system). You will find this path in your Application properties --> Run --> MDS.

Project Accounting - Check funds queries at Task, Top Task and Project Level

FOR TASK

  SELECT project_id,
    (SUM(budget) - SUM(actuals)) "funds"
  FROM PA_TASK_FC_RESULTS_V
  WHERE project_id     =<p_project_id>
  AND start_date       > '31-MAR-15'
  AND end_date         < '1-APR-16'
  AND budget_version_id=
    (SELECT MAX(budget_version_id)
    FROM PA_TASK_FC_RESULTS_V
    WHERE project_id=<p_project_id>
    )
  and TASK_ID=<P_TASK_ID>
  GROUP BY project_id;
  
FOR TOP TASK 

  SELECT project_id,
    (SUM(budget) - SUM(actuals)) "funds"
  FROM PA_TOP_TASK_FC_RESULTS_V
  WHERE project_id     =<p_project_id>
  AND start_date       > '31-MAR-15'
  AND end_date         < '1-APR-16'
  AND budget_version_id=
    (SELECT MAX(budget_version_id)
    FROM PA_TOP_TASK_FC_RESULTS_V
    WHERE project_id=<p_project_id>
    )
  and TOP_TASK_ID=<P_TOP_TASK_ID>
  GROUP BY project_id;
  
FOR PROJECT 

  SELECT project_id,
    (SUM(budget) - SUM(actuals)) "funds"
  FROM PA_PRJ_FC_RESULTS_V
  WHERE project_id     =<p_project_id>
  AND start_date       > '31-MAR-15'
  AND end_date         < '1-APR-16'
  AND budget_version_id=
    (SELECT MAX(budget_version_id)
    FROM PA_PRJ_FC_RESULTS_V
    WHERE project_id=<p_project_id>
    )
  GROUP BY PROJECT_ID;