CODEWITHSUNDEEP
sqldb2cognosdb2-400
VAI Jason
VAI Jason

Reputation: 544

Cognos 11.1.7 Framework manager change the table for the query subject, type data

Using Cognos 11.1.7 and the database is DB2 on IBMi DQM

Trying to figure out how to get Framework manager to change SQL definition on the fly, based on a value in the database

This idea is based on a similar concept with parameter maps. You can change the SQL definition, however in the example below, it is based on a session parm. This example is great for multi language as it changes the from statement of the SQL definition on the fly, like this:

  Select * from  [LIVE].TABLE_#$Parameter_map{key}#

It's also possible to do this by a parm passed when a report is run, but the user would not know what to pass. So I tried something different...

Why parameter map won't work This article explains a parameter map would not work because the value needs to come in before any queries are sent to any data sources https://www.ibm.com/support/pages/unable-get-parameter-map-accept-query-item-its-input-parameter

However, with SQL, I think it can. Here is what I have tried A nested SQL statement where the sql inside the parenthesis if it resolved as a subselect would then complete the from statement by changing it to the correct table, like TABLE_A.

select * from (select X_VALUE from TABLE_X where X_CATEGORY = 'SALES')

For this example, TABLE_X has two columns -- X_CATEGORY X_VALUE

X_CATEGORY = SALES

X_VALUE = TABLE_A

so select X_VALUE from TABLE_X where X_CATEGORY = 'SALES'

will return the value of 'TABLE_A'

btw, a never ending program toggles the value every 10 minutes TABLE_A or TABLE_B as these tables are constantly being updated with pre-aggregated data

Unfortunately, in SQL (DB2 on IBMi), this returns X_VALUE instead of TABLE_A. Maybe there is another way to accomplish this in SQL on DB2 for IBMi? I am probably just running into a syntax issue

With SQL - How can you change the table in the from statement based on another table?

Currently I have a work around, using two sets of query subjects with the sql definitions hard coded. Stitching them with a full outer join coalesce to common tables (like company, location, date, etc).

At the report level, I render the content based on TABLE_X

This works, however it is not user friendly and more difficult to maintain

Upvotes: 0

Views: 923

Answers (2)

Daniel Wagemann
Daniel Wagemann

Reputation: 851

You might be able to use the QueryValue macro function to dynamically build up the FROM clause. The example below will change the FROM claus to GOSALES. Camping Equipment. (This will fail, given its not an actual table_name) In your case you would replace #dq(queryValue ('[Sales (query)].[Products].[Product line]'))# with the database column which contains the table_name to be used.

SELECT
    "PRODUCT_LINE"."PRODUCT_LINE_CODE" AS "Product_line_code", 
   "PRODUCT_LINE"."PRODUCT_LINE_EN" AS "Product_line"
FROM
    "GOSALES".#dq(queryValue ('[Sales (query)].[Products].[Product line]'))# "PRODUCT_LINE"

Upvotes: 0

C'est Moi
C'est Moi

Reputation: 326

This is my understanding of the situation. One problem I'm having is that I've needed to guess a bit.

You have two data bases, each with the same structure of metadata. You update the dbs continually.

You have a table which keeps the status of both DBs and there is {something} which you can use as the indicator of the most recently update DB.

It isn't clear how this is done or how many records are in this table.

You might want to try something along these lines. I have not tried it, so the details involve a considerable degree of hand-waving.

You can use parameter map macros in the data source connection properties in your FM model.

You would need to import the table which you are using to track the DB status. You would create a filter in the query subject to find something like the maximum (if you are using time stamps) or filter for the value in the relevant column of the table which tells you which DB is current. This would mean that only one row, representing the current db, would be returned.

You would create a parameter map which would pick up whatever value is returned for the relevant column in the query subject. Put that macro into the appropriate property of the data source connection.

Key          Value
Sales        DB1's name
AnotherThing DB2's name

Upvotes: 0

Related Questions