How to return distinct lesson record

How to return distinct lesson record

  

Hi,

I have the following data and i only want to display distinct lesson name and the staff name.

LessonName

Date

TeacherName

AssistantName

Cooking
21/1/2018
Mr Tan
Ms Koh
Cooking
22/1/2018
Mr Loh

Sewing
1/1/2018

Ms Joey
Sewing
2/1/2018
Mr Johnny
Ms Mo
Baking
3/2/2018
Ms Poh


I have tried working on it and get the following output:

LessonName

StaffName

Cooking
Mr Tan
Ms Koh
Cooking
Mr Loh
Sewing
Ms Joey
Sewing
Mr Johnny
Ms Mo
Baking
Ms Poh


But this is not the output i want, i am looking to see the following output. It will display distinct lesson and group all the staff name together. May i know how can i achieve it?

LessonName

StaffName

Cooking
Mr Tan
Ms Koh
Mr Loh
Sewing
Ms Joey
Mr Johnny
Ms Mo
Baking
Ms Poh


hi jace,

What do you want to find?

You can only select the entity that is grouped:

or you can check this link for Advanced SQL:

https://www.outsystems.com/forums/discussion/31201/to-return-distinct-value/

regards,

IB

Hi Jace,

Of my knowledge you need to use an advance query to achieve this. You can user this query:

SELECT
     L.[LessonName],
     STUFF(
         (SELECT ',' + {LessonStaff}.[StaffName]
          FROM  {LessonStaff}
          WHERE {LessonStaff}.[LessonName] = L.[LessonName]
          FOR XML PATH (''))
          , 1, 1, '')  AS Staff
FROM {Lesson} AS L
GROUP BY L.[LessonName]

Btw is always prefer to link tables by ID.

Regards,

Marcelo

Hello IB,

I just want to display the trainer name together without any duplicate lesson name

Marcelo Ferreira wrote:

Hi Jace,

Of my knowledge you need to use an advance query to achieve this. You can user this query:

SELECT
     L.[LessonName],
     STUFF(
         (SELECT ',' + {LessonStaff}.[StaffName]
          FROM  {LessonStaff}
          WHERE {LessonStaff}.[LessonName] = L.[LessonName]
          FOR XML PATH (''))
          , 1, 1, '')  AS Staff
FROM {Lesson} AS L
GROUP BY L.[LessonName]

Btw is always prefer to link tables by ID.

Regards,

Marcelo

Hi Marcelo,

But there is an assistant name too. Need to combine Teacher and Assistant name


Hi Jace

Is data in the same table? I mean, the first table you show in the question, is it a single entity?

Cheers

Eduardo Jauch



Eduardo Jauch wrote:

Hi Jace

Is data in the same table? I mean, the first table you show in the question, is it a single entity?

Cheers

Eduardo Jauch



Hi Eduardo,

Yes, the data is in the same table. 


Hum...

In that case, I think you can do it very easily, even with an aggregate. 

1. Create a calculated column and append both teacher and assistant. You can use IF to get the names only if they exist.

2. Group by lesson name AND this new field

And you will have it :)

Eduardo Jauch wrote:

Hum...

In that case, I think you can do it very easily, even with an aggregate. 

1. Create a calculated column and append both teacher and assistant. You can use IF to get the names only if they exist.

2. Group by lesson name AND this new field

And you will have it :)

Hi,

Yes i have tried this. But refer to the question that i have posted, i do not get what i have expected.


I am still facing same problem.


Regards

-SK-

Hi,

You can use this:

SyntaxEditor Code Snippet

SELECT
     L.[LessonName],
     LTRIM(STUFF(
         (SELECT ',' + {Lesson}.[TeacherName] + ',' + {Lesson}.[AssistantName]
          FROM  {Lesson}
          WHERE {Lesson}.[LessonName] = L.[LessonName]
          FOR XML PATH (''))
          , 1, 1, ''))  AS TeacherName
FROM {Lesson} AS L
GROUP BY L.[LessonName]

Keep in mind that you will some , that shouldn't be there because of empty columns. Use some SQL to remove them.

Regards,

Marcelo

Marcelo Ferreira wrote:

Hi,

You can use this:

SyntaxEditor Code Snippet

SELECT
     L.[LessonName],
     LTRIM(STUFF(
         (SELECT ',' + {Lesson}.[TeacherName] + ',' + {Lesson}.[AssistantName]
          FROM  {Lesson}
          WHERE {Lesson}.[LessonName] = L.[LessonName]
          FOR XML PATH (''))
          , 1, 1, ''))  AS TeacherName
FROM {Lesson} AS L
GROUP BY L.[LessonName]

Keep in mind that you will some , that shouldn't be there because of empty columns. Use some SQL to remove them.

