CODEWITHSUNDEEP
sqloracle-databasenullpivot
justin cress
justin cress

Reputation: 1831

Use ON NULL with create table from pivot result (in oracle)

Can ON NULL (or DEFAULT?) be used in conjunction with select * from () pivot () to replace nulls with zero when creating a table?

Explanation: A transaction table contains customer_id, item_qty, department, transaction_key, sales

The task is to create a table which provides a single row for each customer containing the number of items, transactions and sales in each department. 

create table merchandise_summary as
select * 
from (select customer_id, item_cd, department, transaction_key, sales from transaction_table)
pivot (
    sum(item_qty) items,
    count(distinct transaction_key) transactions,
    sum(sales) sales
 for department in ('socks', 'dresses', 'shoes')
)

This table will create null values for any customer not purchasing anything from the socks department, but in reality the value should be zero (the customer purchased zero socks).

A separate query could update the values from null to zero after the table is created, but is it possible to set a default for the columns? Assume there are many departments and a number of fields to aggregate, listing each column individually is not convenient. (And, isn't the pivot operator supposed to make these operations more elegant?)

Upvotes: 0

Views: 3572

Answers (1)

Multisync
Multisync

Reputation: 8797

Yes, it's possible, but you'd better use short aliases:

create table merchandise_summary as
select customer_id, item_cd, department, sock_s, sock_tr, dress_s, dress_tr, shoe_s, nvl(shoe_tr, 0) shoe_tr
from (select customer_id, item_cd, department, transaction_key, sales from transaction_table)
pivot (
    sum(item_qty) s,
    count(distinct transaction_key) tr,
    sum(sales) sales
 for department in ('socks' as sock, 'dresses' as dress, 'shoes' as shoe)
)

The database generates a name for each new column. If you do not provide an alias for an aggregation function, then the database uses each pivot column value as the name for each new column to which that aggregated value is transposed. If you provide an alias for an aggregation function, then the database generates a name for each new column to which that aggregated value is transposed by concatenating the pivot column name, the underscore character (_), and the aggregation function alias. If a generated column name exceeds the maximum length of a column name, then an ORA-00918 error is returned. To avoid this issue, specify a shorter alias for the pivot column heading, the aggregation function, or both.

"Assume there are many departments and a number of fields to aggregate, listing each column individually is not convenient."

I suppose you need to list all the columns anyway if you're using CTAS. Oracle doesn't give convenient names to pivot columns.

Upvotes: 2

Related Questions