"IF THEN ELSE" SQL COMMAND

[APB1981]

Hello,

CR: XI
DB: MSSQL 2000 & ORA 9.2
WIN: XP SP2

My goal is to have one report that can run on both MSSQL server databases and also on Oracle. Is it a possibility to configure an SQL command to show two different SELECT statements based on a parameter.

if param = 'ORA'

then

.......

else if param = 'MS'

then

.......


I have been told it is possible to design one single report that will work on both db platfroms but I am yet to find a way. Any ideas will be greatly appreciated.

My databases have the exact same table and field structure. It is the just the joining in the SQL that is unique.

Thanks.

Adam

APB

 

[lbass]

You could try creating the string parameter within the command, and then setting up the SQL query as a union all where you use the query for one datasource followed by "union all" and then the query for the other datasource (following the rules for a union all statement). Then in the where clause for each query, use a clause like:

where '{?datasource}' = 'ORA' //or 'MS' for the other

-LB

 

[APB1981]

Hello Ibass,

Wouldn't that try and connect to both sources at the same time?

At runtime it would only be one or the other - depending on the Customer's config.

Example:

SELECT Field 1, Field 2 etc
FROM TableA
WHERE '{?datasource}' = 'ORA'
UNION ALL
SELECT Field 1, Field 2 etc
FROM TableA
WHERE '{?datasource}' = 'MS'

Thanks.

APB

 

[Turkbear]

Hi,
One method might be to add 2 subreports to a master that has just Text fields and a parameter..add 2 Detail sections and insert the Oracle based report in 1 and the MSSql one in the other..
Use the parameter in the main report to determine which section to suppress resulting in only one version being run.
Never tried it, but it should work, I think..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

No, because only one parameter option would be selected, so only one of the queries would run. I'm assuming the owner would be included in the query and that would be different per datasource.

Of course, before selecting the parameter, you would have to choose the corresponding driver for the report to run properly.

If there's only one table per datasource, I wonder whether you could write one query and then just use "set location" to establish which datasource to use. I'm not sure how that works with commands.

-LB

 

[APB1981]

Hello Ibass,

There would be many tables per data source. I like the idea of the Subreports - but I am not sure about the performance.


APB

 

[lbass]

Why not try my earlier suggestion with one table per half of the union query? I can't really test it, but it seems like it would work.

I'm not sure where the idea of subreports came up or how that would address the issue.

-LB

 

[APB1981]

I have tested the sub report suggestion and it works fine. However, this method prevents me from having a report with subreports as you can not have subreports within subreports.

Managing this system would take the exact same amount of time because I am still managing 2 reports - but they are just bundled in one report now. My goal is to become twice as effective...and this means only having one report to update and develop.

APB

 

[lbass]

And did you try my suggestion?

-LB

 

[lbass]

Okay, I tried my earlier suggestion--which I should have done earlier! Doesn't work, since the command is created for a specific datasource. Sorry about that.

Turkbear's solution should work or you could consider writing the report using the Crystal GUI and then you should be able to just use set location depending upon the datasource.

-LB