Nested append subqueries

Daniel Marrujo shared this question 9 months ago
Answered

Hello Team,


I notice that when creating a second append subquery it actually refers to the main query and not the the last Subquery, is there a way we can do that?, this is the actual use case:


Lets Say we have an incident number:

INC001


And that incident has lots of work orders attached, for example:


Inc number Status Last Date Log
INC001 Open 1/25/2021 Info 1
INC001 Open 1/26/2021 Info 2
INC001 Open 1/27/2021 Info 3
INC001 Closed 1/28/2021 Info 4
INC001 Closed 1/29/2021 Info 5
INC001 Closed 1/30/2021 Info 6
INC001 Closed 1/31/2021 Info 7
INC001 Pending 2/1/2021 Info 8
INC001 Pending 2/2/2021 Info 9
INC001 Pending 2/3/2021 Info 10
INC001 Pending 2/4/2021 Info 11
INC001 Other 2/5/2021 Info 12
INC001 Other 2/6/2021 Info 13
INC001 Other 2/7/2021 Info 14
INC001 Other 2/8/2021 Info 15


First The output we want is to show only the latest record for each of the Status, something like this:


Inc number Status Last Date Log
INC001 Open 1/27/2021 Info 1
INC001 Closed 1/31/2021 Info 7
INC001 Pending 2/4/2021 Info 11
INC001 Other 2/8/2021 Info 15

At this point we are fine as this can be achieved with a simple append subquery using the Max date.


However we want to have a prompt filter for "Status" that if there is no match still shows the INC001, for example lets say this is the raw data:


Inc number Status Last Date Log
INC001 Open 1/25/2021 Info 1
INC001 Open 1/26/2021 Info 2
INC001 Open 1/27/2021 Info 3
INC001 Closed 1/28/2021 Info 4
INC001 Closed 1/29/2021 Info 5
INC001 Closed 1/30/2021 Info 6
INC001 Closed 1/31/2021 Info 7
INC001 Pending 2/1/2021 Info 8
INC001 Pending 2/2/2021 Info 9
INC001 Pending 2/3/2021 Info 10
INC001 Pending 2/4/2021 Info 11
INC001 Other 2/5/2021 Info 12
INC001 Other 2/6/2021 Info 13
INC001 Other 2/7/2021 Info 14
INC001 Other 2/8/2021 Info 15
INC002 Special 2/7/2021 Info 16
INC002 Special 2/8/2021 Info 17


And on the prompt filter we select "Special", the required output should be:


Inc number Status Last Date Log
INC001
INC002 Special 2/8/2021 Info 17

To still show INC001 and blanks because there is no match for "Special" however if we do this INC001 is Removed from the picture.


I think this could be achieved if we can do nested subqueries not refering to the main query but to another existing query.


Regards,

Daniel Marrujo

Comments (4)

photo
1

Hi Daniel,

Thanks for reaching out. There is no way to nest Append subqueries.

As you can see when you are configuring the subqueries, you are explicitly configuring the relationship between the Master query and the Subquery:

/0adc73fd9358f5b350214f4277265d2c

I suspect it likely that there would be another way to accomplish something similar to this via some other alternative possibility. The issue though, is that this would require Consulting assistance.

Ultimately, I think what we have to consider is what this SQL query would look like, keeping in mind all reports are built based on SQL. Do you have an example of a SQL query that would return two rows, one where there's only one value in one column, while it excludes the other columns? I'm struggling to find an example of a SQL query that could give you a result that looks like this:

Inc numberStatusLast DateLog

INC001

INC002Special2/8/2021Info 17

I've so far been unable to draft a query that returns a result that looks like this.

Thanks,

Mike

photo
1

Hi Daniel,

I just wanted to check in and see how things are going with this.

Regards,

Mike

photo
1

Thanks for the info feel free to conclude this post, Thanks =)

photo
1

Hi Daniel,

Great! Thanks for your response. Will do.

Please don't hesitate to reach out with any other questions or concerns.

Regards,

Mike