AnsweredAssumed Answered

NSQL: how to declare variables?

Question asked by Alex_Feldstein on Aug 5, 2010
Latest reply on Aug 10, 2010 by Dave
As a Clarity/Portlet/NSQL n00b I want to learn.

I have downloaded the CA PDF document titled Clarity Studio Developers Guide (m001081e). I have read the NSQL section and have been successful in creating my first semi-complex NSQL query that parsed fine into SQL Server 2005.

Now I am tasked to create my first portlet to display a grid with a summary query then drill down into a detailed grid and then drill some more.

Before I get into the displaying data part of the portlet, I have to learn NSQL. According to the document mentioned above (the only reference I could find),:

"Only SELECT statements that specify which rows and columns to fetch from one or more tables are permitted. NSQL statements fail with an error message if a statement does not start with SELECT."

The query I need is complex. It is actually a series of queries, declaring variables to be filled by queries and used in subsequent queries. A very simplified excerpt in T-SQL follows:

Declare @ProjectFinishDate datetime
Declare @TotalBillableProjects as float
set @ProjectFinishDate = '06-30-2010' /* End of Q1-2010 */

@TotalBillableProjects = count(p.Name)

srm_projects p

inner join prj_projects pr ON p.ID=pr.prID

inner join odf_ca_project po ON p.ID=po.ID

p.is_active = 1 and po.pr_project_billable = 1 and p.is_template = 0 and p.is_program = 0

.. then the next query in the set uses the filled in variable @TotalBillableProjects as input and so on with many more queries and more declared variables (not shown in this example).

Q: How do I declare and save variables in NSQL so I can make complex queries dependent on results of previous query?
This is for temporary variables declared internal to the query, besides the user-input vars (which is another issue on how to read user input), like the @ProjectFinishDate shown above.

I guess one easy solution would be to have NSQL execute a SQL Server Stored Procedure so the NSQL used is minimal, and the complex query logic resides in SQL Server (as a SP). Is this possible?