How to dynamically change ROWSPEC

Question

Question

Lyle McDaniel · Aug 30, 2018

Hello,

When working with class queries you have to define the ROWSPEC at the beginning (See below).

Instead of using Year1 or Year2 as the column name, how can I use the passed in fiscal year (xFiscalYear)

so Instead of using "Year1" as the column name I can call it something like: "Jul"_xFiscalYear

Query GLReportYearToYearTrend(xFiscalYear As %String) As %Query(CONTAINID = 0, ROWSPEC = "GLCode:%String,Description:%String,Year1:%String,Year2:%String,JulChange:%String") [ SqlProc ]
{
}

Discussion (10)1

Log in or sign up to continue

Benjamin De Boe · Sep 5, 2018

The ROWSPEC itself is indeed static, but depending on how you plan to use/expose this, you might generate/write a SELECT statement that does the renaming:

SELECT GLCode, Description, Year1 AS Jul2017, Year2 AS Jul2018 
FROM MyPackage.MyClass_GLReportYearToYearTrend(2017)

0 0

Joel Solon · Sep 5, 2018

This is not quite an answer to your question. Just some clarifications:

Your example is a %Query class query, where you write ObjectScript code for the query rather than using SQL. I haven't written one of those for a while. I'm assuming that ROWSPEC and CONTAINID are required for these types of class queries, but I'm not 100% sure.
A "newer" way to write a custom ObjectScript-based class query, using the class %SQL.CustomResultSet, doesn't require ROWSPEC either. Sample.CustomResultSet is an example of this technique.
For a %SQLQuery class query, written using SQL, ROWSPEC and CONTAINID have not been required for a loooong time. They are dynamically created from the query itself.

0 0

Benjamin De Boe · Sep 7, 2018

I just realized you're only on Caché 2012, which doesn't support table-valued functions, in which you can just SELECT from a function rather than having to use CALL, sorry.

On the other hand, I'd expect a BI tool like Logi to be capable of providing exactly the sort of UI-side labelling of columns, if not drive the entire YoY calculation. Not that I want to fend off the question, but if there's a full-fledged BI tool sitting on top of these results anyhow, let's make sure to use its full set of fledges :-)

0 0

score 0 · Answer 1 · 2018-09-05T10:48:46-04:00

Do you want to change ROWSPEC depending on passed argument?

ROWSPEC can be changed on compilation only and it's rather static. At least xDBC clients depend on declared schema/query info and so it cannot be changed dynamically.

score 0 · Answer 2 · 2018-09-05T10:56:19-04:00

Yes, I would like to change Year1 and Year2 from the example to "Jul"_xFiscalYear and "Jul"_xFiscalYear+1

If this is not doable, then maybe a different kind of class query?

score 0 · Answer 3 · 2018-09-05T18:30:43-04:00

Thank you.

We are using an order version of Cache (2012) so I am not sure this newer way to write objectScript-based class queries is available. We are planning to move to 2017 sometime this year.

score 0 · Answer 4 · 2018-09-06T03:20:38-04:00

I'm not sure %SQL.CustomResultSet is going to be a true solution here, as it also requires you to statically define properties that represent the columns being returned upfront, while Lyle would like to be able to let those column names depend on a runtime argument. You might be able to just do this at runtime and have dynamic dispatch take care of things, but that won't help the column metadata get set up.

score 1 · Answer 5 · 2018-09-07T04:47:45-04:00

It is possible. You will need to override the %Get and %GetSerializedMetadata method of %SQL.CustomResultSet.

It gets a bit messy but works fine.

score 0 · Answer 6 · 2018-09-06T03:22:25-04:00

Eduard Lebedyuk · Sep 6, 2018

What's the use case by the way?

0 0

score 0 · Answer 7 · 2018-09-06T10:56:47-04:00

Basically we are using the class queries as stored procedures to be called by Logi reports. All the developer has to do is perform a call method within the reports and get back a record set of data for the report.

Example:

Call SAT.ReportQueries_GLReportYearToYearTrend('2017') From SAT.ReportQueries

If there is a better way of doing this that would be great but this was the only way I knew how to do it at the time.

I tried Benjamin's suggestion (Select within a Select) but it doesn't work with a call Statement.