CODEWITHSUNDEEP
sqlpostgresqlsyntaxsqlite
sriramn
sriramn

Reputation: 2378

SQLite syntax error (Conversion from PostgreSQL)

I am trying to merge some contents from table1 and table2 into another table3. I have the following query up and running in PostgreSQL:

CREATE TABLE table3 AS SELECT 
table1.orig_zon AS orig_zon,
table1.dest_zon AS dest_zon,
table1.same_zon AS same_zon,
table1.adjacent AS adjacent,
table1.distance AS distance,
table1.da_ivtt AS da_ivtt1,
table1.da_ovtt AS da_ovtt1,
table1.tr_avail AS tr_avail1,
table1.tr_ivtt AS tr_ivtt1,
table1.tr_ovtt AS tr_ovtt1,
table1.tr_cost AS tr_cost1,
table1.au_cost AS au_cost1,
table1.sr_ivtt AS sr_ivtt1,
table1.sr_cost AS sr_cost1,
table2.da_ivtt AS da_ivtt2,
table2.da_ovtt AS da_ovtt2,
table2.tr_avail AS tr_avail2,
table2.tr_ivtt AS tr_ivtt2,
table2.tr_ovtt AS tr_ovtt2,
table2.tr_cost AS tr_cost2,
table2.au_cost AS au_cost2,
table2.sr_ivtt AS sr_ivtt2,
table2.sr_cost AS sr_cost2,
FROM table1, table2
WHERE ((table1.orig_zon = table2.orig_zon)) AND ((table1.dest_zon = table2.dest_zon));

I have tested the query for a very small dummy dataset and the result is as desired. However my actual database is in SQLite and when I run this in the sqlite3 via Linux shell I get an error as:

Error: near "table1": syntax error

Can anyone point me to the modifications I need to make in order to make this work in SQLite? Thanks for the help!

Edit1: After removing the trailing comma (see Craig's comment below) the issue persists.

Edit2: After playing around a bit I found the fix. Seems very silly but if I were to pass the contents of the query in a single line the statement works!

table2.sr_ivtt AS sr_ivtt2,
table2.sr_cost AS sr_cost2 FROM table1, table2 WHERE ((table1.orig_zon = table2.orig_zon)) AND ((table1.dest_zon = table2.dest_zon));

Oh! The bane of typecasting!!

Upvotes: 1

Views: 246

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324751

SQL doesn't permit a trailing comma in the SELECT list.

table2.sr_cost AS sr_cost2,
FROM table1, table2

should be:

table2.sr_cost AS sr_cost2
FROM table1, table2

Upvotes: 3

Marcin Wasiluk
Marcin Wasiluk

Reputation: 4874

You need to delete extra comma

table2.sr_ivtt AS sr_ivtt2,
table2.sr_cost AS sr_cost2
FROM table1, table2

Upvotes: 1

Related Questions