Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Creating achilles_result_concept_count table is different than the one from WebAPI #723

Closed
CyloNox opened this issue May 4, 2023 · 1 comment

Comments

@CyloNox
Copy link

CyloNox commented May 4, 2023

Describe the bug
When running Achilles in R on CDMs it produces a table called "@schema.achille_result_concept_count" along with the values being inserted into it. The problem that I'm seeing is that when calling the WebAPI "http://www.example.com/WebAPI/ddl/achilles" it produces an entirely different table with two additional columns.

To Reproduce
Goto the webapi ddl url as outlined in the description: http://www.example.com/WebAPI/ddl/achilles

Then look at the code within Achilles to see that it's different: https://github.com/OHDSI/Achilles/blob/c6b7adb6330e75c2311880db2eb3dc4c12341c4f/inst/sql/sql_server/analyses/create_result_concept_table.sql

Expected behavior
For them to produce the same table.

Screenshots
Here is what the table looks like based on the DDL call:
CREATE TABLE @results_schema.achilles_result_concept_count
(
concept_id int,
record_count bigint,
descendant_record_count bigint,
person_count bigint,
descendant_person_count bigint
);
Here is what the table looks like within Achille here:
SELECT
concepts.ancestor_id concept_id,
isnull(max(c1.agg_count_value), 0) record_count,
isnull(sum(c2.agg_count_value), 0) descendant_record_count
FROM concepts
LEFT JOIN counts c1 ON concepts.ancestor_id = c1.concept_id
LEFT JOIN counts c2 ON concepts.descendant_id = c2.concept_id
GROUP BY concepts.ancestor_id

Additional context
Another thing that I happen to noticed when running the DDL version is that the number of rows are drastically less than the number of rows that are produced in the Achilles in here.

@konstjar
Copy link

konstjar commented May 26, 2023

You should use the one generated by WebAPI endpoint: WebAPI/ddl/achilles

PS. See #696

@CyloNox CyloNox closed this as completed Oct 25, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants