OHDSI Home | Forums | Wiki | Github

Atlas Characterization Error in oracle


(Park,CHANGHYUN) #1

When I implement “Characterizations” FUNCTION, it didint work!

[Didn’t woks page]


[Error Log]
image

.
.
.
image

When I look the Error QUERY. The checked line occurred Error. Because Oracle didn’t have CONCAT FUNCTION( in oracle use “||” ).
image

So!! I need Where can I fix the “Characterizations” FUNCTION.
I want to know source code directory in WebAPI.


(Joe Pallas) #2

The SqlRender package translates SQL to the target database dialect. In this case, it should have transformed CONCAT with more than 2 arguments into nested CONCATs (Oracle does have CONCAT, it just doesn’t support more than 2 arguments). It seems there is a bug in the mapping so it failed to do that.


(Chris Knoll) #3

Right, I think the solution is to update the query to use CONCAT() chains of only 2 args:

Instead of
CONCAT(A,B,C)

do:
CONCAT(CONCAT(A,B),C)

Pull requests welcome for this!

Edit, the query expression is defined here.
-Chris


(Joe Pallas) #4

I opened an issue against SqlRender, since the philosophy as I understand it is “write MS SQL Server SQL and let SqlRender translate it for other databases.” But I’m not set up to build or test SqlRender.

There isn’t anything wrong with changing the query to use nested concats, but I think that’s working around the problem rather than fixing it. Sometimes a workaround is the pragmatic path to a quick solution, though.


(Chris Knoll) #5

The query in question isn’t subject to SqlRender translation. It’s a WebAPI database call that goes through Hibernate to do the translation. However, in this case, it uses a custom query that will need to be written in a cross-dialect manner. I think this is the most pragmatic approach in this case, but it would be good to handle it for other queries that run through SqlRender.


(Joe Pallas) #6

Ouch. Looking at the SqlRender transformations, it looks like netezza and sqlite do not support concat, and of course SQL Server does not support the SQL standard “||” operator, using “+” instead. Writing a cross-dialect query that uses string concatenation could be a challenge.


(Chris Knoll) #7

PR Submitted.


(Park,CHANGHYUN) #8

Thanks All.
Like @Chris_Knoll’s linked solution.
After then it well working!
:slight_smile:


t