Regards,

Marcelo

Hi Marcelo,

Thanks for the help. Sorry i am quite new to advanced sql. I got this error when i test the sql u given. Missing right parenthesis


Jace Jace wrote:

Eduardo Jauch wrote:

Hum...

In that case, I think you can do it very easily, even with an aggregate. 

1. Create a calculated column and append both teacher and assistant. You can use IF to get the names only if they exist.

2. Group by lesson name AND this new field

And you will have it :)

Hi,

Yes i have tried this. But refer to the question that i have posted, i do not get what i have expected.


Hi Jace,

I don't know what you are doing, but for sure is not the same thing I am doing and told you to do :)

https://eduardojauch.outsystemscloud.com/OneLessonMultipleTeachersPerLine/Home.aspx?_ts=636473712509231063

Cheers,
Eduardo Jauch


Hi,

Strange because I copied it from a test I made and is working. And beside u can see the query has the same left and right parenthesis

Can you post the query you are using in your code?

Regards,

Marcelo

Jace Jace wrote:

Hello IB,

I just want to display the trainer name together without any duplicate lesson name

Hi Jace,

Find (you just need to trim the commas in front and in the trail...):

Cookbook:


regards,

IB

People,

Lets not use SQL when it is not needed :)

SQL makes the code harder to understand and maintain. 

Cheers,
Eduardo Jauch

Hi everyone,

Is it possible to achieve it by using aggregates with gruopby ?


Regards

-SK-

Hi,

Its not. You cant group by LessonName and then aggregate the Teacher and Assistants names. Only Count is available for text fields.

You need to use one advance query.

Regards,

Marcelo

Marcelo Ferreira wrote:

Hi,

Strange because I copied it from a test I made and is working. And beside u can see the query has the same left and right parenthesis

Can you post the query you are using in your code?

Regards,

Marcelo

Yes quite strange. I am using the same as yours, just that i change the entity and column names to mine own. Do you mind to share your oml?


Shashi kant Shukla wrote:

Hi everyone,

Is it possible to achieve it by using aggregates with gruopby ?


Regards

-SK-

Yes, It is perfectly possible.

See the OML attached.

The link to the example is the same.

Cheers,
Eduardo Jauch


Marcelo Ferreira wrote:

Hi,

Its not. You cant group by LessonName and then aggregate the Teacher and Assistants names. Only Count is available for text fields.

You need to use one advance query.

Regards,

Marcelo

Sorry to disagree, Marcelo.
In the present case, it is perfectly possible to use group by, and have the result asked in the question.

See my example above.

Cheers,
Eduardo Jauch


Eduardo Jauch wrote:

Shashi kant Shukla wrote:

Hi everyone,

Is it possible to achieve it by using aggregates with gruopby ?


Regards

-SK-

Yes, It is perfectly possible.

See the OML attached.

The link to the example is the same.

Cheers,
Eduardo Jauch


Hello,

But your result display duplicate lesson name, it is possible to remove duplicate lesson name. Meaning that there are only distinct lesson name with all the teachers and assistant name. How about if there is possibility that there are more than one teacher name or assistant name?


Jace Jace wrote:

Eduardo Jauch wrote:

Shashi kant Shukla wrote:

Hi everyone,

Is it possible to achieve it by using aggregates with gruopby ?


Regards

-SK-

Yes, It is perfectly possible.

See the OML attached.

The link to the example is the same.

Cheers,
Eduardo Jauch


Hello,

But your result display duplicate lesson name, it is possible to remove duplicate lesson name. Meaning that there are only distinct lesson name with all the teachers and assistant name. How about if there is possibility that there are more than one teacher name or assistant name?


Hello Jace.

It seems that I misunderstood your requirement. :)
I saw your second table and thought it was what you "wanted". :)

In this case, Marcelo is right (Sorry Marcelo).

The only way to do the way you want is through SQL (as far as I know), and the method is the one provided by Marcelo.

Marcelo Ferreira wrote:

Hi,

Strange because I copied it from a test I made and is working. And beside u can see the query has the same left and right parenthesis

Can you post the query you are using in your code?

Regards,

Marcelo

This is my code for your information:


SyntaxEditor Code Snippet

SELECT LTRIM(STUFF((SELECT ',' + CS.[TeacherName] + ',' + CS.[AssistantName]
                  FROM  {Lesson} AS CS
                  WHERE CS.[LessonCode] = C.[LessonCode]
                  FOR XML PATH (''))
                  , 1, 1, '')) AS [StaffName]
FROM {Lesson} AS C


I have removed the lesson name, i just extracting the staff name


Hi Jace,

See this oml in attachment.

Best regards,

Marcelo

Hi,

Btw what you are missing on your query is the group by

Regards,

