CODEWITHSUNDEEP
sqlpostgresql
Anusha
Anusha

Reputation: 294

Postgres query returning SQL Error syntax error at "Where"

I have the following query, when I execute it I'm getting an error 

INSERT INTO AM_EVENT_STATUS (EVENT_STATUS_ID, EVENT_ID, TIMESTAMP,OLD_STATUS_ID, NEW_STATUS_ID, NOTE, USERNAME)
SELECT
"external_id" AS EVENT_STATUS_ID,
"external_id" AS EVENT_ID,
"timestamp"   AS TIMESTAMP,
NULL          AS OLD_STATUS_ID,
'unknown'     AS NEW_STATUS_ID,
NULL          AS NOTE,
NULL          AS USERNAME
FROM (SELECT "2cba77463c899fee1656b367e39892"
  FROM "2cba77463c899fee1656b367e39892"
  INNER JOIN (SELECT "external_id", MAX("timestamp") AS "timestamp"
            FROM "2cba77463c899fee1656b367e39892"
              GROUP BY "external_id") LATEST WHERE "2cba77463c899fee1656b367e39892"."external_id" = LATEST."external_id"
    AND "2cba77463c899fee1656b367e39892"."timestamp" = LATEST."timestamp"
    AND "2cba77463c899fee1656b367e39892"."event_type" = 'transaction_update');

Error is:

SQL Error [42601]: ERROR: syntax error at or near "WHERE" Position: 601 org.postgresql.util.PSQLException: ERROR: syntax error at or near "WHERE" Position: 601

What am I doing wrong here ? Thanks in advance.

Upvotes: 0

Views: 3353

Answers (2)

Anusha
Anusha

Reputation: 294

INSERT INTO "AM_EVENT_STATUS" ("EVENT_STATUS_ID", "EVENT_ID","TIMESTAMP", "OLD_STATUS_ID", "NEW_STATUS_ID", "NOTE", "USERNAME")
SELECT
"external_id" AS EVENT_STATUS_ID,
"external_id" AS EVENT_ID,
"timestamp"   AS TIMESTAMP,
NULL          AS OLD_STATUS_ID,
'unknown'     AS NEW_STATUS_ID,
NULL          AS NOTE,
NULL          AS USERNAME
FROM (SELECT "2cba77463c899fee1656b367e39892".*
    FROM "2cba77463c899fee1656b367e39892"
    INNER JOIN (SELECT "external_id", MAX("timestamp") AS "timestamp"
                FROM "2cba77463c899fee1656b367e39892"
                GROUP BY "external_id") AS LATEST 
    ON "2cba77463c899fee1656b367e39892"."external_id" = LATEST."external_id"
    AND "2cba77463c899fee1656b367e39892"."timestamp" = LATEST."timestamp"
    AND "2cba77463c899fee1656b367e39892"."event_type" = 'transaction_update') as EVENTSTATUS;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Change the LATEST WHERE to LATEST ON. You have a JOIN, so the correct comparison clause is ON, not WHERE.

You also need a table alias after the last parentheses.

Upvotes: 2

Related Questions