CODEWITHSUNDEEP
snowflake-cloud-data-platform
Priya Chauhan
Priya Chauhan

Reputation: 485

SQL compilation error: syntax error line 1 at position 8 unexpected '-'

I am getting the SQL compilation error: syntax error line 1 at position 8 unexpected '-'.

sql query is :

INSERT INTO table_name(col_names) values();

position 8 is I of "INTO"

I am stuck with this tried searching the character from notepad++, but couldn't find.

Upvotes: 0

Views: 27063

Answers (3)

Priya Chauhan
Priya Chauhan

Reputation: 485

Thanks, @David Garrison and @Simeon Pilgrim for the time you put into answering my question.

The cause of my error was: I was using a Postgres data type: INTERVAL in my subquery with hyphen in it.

select CAST((DATE_TRUNC('MONTH', CURRENT_DATE) + INTERVAL '1 MONTH - 1 DAY') AS - 
DATE

so , snowflake was not compatible with the hyphen above, but it was not showing the error in the correct line number while compiling the SQL instead it was showing the error in the first line INSERT INTO as explained in my question.(I think its a bug in snowflake)

So to make it compatible in snowflake , I used :

select CAST(LAST_DAY((DATE_TRUNC('MONTH', CURRENT_DATE))) AS DATE);

INTERVAL will work in snowflake but without hyphen, that is : INTERVAL '30 DAYS', but this is not fair with the month of February, so I used LAST_DAY function.

and the query went fine :D

Upvotes: 1

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

You example of "it breaks" need to be reproducible. So you need to paste you not-working code (which is clear you don't want to) or toy sql that has the problem. It should look like:

create table toy_example(col_names text);

-- this works yippie!
insert into toy_example(col_names) values ('this is a value');

-- but this
insert into toy_example(col_names) values ();

/*
 Syntax error: unexpected ')'. (line 22)
*/

because as it stands:

I have some SQL it gives me a error

002020 (21S01): SQL compilation error: Insert value list does not match column list expecting 3 but got 1

my code looks like:

SELECT 1;

cannot really be worked with.

Upvotes: 2

David Garrison
David Garrison

Reputation: 2880

From your toy example your list of columns doesn't match the number of columns in your values clause. You're trying to insert 0 values into some number of columns.

You need something like

INSERT INTO table_name(col_1, col_2, col_3) values(1, 'hello', 'world');

I'm getting a similar, less than helpful error when I try something similar to your code. I think the parser is simply not able to comprehend a values clause with no input, so it's not even managing to figure out where the issue is, and it's just giving a generic "there's something wrong with your insert" error.

Upvotes: 1

Related Questions