Log in to AnswerLog in to Follow
divya v
349Views
12Comments
SQL query records showing in table
Question

Hi,

I have one table called Test2. Please find the below screenshot.

In this table, one column called Status. It will contain possible values(1 or 2).


Status    1 means  success

Status     2 means Failure

 In the front end,  I have to show batch_fk and success and failure count in frond end table.


So, I have written a SQL query to fetch the success and failure count from status column of Test2 table

SQL1:

select {Test2}.[batch_fk],count(*) from {Test2} where {Test2}.[status]=1 group by {Test2}.[batch_fk];

This above query is for taking success count

Likewise, I have written a SQL query to fetch the error count as well. If the status is 2 means error

SQL2:

select {Test2}.[batch_fk],count(*) from {Test2} where {Test2}.[status]=2 group by {Test2}.[batch_fk];


In the frond end, I am able to show the batch_fk and success count. But I am not able to show the error count for each batch_fk since the query is different for error.

I have given the source for the table is SQL1.List.

How can I show the error count also in the table?


Regards,

Divya

0
0
on 2019-05-28

Hi Divya,

Do you have a specific reason for separating those SQL queries? Because you can group by status also like following;

select {Test2}.[batch_fk],{Test2}.[status],count(*) from {Test2} 
group by {Test2}.[batch_fk], {Test2}.[status];

Hope it helps,

With best regards,
F.Karatay

1
0
on 2019-05-28

Ferhat Karatay wrote:

Hi Divya,

Do you have a specific reason for separating those SQL queries? Because you can group by status also like following;

select {Test2}.[batch_fk],{Test2}.[status],count(*) from {Test2} 
group by {Test2}.[batch_fk], {Test2}.[status];

Hope it helps,

With best regards,
F.Karatay


Hi Ferhat,

Thanks for your reply. I want to display the records of SQL query in a table format in front end. Please find the below screenshot. In this format, I have to show the success count and error count for each batch_fk. As per your statement, I can do but there are two columns(Success and error) in a web screen(front end table). But query returns count which contain both success and error count. Using this count, how can I show both success and error count for each batch_fk?


Regards,

Divya

1
0
on 2019-05-28

divya v wrote:

Ferhat Karatay wrote:

Hi Divya,

Do you have a specific reason for separating those SQL queries? Because you can group by status also like following;

select {Test2}.[batch_fk],{Test2}.[status],count(*) from {Test2} 
group by {Test2}.[batch_fk], {Test2}.[status];

Hope it helps,

With best regards,
F.Karatay


Hi Ferhat,

Thanks for your reply. I want to display the records of SQL query in a table format in front end. Please find the below screenshot. In this format, I have to show the success count and error count for each batch_fk. As per your statement, I can do but there are two columns(Success and error) in a web screen(front end table). But query returns count which contain both success and error count. Using this count, how can I show both success and error count for each batch_fk?


Regards,

Divya

Hi Divya,

As I understand, you'd like to create a PIVOT table. I suggest you make a search about PIVOT in SQL.
Here is an example;


SyntaxEditor Code Snippet

SELECT * FROM (SELECT {Records}.[BatchFK], {Records}.[Status] FROM {Records}) SRC
PIVOT (count(*) FOR Status in ('Success' AS Success, 'Failure' as Fail)) PIV ORDER BY 1 ASC


Hope it helps.

With best regards,
F.Karatay

0
0
on 2019-05-29

You could try a SQL query like this:

SELECT {Test2}.[batch_fk]
, COUNT(NULLIF(2, {Test2}.[status])) AS succesCount
, COUNT(NULLIF(1, {Test2}.[status])) AS errorCount
FROM {Test2}
GROUP BY {Test2}.[batch_fk];


This query takes advantage of the fact that the COUNT function does not count NULL values. The function NULLIF returns NULL if the two values are the same and the first value if they are not.

0
0
on 2019-05-28

Timothy de Vries wrote:

You could try a SQL query like this:

SELECT {Test2}.[batch_fk]
, COUNT(NULLIF(2, {Test2}.[status])) AS succesCount
, COUNT(NULLIF(1, {Test2}.[status])) AS errorCount
FROM {Test2}
GROUP BY {Test2}.[batch_fk];


This query takes advantage of the fact that the COUNT function does not count NULL values. The function NULLIF returns NULL if the two values are the same and the first value if they are not.


For many reasons, a better option would be to make use of the standard aggregate within outsystems instead of using a custom SQL query. Using the standard aggregate, you will have to do this:

1. Create an aggregate with Test2 as a source (drag the test2 table into an action)

