Bug: CohortMethod.sql, varchar field in cohort_covariate_ref table not large enough? about cohortmethod

[pbr6cornell]

varchar(max) in the original MSSQL dialect SQL gets converted to blob in
oracle and postgresQL. it looks like for redshift, it should use
varchar(max):
http://docs.aws.amazon.com/redshift/latest/dg/r_Character_types.html

a good example where it appears postgres and redshift diverge...

On Wed, Sep 24, 2014 at 4:43 PM, Thomas Nyberg [email protected]
wrote:

This field doesn't seem to be large enough for all cases:

https://github.com/OHDSI/CohortMethod/blob/master/inst/sql/sql_server/CohortMethod.sql#L154

When that gets translated to redshift (at least in my case), the field
becomes varchar(256). But this isn't long enough for some cases at this
line in the CohortMethod.sql code:

https://github.com/OHDSI/CohortMethod/blob/master/inst/sql/sql_server/CohortMethod.sql#L681

That field can end up too long if the concept_name field is long. For
example, for concept_id 439181, we have that concept_name is: "Cortex
contusion without open intracranial wound AND with prolonged loss of
consciousness (more than 24 hours) without return to pre-existing conscious
level" which ends up being too long (total length of concatenated string is
274).

I have code that replicates this if anyone is curious (I doubt it), but
I'd have to clean it a bit so I'll only do that if someone's interested. I
guess the field size should be increased (though the "max" there is a
little ominous) or maybe the field should just be changed to "text"? I'm
not sure what the best solution is myself since I'm still not too
comfortable with all the inner workings of SqlRender, etc.

—
Reply to this email directly or view it on GitHub
#4.

[ApproximateIdentity]

Yeah so when cohort_covariate_ref is created currently (after redshift translation), here is its schema:

CREATE TEMP TABLE cohort_covariate_ref (
    covariate_id bigint,
    covariate_name VARCHAR,
    analysis_id int,
    concept_id int)
;

I handcoded it to instead be:

CREATE TEMP TABLE cohort_covariate_ref (
    covariate_id bigint,
    covariate_name VARCHAR(max),
    analysis_id int,
    concept_id int)
;

That fixes the problem as one would expect. So if this is fixed in the translation function it should be good to go.

[schuemie]

SqlRender now leaves VARCHAR(MAX) as is for Redshift, so this issue should be solved.