Multi-part identifier leads to problems in MSSQL

[birthe-lorenz]

Describe the bug
When using the library against a MSSQL database, the created SQL statement leads to an error, because the alias "c" is used multible times for subselects. This is allowed in postgres, but MSSQL is more strict.

To Reproduce
Currently the code is run by an other R-Project. My knowlege in R is very limited, so I can not provide a good example.

Expected behavior

Instead of using the same alias in the subselects (here orignall always c) use different (like c1, c2, ...) : the following statement also workds for MSSQL

SELECT 0 as codeset_id, c.concept_id
FROM (
select distinct I.concept_id
FROM (
select concept_id
from "digione".CONCEPT
where (concept_id in (4162253,36564848,4092513,4158563,4158563,4187850,4187851,4188545, 4237178,441513,44502548,4221403,4295715))
UNION
select c1.concept_id
from "digione".CONCEPT c1
join "digione".CONCEPT_ANCESTOR ca on c1.concept_id = ca.descendant_concept_id
WHERE c1.invalid_reason is null and (ca.ancestor_concept_id in (4162253,36564848,4092513,4158563,4158563,4187850,4187851,4188545,4237178,441513,44502548,4221403,4295715)) ) I
LEFT JOIN (
select concept_id from "digione".CONCEPT
where (concept_id in (4147164,139750,4162276,1075593,36712738,36712739))
UNION select c2.concept_id from "digione".CONCEPT c2 join "digione".CONCEPT_ANCESTOR ca on
c2.concept_id = ca.descendant_concept_id WHERE c2.invalid_reason is null
and (ca.ancestor_concept_id in (4147164,139750,4162276,1075593,36712738,36712739)) ) E
ON I.concept_id = E.concept_id WHERE E.concept_id is null ) c;

Screenshots
no screenshot, but the generated SQL-Statement and stack trace

Backtrace:

9. ├─CDMConnector::generateCohortSet(...) at DigiONE04-Flat-file-generation/R/0_CohortGeneration.R:36:1
10. │ └─CDMConnector (local) generate(i) at CDMConnector/R/generateCohortSet.R:581:5
11. │ ├─DBI::dbExecute(con, sql[k], immediate = TRUE) at CDMConnector/R/generateCohortSet.R:570:7
12. │ └─DBI::dbExecute(con, sql[k], immediate = TRUE) at DBI/R/dbExecute.R:57:3
13. │ └─odbc (local) .local(conn, statement, ...)
14. │ ├─DBI::dbSendStatement(conn, statement, params = params, ..., immediate = immediate)
15. │ └─odbc::dbSendStatement(...)
16. │ └─odbc (local) .local(conn, statement, ...)
17. │ └─odbc:::OdbcResult(...)
18. │ └─odbc:::new_result(p = connection@ptr, sql = statement, immediate = immediate)

The multi-part identifier "c.concept_id" could not be bound. •
INSERT INTO #Codesets (codeset_id, concept_id)
SELECT 0 as codeset_id, c.concept_id
FROM (
select distinct I.concept_id
FROM (
select concept_id
from "digione".CONCEPT
where (concept_id in (4162253,36564848,4092513,4158563,4158563,4187850,4187851,4188545, 4237178,441513,44502548,4221403,4295715))
UNION
select c.concept_id
from "digione".CONCEPT c
join "digione".CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
WHERE c.invalid_reason is null and (ca.ancestor_concept_id in (4162253,36564848,4092513,4158563,4158563,4187850,4187851,4188545,4237178,441513,44502548,4221403,4295715)) ) I
LEFT JOIN (
select concept_id from "digione".CONCEPT
where (concept_id in (4147164,139750,4162276,1075593,36712738,36712739))
UNION select c.concept_id from "digione".CONCEPT c join "digione".CONCEPT_ANCESTOR ca on
c.concept_id = ca.descendant_concept_id WHERE c.invalid_reason is null
and (ca.ancestor_concept_id in (4147164,139750,4162276,1075593,36712738,36712739)) ) E
ON I.concept_id = E.concept_id WHERE E.concept_id is null ) C;

Additional context
n/a

Kind regards
Birthe Lorenz