Marcelo

Marcelo Ferreira wrote:

Hi,

Btw what you are missing on your query is the group by

Regards,

Marcelo

Hi Marcelo,

Thanks for the oml. It is very strange. I using exactly the same as your query but still got the error of missing right parenthesis. I google and it mentioned that either it is missing a right parenthesis or there is some error inside the query enclosed.


Hi,

My oml works on your env? Are you using sql server?

Regards,

Marcelo

Jace Jace wrote:

Marcelo Ferreira wrote:

Hi,

Strange because I copied it from a test I made and is working. And beside u can see the query has the same left and right parenthesis

Can you post the query you are using in your code?

Regards,

Marcelo

This is my code for your information:


SyntaxEditor Code Snippet

SELECT LTRIM(STUFF((SELECT ',' + CS.[TeacherName] + ',' + CS.[AssistantName]
                  FROM  {Lesson} AS CS
                  WHERE CS.[LessonCode] = C.[LessonCode]
                  FOR XML PATH (''))
                  , 1, 1, '')) AS [StaffName]
FROM {Lesson} AS C


I have removed the lesson name, i just extracting the staff name


I tried this query in cloud (Dot Net Stack) and it works with this result, although there is still a duplicate

:

So, you can use my query above or use Marcelo's query with distinct clause.

Regards,

IB 

Marcelo Ferreira wrote:

Hi,

My oml works on your env? Are you using sql server?

Regards,

Marcelo

Yes, the oml you provided work on my environment. That the confusing part. haha. 


Anyway i have a different font color for STUFF. it is because it doesn't support it?

This is what i see from my side.


hi Jace,

Okay, so the SQL editor does not know that keywords. Check your environment:

- what stack, dot net or java?

- what sql server  version?

- ask your admin 

regards,

IB

I Box wrote:

hi Jace,

Okay, so the SQL editor does not know that keywords. Check your environment:

- what stack, dot net or java?

- what sql server  version?

- ask your admin 

regards,

IB

Hello,

Noted on this. May i know how do i check? I am using oracle. 


Hi Jace,

if you use Oracle then use this function, instead:

https://www.techonthenet.com/oracle/functions/listagg.php

regards,

IB

Hi Jace,

Since you said you didnt know much of SQL here is the query you should use. Dunno if it is completely right because I dont have a oracle environment to test it

SELECT {Lesson}.[LessonName],
       LISTAGG({Lesson}.[TeacherName] + ',' + {Lesson}.[AssistantName] + ',') WITHIN GROUP (ORDER BY {Lesson}.[Date]) "Teachers"
  FROM {Lesson}
  GROUP BY {Lesson}.[LessonName]
  ORDER BY {Lesson}.[LessonName];

Regards,

Marcelo

Marcelo Ferreira wrote:

Hi Jace,

Since you said you didnt know much of SQL here is the query you should use. Dunno if it is completely right because I dont have a oracle environment to test it

SELECT {Lesson}.[LessonName],
       LISTAGG({Lesson}.[TeacherName] + ',' + {Lesson}.[AssistantName] + ',') WITHIN GROUP (ORDER BY {Lesson}.[Date]) "Teachers"
  FROM {Lesson}
  GROUP BY {Lesson}.[LessonName]
  ORDER BY {Lesson}.[LessonName];

Regards,

Marcelo

Hi,

This is so cool. Thanks. But how can i remove duplicate values?? :(


select distinct {Lesson}.[LessonName] maybe.

Jace Jace wrote:

Marcelo Ferreira wrote:

Hi Jace,

Since you said you didnt know much of SQL here is the query you should use. Dunno if it is completely right because I dont have a oracle environment to test it

SELECT {Lesson}.[LessonName],
       LISTAGG({Lesson}.[TeacherName] + ',' + {Lesson}.[AssistantName] + ',') WITHIN GROUP (ORDER BY {Lesson}.[Date]) "Teachers"
  FROM {Lesson}
  GROUP BY {Lesson}.[LessonName]
  ORDER BY {Lesson}.[LessonName];

Regards,

Marcelo

Hi,

This is so cool. Thanks. But how can i remove duplicate values?? :(


I want to remove the duplicate teacher name and assistant name. But i try to put 


SELECT DISTINCT LISTAGG({Lesson}.[TeacherName] + ',' + {Lesson}.[AssistantName] + ',') WITHIN GROUP (ORDER BY {Lesson}.[Date]) "Teachers"
  FROM {Lesson}
  GROUP BY {Lesson}.[LessonName]
  ORDER BY {Lesson}.[LessonName];

 But it did not return distinct record


HI Jace:

This is the right query [tested on TOAD already]:

with the following results:

regards,

IB

HI Jace:

This is the right query [tested on TOAD already]: