Hi ,


I have a problem in writing the advanced query.

This is my query statement

SELECT convert(varchar, {ChatRequest}.[CreatedOn], 23) AS Date,convert(varchar,{ChatRequest}.[CreatedOn], 8) AS ReceivedChatTime, convert(varchar,{ChatRequest}.[ChatPickupDate], 8) AS PickedupChatTime, {ChatExtension}.[DPDealId] AS DocuPrepNumber,
(ReceivedChatTime - Abs(PickedupChatTime)) AS AnswerSpeed
, Avg(AnswerSpeed) AS AverageSpeed, Count({ChatRequest}.[Id]) AS TotalChats, Max(AnswerSpeed) AS MaxWait from {ChatRequest}
Left JOIN {ChatExtension} ON {ChatExtension}.[ChatId] = {ChatRequest}.[ChatId]
Group By Date, ReceivedChatTime, PickedupChatTime,DocuPrepNumber, AverageSpeed, TotalChats, AnswerSpeed, MaxWait
Having {ChatRequest}.[CreatedOn] >= @StartDate
and {ChatRequest}.[CreatedOn] < @EndDate
and substring(CONVERT(VARCHAR, [CREATEDON], 108),0,9) between '08:30:00' and '18:00:00'


I am getting the following error

Database returned the following error:

Error in advanced query chats: InvalidColumnName Date

Invalid Column Name ReceivedChatTime so on.


How to fix this query?


Hi Ramya,

That query looks really bad. It has all kinds of converts and checks on dates as strings and whatnot. May I deduce from it that you are really not comfortable with writing SQL?

There's a good reason OutSystems has Aggregates, and you should use them whereever possible! Is there any reason you don't use an Aggregate instead of an SQL? My advice would be to use an Aggregate, it seems there's nothing in the query above that can't be done with an Aggregate (as far as I can see, as it's full of errors and weird stuff).

Hey Ramya,

Like Killian said, it might be easier (and better to understand for yourself and other developers) if you use aggregates instead of advanced queries.

To come back on the error you are having, it's caused by your Group By statement, SQL builds using a specific order.

  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER BY clause

As you can see, the SELECT clause is built at 5, group by is built at step 3.
Meaning that at the point the database is trying to compile the Group By statement, it has no idea of the aliases you created in your select statement.

You could solve this by creating a subquery where you return an aliased table.... but why not make it easier for yourself and just use aggregates? It doesn't hurt to use multiple aggregates if you need to collect some extra information, in some cases that might even be faster than subqueries or queries that use a group by.
Just make sure to use your indexes correctly and limit the results accordingly.

Joey Moree wrote:

Hey Ramya,

Like Killian said, it might be easier (and better to understand for yourself and other developers) if you use aggregates instead of advanced queries.

To come back on the error you are having, it's caused by your Group By statement, SQL builds using a specific order.

  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER BY clause

As you can see, the SELECT clause is built at 5, group by is built at step 3.
Meaning that at the point the database is trying to compile the Group By statement, it has no idea of the aliases you created in your select statement.

You could solve this by creating a subquery where you return an aliased table.... but why not make it easier for yourself and just use aggregates? It doesn't hurt to use multiple aggregates if you need to collect some extra information, in some cases that might even be faster than subqueries or queries that use a group by.
Just make sure to use your indexes correctly and limit the results accordingly.

Hi,

I am trying to use an aggregate function.

One scenario is such I am subtracting two Time columns using this expression for column
DiffSeconds(ReceivedChatTime,PickedupChatTime). 

But I have to check for '1900-01-01 00:00:00' and if its true then like to assign a different time value and calculate time. How can this be achieved ?

Second issue is I like to find the max value in a column. If I try Max(Column1) it wont take as it expects me to give minmum two values. 

Can you please help?


Thank you,

Ramya


Solution

Hey Ramya,

You could do an if check

DiffSeconds(if(ReceivedChatTime = NullDate(), OtherValue,ReceivedChatTime), PickedupChatTime)

For max, you seem to be confusing the Built-in Function  Max with the SQL function Max.

Inside an aggregate, right click on a column and select MAX, here you also have options for MIN, COUNT, AVERAGE and SUM.

Solution

The only issue I am facing now is, since I am using an aggregate function I have to display count, average of certain columns and I have to display Column values which are not part of aggregate.

I created two data sets one for aggregate and one more for select columns and with listappend all mapped the values to be exported to excel.

Problem is I want Avg, count and Max values to be shown only once and not in all rows. Is there a way to achieve this? For example I want TotalChat 10 to be displayed only once in row.

Thank you

Hey Ramya, 

This can be achieved by using a structure, let's call it ChatRequestData which holds:

- Max (integer)
- Total (integer)
- Average (decimal)
- ChatRequestList (list of ChatRequest)

Create a variable which

Create a variable for a ChatRequestDataList (list of ChatRequestData) and a ChatRequestData, variable.
Inside the loop fill the ChatRequestData with the current Max, Count, Avg (the grouped data) then append the ChatRequest records themselves (from the other aggregate) to the ChatRequestData.ChatRequestList

I hope this helps you a bit!