von Raluca Damian Vor 11 Jahren
482
Oracle_SELECT
The text outlines the syntax and structure for using pivot and unpivot clauses in SQL queries. Pivots are used to convert rows into columns, allowing for a more readable format of aggregated data.
von Raluca Damian Vor 11 Jahren
482
Mehr dazu
SELECT e.employee_id, e.salary, e.commission_pct
FROM employees e, departments d
WHERE job_id = 'SA_REP'
AND e.department_id = d.department_id
AND location_id = 2500
ORDER BY e.employee_id
FOR UPDATE;
SELECT e.employee_id, e.salary, e.commission_pct
FROM employees e JOIN departments d
USING (department_id)
WHERE job_id = 'SA_REP'
AND location_id = 2500
ORDER BY e.employee_id
FOR UPDATE OF e.salary;
SELECT employee_id FROM (SELECT * FROM employees)
FOR UPDATE OF employee_id;
SELECT employee_id FROM (SELECT employee_id+1 AS employee_id FROM employees)
FOR UPDATE;
Since the FOR UPDATE clause is not supported on views, anything that prevents view merging, such as the NO_MERGE hint, parameters that disallow view merging, or something in the query structure that prevents view merging, will result in an ORA-02014 error.
In the following example, the GROUP BY statement prevents view merging, which causes an error:
SELECT avgsal
FROM (SELECT AVG(salary) AS avgsal FROM employees GROUP BY job_id)
FOR UPDATE;
FROM (SELECT AVG(salary) AS avgsal FROM employees GROUP BY job_id)
*
ERROR at line 2:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
QUERY_NAME AS (SUBQUERY) SEARCH_CLAUSE CYCLE_CLAUSE
FUNCTION...;
PROCEDURE...;
row_pattern_factor
row_pattern_primary
row_pattern_permute
row_pattern_quantifier
outer_join_type
query_partition_clause
table_reference PIVOT [ XML ]
( aggregate_function ( expr ) [[AS] alias ]
[, aggregate_function ( expr ) [[AS] alias ] ]...
pivot_for_clause
pivot_in_clause
)
FOR { column
| ( column [, column]... )
}
IN ( { { { expr
| ( expr [, expr]... )
} [ [ AS] alias]
}...
| subquery
| ANY [, ANY]...
}
)
table_reference UNPIVOT [ {INCLUDE | EXCLUDE} NULLS ]
( { column | ( column [, column]... ) }
pivot_for_clause
unpivot_in_clause
)
IN
( { column | ( column [, column]... ) }
[ AS { literal | ( literal [, literal]... ) } ]
[, { column | ( column [, column]... ) }
[ AS {literal | ( literal [, literal]... ) } ]
]...
)
table_reference UNPIVOT [ {INCLUDE | EXCLUDE} NULLS ]
( { column | ( column [, column]... ) }
pivot_for_clause
unpivot_in_clause
)
IN
( { column | ( column [, column]... ) }
[ AS { literal | ( literal [, literal]... ) } ]
[, { column | ( column [, column]... ) }
[ AS {literal | ( literal [, literal]... ) } ]
]...
)
MATCH_RECOGNIZE {
[ row_pattern_partition_by ]
[ row_pattern_order_by ]
[ row_pattern_measures ]
[ row_pattern_rows_per_match ]
[ row_pattern_skip_to ]
PATTERN (row_pattern)
[ row_pattern_subset_clause ]
DEFINE row_pattern_definition_list
}
unpivot_clause
pivot_clause
subquery_restriction_clause
partition_extention_clause
sample_clause
