Using a prompt to choose which SQL statement to send to the DB?

[seballen]

Hello,

I am trying to develop a report that will be shared in multiple environments (some MySQL and some MSSQL). I have a very specific need that extracts parts of a string, and requires different functions for the two databases. This particular query is SQL sent directly to the database and I don't have access to the framework manager to do anything with the package.

Has anyone ever been able to use a prompt (e.g. Database Type: MySQL or MSSQL) to choose which SQL to send? Ideally, I don't want to maintain two separate reports.

Thanks,
Sasha

[wyconian]

Hi

I have absolutely no idea how you can specify which SQL gets sent to the DB but I may have a work around for you.

If you have all queries and a page for each query you can use a prompt and render varibale so the user can choose which page to see.  All the queries will get run but only the data on the rendered page will be displayed to the user.

Does that help?

[Lynn]

Is there a local Cognos function you can use? I think Cognos would generate the appropriate SQL to the database so you don't have to code for it explicitly.

That is the idea around making things portable. If you code with database syntax you'll have to adjust whereas using Cognos functions avoids this issue.

If you share what function you are talking about people might have more specific responses.

[CognosPaul]

Lynn's advice is spot-on. Cognos functions is the only way to go. In the majority of cases, Cognos will translate the functions as needed for each database. Unfortunately Cognos doesn't handle dates in MySQL as well as it should, so building shared reports may be difficult.

Before I can start giving advice, I'll need some information as to which direction to advise.

1. Do you have any control over the database?
  1A. Can you modify existing tables?
  1B. Can you add new tables, or use any existing param type tables?
  1C. Can you add any UDFs to the databases?
2. Can you modify the Framework?