CODEWITHSUNDEEP
sqlmysqlohdsi-omop
Sana
Sana

Reputation: 37

SQL error when a temporary table can't be made using #

I tried to use "eMERGE OMOP MS SQL version of the original T2DM case algorithm" in the https://phekb.org/phenotype/type-2-diabetes-mellitus. When I run the code I got this error: "Syntax error at or near '#'". The problem should be in here:

SELECT event_id, person_id, start_date, end_date, 
     op_start_date, op_end_date, visit_occurrence_id

INTO #qualified_events

FROM 
(
  select pe.event_id, pe.person_id, pe.start_date, pe.end_date,
    pe.op_start_date, pe.op_end_date, 
    row_number() over (partition by pe.person_id order by pe.start_date ASC) as ordinal, 
    pe.visit_occurrence_id
  FROM primary_events pe

I tried to make a temporary table instead of using '#', but the error exists. Any suggestion would help me a lot. Thanks.

Upvotes: 0

Views: 59

Answers (1)

Cetin Basoz
Cetin Basoz

Reputation: 23827

If it is MySQL, then write it like:

CREATE TEMPORARY TABLE IF NOT EXISTS temp_qualified_events AS (

  select pe.event_id, pe.person_id, pe.start_date, pe.end_date,
    pe.op_start_date, pe.op_end_date, 
    row_number() over (partition by pe.person_id order by pe.start_date ASC) as ordinal, 
    pe.visit_occurrence_id
  FROM primary_events pe
);

'#' prefix is for MS SQL temp tables.

EDIT: You can see a demo here created with version 5.7 of MySQL DBFiddle demo

Upvotes: 0

Related Questions