Using internal outsystems DB, how do I to get only row (unique, without repeats row)
Question

Hello... Please... (I DON'T USE EXTERNAL DATABASE....I 'm using INTERNAL outsystems DB)

How do I to get the only rows (unique row by clienteid,f_fecha = group)... 

If I used group by in the aggregate window, I get ONLY 3 group columns, the foreing columns (relational tables as cliente,cargo,ciudad doesn't appear).....

PLEASE help me... thanks.

In tradicional language I create 2 cursor sql... like this:

Select * from vitacora group by clienteid,f_fecha,userid into cursor vitacoraTEMP

Select * from vitacoraTEMP,cliente,actividad where vitacoratemp.clientid = cliente.clientid and vitacoraTEMP.actividadid = actividad.actividadid into cursor FINALSQL....


Hi,

I don't think there is a need for advanced SQL, as there is also no need for those 2 steps in how you normally would do it.

But you really must clarify exactly what you want, from your example, I get that you want to group by client, and only have one row in your screen for each client.  But if that's what you want, then you have to determine for yourself what you want with all the other details you show on the screen.  For all attributes that are part of the client table, such as ciudad, that's easy, there's only one per client, so only one per row, in your aggregate you can just add that as one of the group by fields.

But you'll have to decide what you want with a column like proxima and actividad, as there are more of these per row you have in your result set.  In your screen prints above, you only have one of them (such as for enrique bogota there are 3 proxima, 23/feb, 25/feb, 28/feb, you are saying you want it to show 23/feb, are you sure of that ??)  If you only want one row per client, you need to decide if you still want to show this column, and if so, what kind of aggregation (you could decide you want to see the max value, or you could decide you want to see a count,...)


But once you know what you want, there is no reason this shouldn't be possible with an aggregate.


Dorine

mvp_badge
MVP

Hi Dorine,

Actually, he has a table with multiple records for different clients, and want the last record for the clients he is trying to fetch. One record per client.

I am not sure you can do that in an Aggregate...
 


Oh yeah,

I see now in the red text, i was looking at the sql in the original post, there's nothing there about picking the last one.

So yes, might be hard to do in a single aggregate... I'd have to think a bit more

Champion

Hi Carrara

You should use Advance Sql instead of Aggeregate.

If your DB is SQL server please refer to like below. 

https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15
D. Using ROW_NUMBER() with PARTITION

Kind regards,

Please...

(I don't use external database....I 'm using "INTERNAL" outsystems DB)

Champion

Outsystems DB internal Db by default means Sql Server.

So you can use ROW_NUMBER() with PARTITION  in Adwance SQL tool.

mvp_badge
MVP

Actually, it's possible to have other databases as the default database, like Oracle.

Champion

Hi Eduardo,

Yes, you are right. Thank you.

Kind regards, 

Hi,

I don't think there is a need for advanced SQL, as there is also no need for those 2 steps in how you normally would do it.

But you really must clarify exactly what you want, from your example, I get that you want to group by client, and only have one row in your screen for each client.  But if that's what you want, then you have to determine for yourself what you want with all the other details you show on the screen.  For all attributes that are part of the client table, such as ciudad, that's easy, there's only one per client, so only one per row, in your aggregate you can just add that as one of the group by fields.

But you'll have to decide what you want with a column like proxima and actividad, as there are more of these per row you have in your result set.  In your screen prints above, you only have one of them (such as for enrique bogota there are 3 proxima, 23/feb, 25/feb, 28/feb, you are saying you want it to show 23/feb, are you sure of that ??)  If you only want one row per client, you need to decide if you still want to show this column, and if so, what kind of aggregation (you could decide you want to see the max value, or you could decide you want to see a count,...)


But once you know what you want, there is no reason this shouldn't be possible with an aggregate.


Dorine

mvp_badge
MVP

Hi Dorine,

Actually, he has a table with multiple records for different clients, and want the last record for the clients he is trying to fetch. One record per client.

I am not sure you can do that in an Aggregate...
 


Oh yeah,

I see now in the red text, i was looking at the sql in the original post, there's nothing there about picking the last one.

So yes, might be hard to do in a single aggregate... I'd have to think a bit more

Hi @Carrara Admin 

According to the screenshot, the records are different, if you check for example in my screenshot the dates, so the results are ok.

Now if this is not the expected result, you need to re-check your query, and be more explicit in what is the expected result, i.e., if you want only one record what are the conditions in order to return it?

For example... according to these three records, what record must be returned?

Regards

Gonçalo Almeida

Hello....

I always show the last record added....(this row are vendor asistant, vendor support) in this case 28-feb-2021....max(date)....

But aggregate does't have unique max row by unique clientid....(the group does't works...

mvp_badge
MVP

Hello Carrara

Just follow Tom advice. Use an SQL Tool instead of an aggregate.
I am pretty sure that even if itis possible to do this with an Aggregate, it would be VERY innecficient.

Cheers

In that case, you need to use an advanced SQL widget instead of an aggregate, because you need a sub-query inside and aggregate does not allow sub-queries.


Regards

Gonçalo Almeida

VERY THANKS to everybody....

I have solve the solution based on yours answers....

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.