Hi,


I am trying to get the month name out of a date from a table in my external database using advanced query, and when I try to add the table name in {} some built in functions are not recognized anymore, and when I remove the {} it's recognzied, but the then table isn't recognized as a valid object!


Like in the screenshot here:

Hi Mohamed

Is your entity mapped into Service Studio?

Also noticed you don't have output defined in your advanced query.

try {trips}.[pickup_date] in the atribute and {trips} in the entity and put an output in the advance query

I tried guys, and that's what I am getting:

strange i just test this

 and it returns values.


Can you check if the field is of type date and the output is a text

Carlos Diogo Lima Gonçalves wrote:

strange i just test this

 and it returns values.


Can you check if the field is of type date and the output is a text

Yes, the data types are correct! 

I created a test entity internally in the same module, and it's working, but my tables are from external database, so maybe that's the reason?!


ok lets do this the other way. create an input of type text and set expand inline = yes


then write the query inside that  input and just put the variable inside the query like this:


Obs: this is not the standard thing you should do in outsystems but try if that fix your problem.


Don't forget to put output = to the select structure you are returning

Hi Mohamed,

You mentioned that the query you want to do is an external entity, what is the DB, Oracle?

Carlos Diogo Lima Gonçalves wrote:

ok lets do this the other way. create an input of type text and set expand inline = yes


then write the query inside that  input and just put the variable inside the query like this:


Obs: this is not the standard thing you should do in outsystems but try if that fix your problem.


Don't forget to put output = to the select structure you are returning


It can't recognize the table, it gives me error: Invalid object name

Nuno Rolo wrote:

Hi Mohamed,

You mentioned that the query you want to do is an external entity, what is the DB, Oracle?

It's EXASOL database


Ok.

So, in that case, you need to use a function that is recognized by that Database. 

Datename is SQL Server, Oracle as DatePart and extract to deal with dates.

Nuno Rolo wrote:

Ok.

So, in that case, you need to use a function that is recognized by that Database. 

Datename is SQL Server, Oracle as DatePart and extract to deal with dates.

so I should use EXASOL built in functions in the advanced query?
because I tried to use some, and they are not recognized by outsystems and giving errors


Mohamed Emam wrote:

Nuno Rolo wrote:

Ok.

So, in that case, you need to use a function that is recognized by that Database. 

Datename is SQL Server, Oracle as DatePart and extract to deal with dates.

so I should use EXASOL built in functions in the advanced query?
because I tried to use some, and they are not recognized by outsystems and giving errors



then try to use what i sugest with in build EXASOL Functions


Test somethign simple first like a select to see if works and then use EXASOL inbuild functions

Carlos Diogo Lima Gonçalves wrote:

Mohamed Emam wrote:

Nuno Rolo wrote:

Ok.

So, in that case, you need to use a function that is recognized by that Database. 

Datename is SQL Server, Oracle as DatePart and extract to deal with dates.

so I should use EXASOL built in functions in the advanced query?
because I tried to use some, and they are not recognized by outsystems and giving errors



then try to use what i sugest with in build EXASOL Functions


Test somethign simple first like a select to see if works and then use EXASOL inbuild functions

not working as well:


Solution

I used the built in functions of EXASOL directly in the advanced query and now it's working.


Thank you, guys :) 

Solution