Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Tuesday, 21 November 2017

Oracle SQL UNION Set Operator

0 comments
   
  The UNION operator returns all rows selected by either query. Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows.

SQL UNION Operator

Guidelines
         The number of columns and the datatypes of the columns being selected must be identical in all the SELECT statements used in the query. The names of the columns need not be identical.
         UNION operates over all of the columns being selected.
         NULL values are not ignored during duplicate checking.
         The IN operator has a higher precedence than the UNION operator.
         By default, the output is sorted in ascending order of the first column of the SELECT clause.

Using the UNION SET Operator 

The UNION operator eliminates any duplicate records. If there are records that occur both in the  Student1 and the Studnet2 tables and are identical, the records will be displayed only once.

Take examples from Student1 and Student2 tables 
 
Select * from Student1;



SrNo
Name
101
Name101
102
Name102
 



Select * from Student2;
 


SrNo
Name
102
Name102
103
Name103
 



Consider the following example:

SQL>SELECT * FROM student1
          UNION
          SELECT * FROM student2;


Output:


SrNo
Name
101
Name101
102
Name102
103
Name103
 




  



SET Operator Guidelines

         The expressions in the SELECT lists must match in number and data type.
         Parentheses can be used to alter the sequence of execution.
         The ORDER BY clause:
        Can appear only at the very end of the statement
        Will accept the column name, aliases from the first SELECT statement, or the positional notation
Continue reading >>

Sunday, 19 November 2017

Oracle SQL WITH Clause

0 comments


The WITH clause


Using the WITH clause, you can define a query block before using it in a query. The WITH clause (formally known as sub-query factoring clause) enables you to reuse the same query block in a SELECT statement when it occurs more than once within a complex query. This is particularly useful when a query has many references to the same query block and there are joins and aggregations.

Using the WITH clause, you can reuse the same query when it is high cost to evaluate the query block and it occurs more than once within a complex query. Using the WITH clause, the Oracle Server retrieves the results of a query block and stores it in the user’s temporary tablespace. This can improve performance.

WITH Clause Benefits


         Makes the query easy to read
         Evaluates a clause only once, even if it appears multiple times in the query, thereby
enhancing performance
         Using the WITH clause, you can use the same query block in a SELECT statement when it occurs more than once within a complex query.
         The WITH clause retrieves the results of a query block and stores it in the user's temporary tablespace.
         The WITH clause improves performance

WITH Clause: Example

Syntax of the SQL WITH clause when using a single subquery alias

WITH <alias_name> AS (sql_subquery_statement)
SELECT column_list FROM <alias_name>[,tablename]
[WHERE <join_condition>]

Example:

WITH dept_temp AS (SELECT *
                     FROM dept
                    WHERE deptno <> 10)
SELECT *
  FROM dept_temp dt, emp e
 WHERE dt.deptno = e.deptno AND dt.deptno = 20
UNION ALL
SELECT *
  FROM dept_temp dt, emp e
 WHERE dt.deptno = e.deptno AND dt.deptno = 30

Syntax of the SQL WITH clause when using a multiple subquery aliases

WITH <alias_name_A> AS (sql_subquery_statement),
<alias_name_B> AS(sql_subquery_statement_from_alias_name_A
or sql_subquery_statement )
SELECT <column_list>
FROM <alias_name_A>, <alias_name_B>, [tablenames]
[WHERE <join_condition>] 

     Using the WITH clause, write a query to display the department name and total salaries for those departments whose total salary is greater than the average salary across departments.
1. Calculate the total salary for every department, and store the result using a WITH clause.
2. Calculate the average salary across departments, and store the result using a WITH clause.
3. Compare the total salary calculated in the first step with the average salary calculated in the second step. If the total salary for a particular department is greater than the average salary across departments, display the department name and the total salary for that department.

WITH dept_costs AS (  SELECT d.department_name, SUM (e.salary) AS dept_total
                        FROM employees e, departments d
                       WHERE e.department_id = d.department_id
                    GROUP BY d.department_name),
     avg_cost
        AS (SELECT SUM (dept_total) / COUNT (*) AS dept_avg FROM dept_costs)
  SELECT *
    FROM dept_costs
   WHERE dept_total > (SELECT dept_avg FROM avg_cost)
ORDER BY department_name;

The SQL code is an example of a situation in which you can improve performance and write SQL more simply by using the WITH clause. The query creates the query names DEPT_COSTS and AVG_COST and then uses them in the body of the main query. Internally, the WITH clause is resolved either as an in-line view or a temporary table. The optimizer chooses the appropriate resolution depending on the cost or benefit of temporarily storing the results of the WITH clause.

Note: A subquery in the FROM clause of a SELECT statement is also called an in-line view.

The WITH Clause Usage Notes

         It is used only with SELECT statements.
         A query name is visible to all WITH element query blocks (including their subquery blocks) defined after it and the main query block itself (including its subquery blocks).
         When the query name is the same as an existing table name, the parser searches from the inside out, the query block name takes precedence over the table name.
         The WITH clause can hold more than one query. Each query is then separated by a comma.


Continue reading >>

Saturday, 11 November 2017

Display Parameter Values in RTF Template

0 comments



We can build XML report by using RDF (By using report builder) and Data templates

Here we are going learn in both ways how show Parameter values 


Showing parameter values in RTF template by RDF report 


Take formula column at report level write the following coding in PLSQL block

Begin
Return (:Parameter);
End;

Refer the XML tag into the RTF 


Showing parameter values in RTF template by data template 

Here we can show in 2 steps:


Step1:
 
Create new SQL in Data template

<sqlStatement name="Q_PARAM_DET">
<![CDATA[
  SELECT  TO_CHAR (TO_DATE (:P_FROM_PO_DATE, 'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YYYY') from_date
        , TO_CHAR (TO_DATE (:P_TO_PO_DATE, 'YYYY/MM/DD HH24:MI:SS'), 'DD-MON-YYYY')  to_date
        , fnd_global.conc_request_id request_id
        ,(SELECT user_name
            FROM fnd_user
           WHERE user_id = fnd_global.user_id) run_by
        , to_char(sysdate,'DD-MON-YYYY HH:MI:SS AM') sys_date
   FROM DUAL
]]>
</sqlStatement>

Step2:

Create data source shown below


<group name="G_PARAM_DET" source="Q_PARAM_DET">    
     <element name="from_date"   datatype="VARCHAR2"     value="from_date"/>
     <element name="to_date"     datatype="VARCHAR2"     value="to_date"/>
     <element name="request_id"  datatype="NUMBER"       value="request_id"/>
     <element name="run_by"      datatype="VARCHAR2"     value="run_by"/>
     <element name="sys_date"    datatype="VARCHAR2"     value="sys_date"/>       
   </group>



Continue reading >>