The SQL syntax explain added to Oracle 11g is very convenient and practical for Row-and-column conversion. Today, I found a problem.
If a table has multiple numeric fields and only sums one of them, the data is converted into one row and divided into multiple rows.
Create Table patient pre-submission record T (ID number (8), settlement method varchar2 (20), punch pre-delivery number (16, 5 ));
Insert into patient prepayment record t
Select 1, 'cash', 123 from dual
Union all
Select 2, 'check', 1000 from dual
Union all
Select 3, 'credit card', 500 from dual;
Select "cash", "check", "Credit Card"
From Patient prepayment record t
Settlement (sum (pre-payment) for settlement method in ('cash' as "cash", 'check' as "check", 'credit card' as "Credit Card "));
Bytes --------------------------------------------------------------------------------------------
Cash check credit card
1 123
2 500
3 1000
Run the following statement to delete the value of the ID field in the table. The query result is as follows:
Update patient pre-submission record t set id = NULL;
Bytes --------------------------------------------------------------------------------------------
Cash check credit card
1 123 1000 500
This is the actual result.
Why?
After analysis, we found that sums (sum (forward )..., although only the value of the "Forward" field is obtained, the query results will be affected as long as there are other numeric types in the table.
It is very likely that this is a bug of 11G. Therefore, to make the results meet expectations, it is best to use a subquery to limit the result set to only one numeric type for the summary field.
Select "cash", "check", "Credit Card"
From (select settlement method, from patient prepayment record T)
Settlement (sum (pre-payment) for settlement method in ('cash' as "cash", 'check' as "check", 'credit card' as "Credit Card "))
Bytes --------------------------------------------------------------------------------------------
Cash check credit card
1 123 1000 500