Comparing different Data Types

Comparing different Data Types

Hi everyone,

I started to work with OutSystems platform few time ago and I am facing some difficulties when I try to compare different data types in advanced queries. I am developing an application that can only use Oracle DBMS. The necessary tables/entities are imported from an external database through reference and using Integration Studio. I will describe one of my problems:

In my advanced query, when reaching the ‘WHERE’ statement I want to compare an attribute from one table with one of my query’s input parameter. The attribute from the table is a ‘text’ data type with length 12 and the input parameter of the query is a ‘decimal’ data type with length 37 and decimals 8 (OutSystems default). Since I only use Oracle DBMS and based in what I already read in OutSystems documentation, I assume that Oracle converts the ‘text’ data type to ‘varchar2(12)’ and the ‘decimal’ to ‘number(37,8)’. Obviously, we cannot compare these 2 types directly. I already used lots of Oracle functions like ‘CAST’, ‘TO_NUMBER’, ‘TO_CHAR’ with and without coherent lengths, but I always get error messages like ‘invalid number’. I believe that this is a small detail problem and easy to solve, however I already spent lots of hours trying to solve it without success.

Am I thinking in a wrong way or doing something wrong? I would much appreciate your help in this matter.

Thank you.
Hi, Luís.

I believe the key to solve your problem, is to set correctly the data type of your input parameter.

If you are comparing dates, the query's input parameter should not be a ‘decimal’ data type with length 37 and decimals 8 (OutSystems default). 

You should try to do the conversion before passing the value to the input.

Could you provide more information or an example eSpace?

Kind Regards.
Daniel Martins
Try change the input parameter to text, and in value that you pass use DecimalToText function, it's a function that Outsystems have to convert a decimal value to text.

Nuno Rolo
Hi! I finnally solved my problem! I decided to add text to my advanced query in the WHERE block by making an input parameter of the query "expand inline". That way i could follow Nuno's sugestion and use the Outsystems functions (like DecimalToText) outside de query together with function "EncodeSql". Now the Query is working fine. Thank you both for your help.

Luis Fernandes