dynamobi/dynamobi — Issue #681: [FRG-191] sql parsing exception for trim function syntax
Author: dynamobi-build
Created: 2011-12-30 03:42:53+00:00
State: open
Labels: FRG, SQL Parser

[reporter="elin", created="Wed, 30 Aug 2006 19:36:19 -0500 (GMT-05:00)"]
Hi John,

Anil is running into Sql parsing error when running the trim function with syntax below:

TRIM (BOTH FROM (COALESCE(cast(null as varchar(2)),'') || ' ' || COALESCE("User"."LastName",'')))

It's complaining about finding the parenthesis in front of the first coalesce, but I believe this syntax is correct for SQL2003: see p.277,273, etc in the 2nd section

I believe both of the following statements should work, but don't:

--------------

values trim (both from (coalesce(cast(null as varchar(2)))||' '||coalesce('junk ','')));

values trim (coalesce(cast(null as varchar(2)))||' '||coalesce('junk ',''));

-- the 1st statement

---------------------------------------

Error: org.eigenbase.sql.parser.SqlParseException: Encountered "(" at line 1, column 24.

Was expecting one of:

"CASE" ...

"CURRENT_DATE" ...

"CURRENT_DEFAULT_TRANSFORM_GROUP" ...

"CURRENT_PATH" ...

"CURRENT_ROLE" ...

"CURRENT_TIME" ...

"CURRENT_TIMESTAMP" ...

"CURRENT_USER" ...

"DATE" ...

"FALSE" ...

"INTERVAL" ...

"LOCALTIME" ...

"LOCALTIMESTAMP" ...

"MULTISET" ...

"NEW" ...

"NULL" ...

"SESSION_USER" ...

"SYSTEM_USER" ...

"TIME" ...

"TIMESTAMP" ...

"TRUE" ...

"UNKNOWN" ...

"USER" ...

...

...

...

...

...

...

...

...

...

...

"?" ...

"+" ...

"-" ...

...

...

"CAST" ...

"EXTRACT" ...

"POSITION" ...

"CONVERT" ...

"TRANSLATE" ...

"OVERLAY" ...

"FLOOR" ...

"CEIL" ...

"CEILING" ...

"SUBSTRING" ...

"TRIM" ...

"SPECIFIC" ...

"ABS" ...

"AVG" ...

"CARDINALITY" ...

"CHAR_LENGTH" ...

"CHARACTER_LENGTH" ...

"COALESCE" ...

"COLLECT" ...

"CUME_DIST" ...

"COUNT" ...

"DENSE_RANK" ...

"ELEMENT" ...

"EXP" ...

"FIRST_VALUE" ...

"FUSION" ...

"LAST_VALUE" ...

"LN" ...

"LOWER" ...

"MAX" ...

"MIN" ...

"MOD" ...

"NULLIF" ...

"OCTET_LENGTH" ...

"PERCENT_RANK" ...

"POWER" ...

"RANK" ...

"ROW_NUMBER" ...

"SQRT" ...

"SUM" ...

"UPPER" ... (state=,code=0)

net.sf.farrago.jdbc.FarragoJdbcUtil$FarragoSqlException: org.eigenbase.sql.parser.SqlParseException: Encountered "(" at line 1, column 24.

Was expecting one of:

"CASE" ...

"CURRENT_DATE" ...

"CURRENT_DEFAULT_TRANSFORM_GROUP" ...

"CURRENT_PATH" ...

"CURRENT_ROLE" ...

"CURRENT_TIME" ...

"CURRENT_TIMESTAMP" ...

"CURRENT_USER" ...

"DATE" ...

"FALSE" ...

"INTERVAL" ...

"LOCALTIME" ...

"LOCALTIMESTAMP" ...

"MULTISET" ...

"NEW" ...

"NULL" ...

"SESSION_USER" ...

"SYSTEM_USER" ...

"TIME" ...

"TIMESTAMP" ...

"TRUE" ...

"UNKNOWN" ...

"USER" ...

...

...

...

...

...

...

...

...

...

...

"?" ...

"+" ...

"-" ...

...

...

"CAST" ...

"EXTRACT" ...

"POSITION" ...

