Anyone saw this error? (never mind the usefullness of the query, simply playing arround)
This query works fine:
select
c.Table_Catalog TableCatalog,
c.table_schema TableSchema,
c.TABLE_NAME TableName,
c.COLUMN_NAME ColumnName,
c.Column_Default ColumnDefault,
c.IS_NULLABLE IsNullable,
c.DATA_TYPE DataType,
c.CHARACTER_MAXIMUM_LENGTH MaxLength,
c.numeric_precision NumPrecision,
CASE WHEN (data_type = 'set')
then
-- Set value
trim(Trailing ')' from SUBSTRING(Column_Type, 5))
else
CASE WHEN (data_type = 'enum')
then
trim(Trailing ')' from SUBSTRING(Column_Type, 6))
else
''
end
end SetOrEnum
from
information_schema.columns c
However when creating a view based on the query it fails:
CREATE VIEW vAdmin_SelectColumns
AS
select
c.Table_Catalog TableCatalog,
c.table_schema TableSchema,
c.TABLE_NAME TableName,
c.COLUMN_NAME ColumnName,
c.Column_Default ColumnDefault,
c.IS_NULLABLE IsNullable,
c.DATA_TYPE DataType,
c.CHARACTER_MAXIMUM_LENGTH MaxLength,
c.numeric_precision NumPrecision,
CASE WHEN (data_type = 'set')
then
-- Set value
trim(Trailing ')' from SUBSTRING(Column_Type, 5))
else
CASE WHEN (data_type = 'enum')
then
trim(Trailing ')' from SUBSTRING(Column_Type, 6))
else
''
end
end SetOrEnum
from
information_schema.columns c
Then selecting the records from the view :
select * from vAdmin_SelectColumns gives me the error:
[Error] Script lines: 1-2 --------------------------
You have an error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near '_utf8')') else (case when
(`c`.`DATA_TYPE` = _utf8'enum') then rtrim(substr(`c`.' at line 1"
annyone?
Thx
****************************************************************************
Disclaimer:
This electronic transmission and any files attached to it are strictly
confidential and intended solely for the addressee. If you are not
the intended addressee, you must not disclose, copy or take any
action in reliance of this transmission. If you have received this
transmission in error, please notify the sender by return and delete
the transmission. Although the sender endeavors to maintain a
computer virus free network, the sender does not warrant that this
transmission is virus-free and will not be liable for any damages
resulting from any virus transmitted.
Thank You.
****************************************************************************

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.