Referencing an Advanced Query within another Advanced Query

Referencing an Advanced Query within another Advanced Query

  

Has anyone been able to successfully bring in the results from the first Advanced Query into a second Advanced Query? I have data that I need to sum based on a lot of unique criteria and would like to do a vlookup like activity from one to the other in order to display the data correctly. Any suggestions?

Hello Katerina,

As far as I know, there is no way to "easily" include the results of a query inside other, other than create a piece of SQL with it (like a list to use in a IN clause) and pass through query input parameters.

Very inefficient.

I would say that the best is to include the first query inside the second, directly.
OR
Process the information of both after the queries are executed, through LOGIC.

Cheers.

Hi Eduardo,

I used input parameter based on the output from the first query to feed the second but only the first value from my input is coming through instead of a VLOOKUP like action. Could I use a ForEach or other approach?

Will need to look into an IN Clause. 

If you need a IN clause, you can build a string that will be the list and pass as query parameter.
Remember to use the Expand Inline in the parameter, so that the text is understood as a part of the string.

Cheers.

Hi Katerina,

The best approach would be doing everything in the same SQL. A query inside a foreach should never be the solution because of performance. Can you share with us the data model and what kind of result you want to get?

Regards,

Marcelo

Hi Marcelo,

What I understood is that Katerina wants to use a list of results from a previous query inside an IN clause.
While I also think that a single query is better, if for some reason she needs them separate, and maybe the query can be slow, running it again can be a bad idea.

In any case, she can build a string with the results from one query to use in another. The foreach would be to construct the string.

Cheers.

Here is a snippet of the query, the Orange section, I have a new select statement summing the quantity but I keep getting a value adding up ALL the books instead of by Book, Subject and Genre Ids. The Book, Subject and Genre are inputs based on a previous SQL query that uses those outputs. I would rather have 1 query but I am at a loss.


CODE REMOVED INTENTIONALLY

Katerina, 

What is your data model, and what do you want to retrieve with your query?

Cheers.

The model is very complex but I am trying to determine the total count of Entities.Consumed based on which Books, Genres and Subjects they all under.

I would include more but it is proprietary information and this is the most I can divulge. 

Apologies for being vague.

No problem.

You want the total count of ENTRY for each BOOK, GENRE and SUBJECT?
So, you need counters for each book, than counters for each GENRE and than counters for each SUBJECT?

Is that?

If so, you need three separated queries.

Or are you trying to do the counting based on a FILTER, like the user can select a book, and or a subject, and or a genre?

Eduardo Jauch wrote:

Or are you trying to do the counting based on a FILTER, like the user can select a book, and or a subject, and or a genre?

This is it, count based on the FILTER, yes!


Katerina Perry wrote:

Eduardo Jauch wrote:

Or are you trying to do the counting based on a FILTER, like the user can select a book, and or a subject, and or a genre?

This is it, count based on the FILTER, yes!


Ok. Just to be sure, what is the difference between Entity.Consumed and Book.Quantity?


In any case, you are receiving a filter:

@BookId, @GenreId and @SubjectId. All of them can be null, I assume.

I would use the following filter:

(@BookId = 0 OR Book.Id = @BookId)
AND
(@GenreId = 0 OR Book.GenreId = @GenreId)
AND
(@SubjectId = 0 OR Book.SubjectId = @SubjectId)

And a simple query would be:

SELECT Count (*)
FROM {Entity}
INNER JOIN {Book} ON {Book}.[Id] = {Entity}.[BookId]
WHERE
  (@BookId = 0 OR Book.Id = @BookId)
  AND
  (@GenreId = 0 OR Book.GenreId = @GenreId)
  AND
  (@SubjectId = 0 OR Book.SubjectId = @SubjectId)

Of course you can add more filters as needed.

But the query will depend if you need just to count, or if you need to sum a quantity in entity, etc.

Eduardo Jauch wrote:

Katerina Perry wrote:

Eduardo Jauch wrote:

Or are you trying to do the counting based on a FILTER, like the user can select a book, and or a subject, and or a genre?

This is it, count based on the FILTER, yes!


Ok. Just to be sure, what is the difference between Entity.Consumed and Book.Quantity?


There can be x number of books based on the other criteria, consider Aristotle wrote about Physics, Philosophy and Psychology and let's say 5 books on Physics, 3 on Philosophy and 2 on Psychology, based on parameters the result could be 5, 3 or 2 or if I added them all up it would be 10.

Does this make any sense?


Eduardo Jauch wrote:

In any case, you are receiving a filter:

@BookId, @GenreId and @SubjectId. All of them can be null, I assume.

I would use the following filter:

(@BookId = 0 OR Book.Id = @BookId)
AND
(@GenreId = 0 OR Book.GenreId = @GenreId)
AND
(@SubjectId = 0 OR Book.SubjectId = @SubjectId)

And a simple query would be:

SELECT Count (*)
FROM {Entity}
INNER JOIN {Book} ON {Book}.[Id] = {Entity}.[BookId]
WHERE
  (@BookId = 0 OR Book.Id = @BookId)
  AND
  (@GenreId = 0 OR Book.GenreId = @GenreId)
  AND
  (@SubjectId = 0 OR Book.SubjectId = @SubjectId)

Of course you can add more filters as needed.

But the query will depend if you need just to count, or if you need to sum a quantity in entity, etc.

I like this approach, if I swap out COUNT for SUM can I have it in the select statement inside the existing query?


Yes.

:)

Hi,


Please provide the sql or data model,i will create query for u.

Yes we can pass multiple values to outer query for filtering.


Thanks!