2. Click the "New attribute" button, rename the calculated attribute to "Succes". Then change the formula to "Test2.status = 1" (open the editor by double clicking the fx icon or the blue icon on the top right and selecting "Edit formula")

3. Do the same as 2, but now name it Error and use the formula "Test2.status = 2"

4. Create a group by on the "batch_fk" field (press the blue icon on the top right of the column and click "Group by"

5. Create a counnt on the "Succes" attribute you created in step 2 (press the blue icon on the top right of the column and click "Count") and name it SuccesCount

6. Do the same as 5, but now for the "Error" attribute and name it ErrorCount


Picture of the end result:

1
0
on 2019-05-28

Timothy de Vries wrote:

You could try a SQL query like this:

SELECT {Test2}.[batch_fk]
, COUNT(NULLIF(2, {Test2}.[status])) AS succesCount
, COUNT(NULLIF(1, {Test2}.[status])) AS errorCount
FROM {Test2}
GROUP BY {Test2}.[batch_fk];


This query takes advantage of the fact that the COUNT function does not count NULL values. The function NULLIF returns NULL if the two values are the same and the first value if they are not.


Hi Timothy,

Your query is working properly. Thanks a lot. But, Now I should use aggregate instead of SQL query because of some reason. So I created aggregate where I added a new attribute called success count and gave this formula NULLIF(2, {Test2}.[status]). Unfortunately, it is showing the error saying that NULLIF is not a function.

Could you please help me.


Regards,

Divya

0
0
on 2019-05-29

Timothy de Vries wrote:

Timothy de Vries wrote:

You could try a SQL query like this:

SELECT {Test2}.[batch_fk]
, COUNT(NULLIF(2, {Test2}.[status])) AS succesCount
, COUNT(NULLIF(1, {Test2}.[status])) AS errorCount
FROM {Test2}
GROUP BY {Test2}.[batch_fk];


This query takes advantage of the fact that the COUNT function does not count NULL values. The function NULLIF returns NULL if the two values are the same and the first value if they are not.


For many reasons, a better option would be to make use of the standard aggregate within outsystems instead of using a custom SQL query. Using the standard aggregate, you will have to do this:

1. Create an aggregate with Test2 as a source (drag the test2 table into an action)

2. Click the "New attribute" button, rename the calculated attribute to "Succes". Then change the formula to "Test2.status = 1" (open the editor by double clicking the fx icon or the blue icon on the top right and selecting "Edit formula")

3. Do the same as 2, but now name it Error and use the formula "Test2.status = 2"

4. Create a group by on the "batch_fk" field (press the blue icon on the top right of the column and click "Group by"

5. Create a counnt on the "Succes" attribute you created in step 2 (press the blue icon on the top right of the column and click "Count") and name it SuccesCount

6. Do the same as 5, but now for the "Error" attribute and name it ErrorCount


Picture of the end result:

Hi,

As you said, I have tried this also. But here what is happening is, If this condition satisfies(Test2.Status = 1), it returns true otherwise false. So, count function counts the records even both true and false.

let's assume Success(Test2.Status=1)

true

false

false

false

Error(Test2.Status=2)

false

true

true

true

So, now both success and error count is 4. But, this is not the case.

Bou actual count is 

success count  1

error count  3


Regards,

Divya

0
0
on 2019-05-30

You can't use SQL formula's in an aggregate for as far as I know. 

Timothy already gave an extensive example for creating the aggregate, that should help you I think.

0
0
on 2019-05-29
Eduardo Jauch
mvp_badge
MVP

Hi Divya,

Use the same approach, but instead of TRUE / FALSE, put 1 / 0, and instead of count, use SUM.

Cheers.

0
0
on 2019-05-30

Eduardo Jauch wrote:

Hi Divya,

Use the same approach, but instead of TRUE / FALSE, put 1 / 0, and instead of count, use SUM.

Cheers.


Hi Eduardo,

I am not manually adding this value(true and false). If this condition(Test2.Status=1) satisfies, it returns true otherwise false.


Regards,

Divya

0
0
on 2019-05-30

Hi Divya,

What Eduardo suggests is not to manually set 1 or 0, but with a formula. You need to change the values of the calculated fields from 'Test2.Status=1' to 'If(Test2.Status = 1, 1, 0)' for the Success column and 'Test.Status=2' to 'If(Test2.Status = 2, 1, 0)' for the Fail-column.

As he also stated, you need to change the count to a SUM.

Regards,
Lennart

1
0
on 2019-05-30
Eduardo Jauch
mvp_badge
MVP

Thanks @Lennart. That's exactly what I meant :) 

0
0
on 2019-05-30
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.