ERROR: relation "results.achilles_results" does not exist

Implementers
4

Hi @Chris_Knoll,

Thank you for the help and explanation, as always.

I solved the problem, and let me share what I did here, so if anyone has the same issue, at least this is one thing she/he can try.

This error message,

org.postgresql.util.PSQLException: ERROR: relation "results.achilles_results" does not exist

is actually due to some other issues, which prevent it from being created successfully, and once we fix those other issues, the problem will go away and results.achilles_results table will be created.

How do you know what are the other issues? There are some txt documents created and there is also a log file, which can all be used as a clue about what these other issues are. For example, one txt file tells me the following issue:

DBMS:

postgresql

Error:

org.postgresql.util.PSQLException: ERROR: relation "cdm.specimen" does not exist

Position: 5180

SQL:

--HINT DISTRIBUTE_ON_KEY(stratum_1)

CREATE TEMP TABLE s_tmpach_1900

so I know I am missing this cdm.specimen table, which I can add it right away. Once I did that, this particular issue is fixed. By the way, the reason I did not have that table at the first place was because I was using the synpuf test dataset and I thought this specimen table was not needed.

Again, we just need to clear all the issues, one by one. Once all the issues are gone, the results table will be create and you can see the Data Source in Atlas. Since we are on this topic, I had one issue like this,

DBMS:
postgresql

Error:
org.postgresql.util.PSQLException: ERROR: column de.drug_era_start_date does not exist
  Position: 454

SQL:
--HINT DISTRIBUTE_ON_KEY(stratum_1)
CREATE TEMP TABLE s_tmpach_901

AS
SELECT
901 AS analysis_id,
        CAST(de.drug_concept_id AS VARCHAR(255)) AS stratum_1,
        CAST(NULL AS VARCHAR(255)) AS stratum_2,
        CAST(NULL AS VARCHAR(255)) AS stratum_3,
        CAST(NULL AS VARCHAR(255)) AS stratum_4,
        CAST(NULL AS VARCHAR(255)) AS stratum_5,
        COUNT(de.person_id) AS count_value

FROM
cdm.drug_era de

Note de is alias of cmd.drug_era. So is this some kind of version error? apparently, in my cdm.drug_era table, I had a field called column de.drug_era_start_datetime, but it was de.drug_era_start_date that Achilles tool is looking for. And I did specified cdmVersion = “5” … so this is still a big mystery to me.

One last question - I never truly understand about the DDL scripts generated by using this URL:

http://localhost:8089/WebAPI/ddl/results?dialect=postgresql&schema=results&vocabSchema=cdm&tempSchema=temp&initConceptHierarchy=true

exactly when should we use the generated DDL scripts?

Thanks,
liyang

1 Like