"CONVERT" ...

"TRANSLATE" ...

"OVERLAY" ...

"FLOOR" ...

"CEIL" ...

"CEILING" ...

"SUBSTRING" ...

"TRIM" ...

"SPECIFIC" ...

"ABS" ...

"AVG" ...

"CARDINALITY" ...

"CHAR_LENGTH" ...

"CHARACTER_LENGTH" ...

"COALESCE" ...

"COLLECT" ...

"CUME_DIST" ...

"COUNT" ...

"DENSE_RANK" ...

"ELEMENT" ...

"EXP" ...

"FIRST_VALUE" ...

"FUSION" ...

"LAST_VALUE" ...

"LN" ...

"LOWER" ...

"MAX" ...

"MIN" ...

"MOD" ...

"NULLIF" ...

"OCTET_LENGTH" ...

"PERCENT_RANK" ...

"POWER" ...

"RANK" ...

"ROW_NUMBER" ...

"SQRT" ...

"SUM" ...

"UPPER" ...

at net.sf.farrago.parser.impl.FarragoParserImpl.convertException(FarragoParserImpl.java:6394)

at net.sf.farrago.parser.impl.FarragoParserImpl.normalizeException(FarragoParserImpl.java:58)

at net.sf.farrago.parser.FarragoAbstractParser.parseSqlText(FarragoAbstractParser.java:149)

at net.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:920)

at net.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:862)at net.sf.farrago.db.FarragoDbStmtContext.prepare(FarragoDbStmtContext.java:105)

at net.sf.farrago.jdbc.engine.FarragoJdbcEngineStatement.execute(FarragoJdbcEngineStatement.java:107)

at sqlline.SqlLine$Commands.execute(Ljava.lang.String;Z)Z(Unknown Source)

at sqlline.SqlLine$Commands.sql(Ljava.lang.String;)Z(Unknown Source)

at sqlline.SqlLine.dispatch(Ljava.lang.String;)Z(Unknown Source)

at sqlline.SqlLine.begin([Ljava.lang.String;Ljava.io.InputStream;)V(Unknown Source)

at sqlline.SqlLine.mainWithInputRedirection([Ljava.lang.String;Ljava.io.InputStream;)V(Unknown Source)

at sqlline.SqlLine.main([Ljava.lang.String;)V(Unknown Source)

0: jdbc:luciddb:>

--------------------------------------

For the 2nd statement:

Error: org.eigenbase.sql.parser.SqlParseException: Encountered "||" at line 1, column 48.

Was expecting one of:

"FROM" ...

"OVER" ... (state=,code=0)

net.sf.farrago.jdbc.FarragoJdbcUtil$FarragoSqlException: org.eigenbase.sql.parser.SqlParseException: Encountered "||" at line 1, column 48.

Was expecting one of:

"FROM" ...

"OVER" ...

at net.sf.farrago.parser.impl.FarragoParserImpl.convertException(FarragoParserImpl.java:6394)

at net.sf.farrago.parser.impl.FarragoParserImpl.normalizeException(FarragoParserImpl.java:58)

at net.sf.farrago.parser.FarragoAbstractParser.parseSqlText(FarragoAbstractParser.java:149)

at net.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:920)

at net.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:862)at net.sf.farrago.db.FarragoDbStmtContext.prepare(FarragoDbStmtContext.java:105)

at net.sf.farrago.jdbc.engine.FarragoJdbcEngineStatement.execute(FarragoJdbcEngineStatement.java:107)

at sqlline.SqlLine$Commands.execute(Ljava.lang.String;Z)Z(Unknown Source)

at sqlline.SqlLine$Commands.sql(Ljava.lang.String;)Z(Unknown Source)

at sqlline.SqlLine.dispatch(Ljava.lang.String;)Z(Unknown Source)

at sqlline.SqlLine.begin([Ljava.lang.String;Ljava.io.InputStream;)V(Unknown Source)

at sqlline.SqlLine.mainWithInputRedirection([Ljava.lang.String;Ljava.io.InputStream;)V(Unknown Source)

at sqlline.SqlLine.main([Ljava.lang.String;)V(Unknown Source)

0: jdbc:luciddb:>