Advanced query syntax and tips

Advanced query syntax and tips

  
The Advanced Query tool is a powerful way to do more complex database operations.

To create an advanced query you must first define an output structure composed of entities or structures.

OutSystems Hub Server parses the SQL statement and translates it into pure SQL following these rules:
- Entities must be written like {Entity name}.
- Attributes must be written like {Entity name}.[Attribute name].
- Parameters must be written like @parametername
- The order of the attributes in the Output Structure must match the result of the query.
- If you want to have text in the SQL statement, you must use single quotes '. For example:
select {customer}.[id] from {customer} where {customer}.[name] = 'John'

Tip 1: Dbl-click entities from the output structure to add it to SQL statement
Tip 2: Write all entity (or structure) attributes in the SQL statement dragging the entity from the eSpace tree to it
Tip 3: Drag a single attribute from the eSpace tree to add it to SQL statement

For more information please refer to OutSystems Service Studio Help\Advanced entity management\Execute advanced queries\Advanced query logic
Hi,

Is there a way to undo changes in an Advanced Query? CTRL+Z doesn't work, and there's no cancel button... (and closing the window is the same as pressing ok, I tried that too)

Any ideias, or should I submit this as an issue? :P

Carlos
Ctrl-Z will be implemented in advanced queries very soon.

Thank you very much for the feedback.
Hi there,

Is it possible to have a piece of SQL code in a variable and can I use that variable (not escaped) as part of the database query?

For example: I'm creating an alert system that sends alerts when certain boundaries are crossed. These boundaries are the sum of messages in a message log for example. So I'd like to have the user configure it's own rules of when to recieve an alert, like:

if message log contains [more/less] than X messages for customer Y, please alert me

I need to store these rules in the database, and process them every hour. To make this I'd like to store the [more/less] part as '>' or '<' in the database, and use the contents of that field in another SQL statement that actually does the check to send an alert or not?

Ofcourse I could do a simple IF-statement, but that means code (adv query) redundancy. And for more complex rules this wouldn't be possible. I'd really like to build the WHERE part of an advanced query in a variable , constructing it part by part, and then feeding it to the database.

Is this kind of advanced behaviour possible?

Thanks,

Jurriaan
You can achieve this behavior using the Expand inline query parameter property.
For more information please refer to
Service Studio Help\Advanced entity management\Executing advanced queries\Advanced input parameters
Cool! Thanks
When I add an entity to the output structure, I have to change de query because the entity doesn' t go to the end of that list.
Is there any way to avoid this?
Hi,

Entities (or Structures) are added to a Query output structure (and indeed to any Record Type definition across the platform) in a set order that depends on the Hub Edition-internal representation of that Entity.

This allows for the uniformization of record types: any two record structures that contain the same entities are, effectively, the same structure - regardless of the order by which you added the Entities to each of them. This greatly simplifies the use of the platform because you can, for example, assign one such record to the other directly. Obviously, to maintain this consistency, the re-ordering of Entities/Structures in a record type is not allowed.

This auto-ordering feature is transparent throughout the whole platform, except - as you noted - the Advanced Queries. Here you will indeed need to be attentive that the order of the columns in your SQL matches the order of the Entities/Structures in the output structure. To aid you on this, the Verify operation will issue warnings when it detects mismatches or misalignments between the structure and the columns in the Queries. Furthermore, in the new (soon to be publicly released) 3.1 version, you will be able to test your queries directly from Service Studio which will make any such errors immediately detectable.

Hope this helps. Best regards,


Miguel
HI there, I have an advanced piece of SQL that looks something like this:
SELECT
case when {Task}.[TaskStateID] = 8
then {Task}.[LastUpdate]
end
FROM
{Task}

So only when a task is in a certain state, do I want to return that date field, even if I add:
else null
to the case, I always seem to get a "1900-01-01" date returned ...

I simply dont want it to return anything in this scenario ...

how best for me to go about doing this
Hi Declan,

the platform always returns a date field like "1900-01-01" whenever the date being returned is a null date. By other words, this is the way the platform represents a null value in a date field (instead of returning an empty field).
If you compare this value with the built-in function "NullDate()" it will return true meaning the field contains a null date.

Hope this helps,

Cláudia Macedo
Hi Carlos

There is a way to undo changes in an advanced query: you just "reopen" de query, before you save it.
Hi,

Generating the custom SQL using a variable did not work even after setting the input parameter "Expand Inline" to "Yes". It throws the incorrect syntax as "Incorrect syntax near ')' ".

My SQL is as below:

Select {TableName}.[Column1]
From {TableName}
where {TableName}.[Column1] in (@In_Parameter)

The @In_Parameter values can be a comma separated values like "1,2,3,4" e.t.c.

The above query works fine with the static values:

Select {TableName}.[Column1]
From {TableName}
where {TableName}.[Column1] in (1,2,3,4)

Where as it throws the Incorrect syntax error with the Input parameter @In_Parameter.

Please Adivese.
Thanks
Dayanand Kesa
Hi Dayanand,

Welcome to our community!

@In_Parameter is configured as 'text' (inline)?

If you fill in the Test Value with "1,2,3,4" (including the quotes), within Test Inputs tab...

and click the Test query button you still get a runtime syntax error?

Best regards,

Duarte Gouveia
Hi all.

How can I use the "List_SortColumn_GetOrderBy(...)"  in a advanced query?
I have the parameter @OrderBy, but don't work.

Can you help, please?

Thanks in advance.
Is it set as "Expand Inline = Yes" ?

Hi.

Yes, I have "Expand Inline = Yes".
The problem is that I have two Inner Join for the same entity, and I use the alias for one. When I put in the parameter @OrderBy {ENTITY}.[Field], it don't recognise the entity!!
How have I do?

Thank you.

Sorry, but still doesn't work.
It works when I do the "Test" in the advanced query, but when run the page, it returns the error "entity_2 found in 'Expand Inline' parameter is an unknown Entity" because it doesn't recognize the entity name used like alias.

Someone help?

Thanks.