OHDSI Home | Forums | Wiki | Github

[OHDSI Large-Scale Population-Level Evidence Generation study] int size issue

Researchers
#1

Hello. I’m seojeong, a graduate student of Ajou university in Korea.
I have a issue while I tried to run the GitHub code proposed by this study.
When I executed codes like below,

           ---------------------------------------------------------------------------------------
           execute(connectionDetails = connectionDetails,
               cdmDatabaseSchema = "[Camel_DB].[dbo]",
               #oracleTempSchema = NULL,
               workDatabaseSchema = "[scratch_Shin_SJ].[dbo]",
               studyCohortTable = "ohdsi_depression_cohorts",
               exposureCohortSummaryTable = "ohdsi_depression_exposure_summary",
               workFolder = "c:/temp/LargeScalePopEst",
               maxCores = 4,
               createCohorts = TRUE,
               fetchAllDataFromServer = TRUE,
               injectSignals = TRUE,
               generateAllCohortMethodDataObjects = TRUE,
               runCohortMethod = TRUE)
           ----------------------------------------------------------------------------------------

I got an error massage about the issue in readChar size. Please check the report I received.

           -----------------------------------------------------------------------------------------
           DBMS:
           sql server

           Error:
           execute JDBC update query failed in dbSendUpdate 
           (Arithmetic overflow error converting expression to data type int.)

           SQL:
           SELECT pair_id,
           	t_cohort_definition_id,
           	c_cohort_definition_id,
           	t_cohort_definition_id * 1000 + pair_id AS tprime_cohort_definition_id,
           	c_cohort_definition_id * 1000 + pair_id AS cprime_cohort_definition_id,
           	min_cohort_date,
           	max_cohort_date
           INTO #exposure_cohort_pairs
           FROM (
           	SELECT ROW_NUMBER() OVER (
	               	ORDER BY s1.cohort_definition_id,
	               		s2.cohort_definition_id
	               	) AS pair_id,
               	s1.cohort_definition_id AS t_cohort_definition_id,
               	s2.cohort_definition_id AS c_cohort_definition_id,
               	CASE 
               		WHEN s1.min_cohort_date > s2.min_cohort_date
               			THEN s1.min_cohort_date
               		ELSE s2.min_cohort_date
               		END AS min_cohort_date,
               	CASE 
               		WHEN s1.max_cohort_date < s2.max_cohort_date
               			THEN s1.max_cohort_date
               		ELSE s2.max_cohort_date
               		END AS max_cohort_date
               FROM #exposure_cohort_summary s1,
               	#exposure_cohort_summary s2
           	WHERE s1.cohort_definition_id < s2.cohort_definition_id
           	) t1
           -----------------------------------------------------------------------------------------

I think this query should be fixed as follows.

            From. -------------------------------------------------------------------
              s1.cohort_definition_id AS t_cohort_definition_id,
	      s2.cohort_definition_id AS c_cohort_definition_id,
            -------------------------------------------------------------------------

            To. ---------------------------------------------------------------------
              CAST(s1.cohort_definition_id AS BIGINT) AS t_cohort_definition_id,
	      CAST(s2.cohort_definition_id AS BIGINT) AS c_cohort_definition_id,
            -------------------------------------------------------------------------

When we tried to run the code with edited version, the result was good.
Could you review this issue and confirm the code that I fixed?

Thank you!

#2

Hi @ShinSeojeong,

Thanks! Yes, that looks good. I’m surprised SQL Server couldn’t figure this out by itself (it worked on our APS without problem), but your changes make it clearer what needs to happen.

Do you want to make these changes and create a pull request, or do you want me to apply them to the code on GitHub?

#3

Hi, thank you for the reply.
Because I’m not familiar with the code-editing procedures of OHDSI, it will be great if you apply them to the code on GitHub.

Thanks !

#4

Implemented your changes here. Thanks!

#5

Would it be a better solution to define cohort_definition_id as bigint to avoid the conversion issue?

t