Syntax error in SQL statement - H2 error 42001

Question

Upon running this SQL statement:

select TimeInterval, 
       ((((Timer*60)/1.0)*100)/((10.0*60)/60.0)) as 'Throughput-run_1_8_11' 
from StatExternalData, StatisticDefinition 
where StatisticDefinition.ID=StatExternalData.StatDefId 
      and StatisticName='PSI_CompTran_Successful_Cnt'  
order by TimeInterval asc

I get this error:

"select TimeInterval, ((((Timer*60)/1.0)*100)/((10.0*60)/60.0)) as 'Throughput-run_1_8_11'[*] from StatExternalData, StatisticDefinition where StatisticDefinition.ID=StatExternalData.StatDefId and StatisticName='PSI_CompTran_Successful_Cnt'  order by TimeInterval asc"; 
expected "identifier"; [42001-185]

I've figured out that the [*] is indicating what part of the statement is incorrect and that H2 error code 42001 signifies an invalid SQL statement, but I've been banging my head on the wall for weeks trying to figure out what the problem is, anyone have an idea?

Have you tried enclosing the column alias between double quotation marks `" "` instead of single ones (`' '`)? — Barranka, Apr 13 '15 at 16:41
also, try avoid using dashes `-`. Try **`Throughput_run_1_8_11`** instead of `Throughput-run_1_8_11` — Barranka, Apr 13 '15 at 16:44
`as 'Throughput-run_1_8_11' ` is invalid SQL. Single quotes are for string literals. Double quotes are for identifiers — a_horse_with_no_name, Apr 13 '15 at 17:38
thanks barranka abd a_horse_with_no_name, that was the issue! — SnoBro, Apr 16 '15 at 19:21

score 3 · Answer 1 · answered Apr 21 '16 at 10:26

3

I had the same issue:

My Entity looked like this:

@Entity
public class ShopCommentRating {

@NotNull
private Boolean like;

}

The resulting Query contained a [*]

To remove the error i had to change the field name to sth. like this:

@Entity
public class ShopCommentRating {

@NotNull
private Boolean commentLike;

}

'lower case camel case' name

answered Apr 21 '16 at 10:26

Simon Ludwig

1,587
1
19
26

In you case the "like" is reserved SQL word. Any other name will work. – Dmytro Maslenko Oct 22 '19 at 19:42
If you really have to use reserved keywords as column name (I don't advice it though), you can also [escape](https://stackoverflow.com/a/3463189/2049986) the column name. – Jacob van Lingen May 04 '21 at 13:16

score 0 · Answer 2 · answered May 29 '20 at 06:01

0

In case of 42001-197 you can also check for Oracle analytical functions and H2 version, which needs to be from 1.4.198 onward, especially for ROW_NUMBER OVER PARTITION.

answered May 29 '20 at 06:01

GrigoreG

21
3

score 0 · Answer 3 · answered Oct 06 '21 at 17:49

0

check out the SQL Grammar page from H2. In my case, the problem was table name 'order' and dash('-'), not underscore('_'). Silly but deadly.

In your query the dash of alias 'Throughput-run_1_8_11' would be problem.

answered Oct 06 '21 at 17:49

Kwonkyu Park

13
3

Tymur Kubai aka SirDiR · Answer 4 · 2022-05-20T11:14:42.873

0

No one seams to be curious what 185 means in [42001-185].

42001 is public static final int SYNTAX_ERROR_2 = 42001; source code

According to h2 sources, 185 is position of error in sql, but for me it looks really measleading because actually it is combination of row and symbol in row

185 -> means 1-row and 85-symbol.

edited May 20 '22 at 11:14

answered May 20 '22 at 11:06

Tymur Kubai aka SirDiR

364
2
14

Syntax error in SQL statement - H2 error 42001

4 Answers4