Kbase P144673: SQL-92: Error 7579 when creating a VIEW containing a FUNCTION or EXPRESSION
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
24/08/2009 |
|
Status: Verified
SYMPTOM(s):
Error 7579 when creating a VIEW
View column list must be specified as expressions are given (7579)
SQL statement example (using sports2000 demo database):
CREATE VIEW v_num_cust AS SELECT COUNT(*) FROM pub.customer ;
=== SQL Exception 1 ===
SQLState=HY000
ErrorCode=-20074
[DataDirect][OpenEdge JDBC Driver][OpenEdge] View column list must be specified as expressions are given (7579)
FACT(s) (Environment):
VIEW definition contains a FUNCTION or EXPRESSION
All Supported Operating Systems
OpenEdge 10.x
Progress 9.x
CAUSE:
The CREATE VIEW statement is missing a column list reference for the column(s) used in the SELECT statement, including columns for the return value for FUNCTIONS and EXPRESSIONS.
FIX:
Modify the CREATE VIEW statement as below:
CREATE VIEW v_num_cust (numberOfCustomers)
AS SELECT COUNT(*)
FROM pub.customer ;
SELECT * FROM v_num_cust ;
NUMBEROFCUSTOMERS
--------------------
1117
Correct syntax to be used:
CREATE VIEW <view_name> (column_list) ... <remainder of VIEW definition>
The number of columns in <column_list> should always match the number of columns used in the VIEW definition, including columns containing values of functions and expressions.