Can a NamedQuery be embedded in another NamedQuery?

Ignition
1

I have a named query "P" which produces a list of people. I want another query "W" which will produce a list of tasks that are owned by anyone in the list produced by query P.

The P query is complicated enough that I do not want to replicate it within W, I just want to reference P like it was a database table (e.g. "select task from tasktable where owner in P").

Is this possible?

2

Can you create a View in the database?

1 Like
3

This in not possible with just the Designer.

Nothing to stop you from sending the list produced by P as a parameter to qeury W.

Honestly though, if you don't need query P without query W, then I would say creating a view in the database itself is probably the best approach, followed by creating a stored procedure in the database.

1 Like
4

I can't see how the "view" option will work. Maybe a limitation of my brain.

I did not see a parameter data type option what would take a list of integers (person ID numbers). I thought of that, but did not pursue.

Query P is useful by itself. My fallback will be to just embed it in W as a subquery. Just hate having to keep 2 blocks of code sync'ed up if (I mean WHEN) anything changes in the future.

Thanks for your input

5

Don't confuse Database View with Perspective View.

You could then do exactly what you're trying to do in W, where instead of a sub query, you just use the View.

6

Strictly speaking, this is not supported by generic JDBC, and therefore there is nothing in the named query system to accommodate them.

It so happens that at least one JDBC driver (PostgreSQL) will accept true java arrays (not lists) in single parameters, and Ignition's "Prep" queries will pass these correctly.

See this topic:

{ Perhaps we need a feature request for NQs to offer array parameters. }

1 Like
7

Not an Ignition View, a database View.

In SQL Server

CREATE VIEW [dbo].[TaskTable]
AS
SELECT        Column1, Column2, Column3
FROM            dbo.Task
INNER JOIN   dbo.User ON Task.UserId = User.UserId
GO

Then you create a named query on the TaskTable database view and treat it like it was a table.

8

Thanks, I did understand it was a DB view you were talking about. The query P is a recursive query that I send a group ID to and it gives me the people in that group or any subgroups. Putting that in a view definition is more than I can wrap my head around at the moment. More thought required on my part.

1 Like