database change

building semantic layer
1

Hi All,
I have created a few reports on WebI (Oracle 8, WebI 2.5) using a development database. We are about to switch to the production database on a different box (same tables, etc). Can I continue using those reports I’ve already created on WebI? If so, what steps do I need to follow?

Thanks,
Sonya

Listserv Archives (BOB member since 2002-06-25)

2

Hello,
Backend database for my client could be either SQL Server or Oracle. Quite a few objects in the universe need to contain database specific SQL like cast (SQL), decode (Oracle).
I am not sure how and where to add an if then else (if possible) so that the following condition could work for the objects. if database = sql
do xyz
else if database = oracle
do abc
endif

Thanks,
Anuradha

Listserv Archives (BOB member since 2002-06-25)

3

Chris@LEGALWEBNET.COM

Anuradha :

I havent tried what your suggesting however it seems if you attempted to do what your suggesting you would most likely bring your reporting system to a crawl by the additional load based in each object.

You also need to consider that SQL really doesent support outer joins in the same way oracle will, views (if used) etc…

i support dual universes for a client… i.e. one built for MSSQL, the other for Oracle (which is a pain :slight_smile: ) but if you do try your route and it works without a significant loss… let me know :slight_smile:

Chris Wilson-Marquis

Mosaic Consulting LLC
http://www.LegalWebNet.com

=========================================================================== ==

Hello,
Backend database for my client could be either SQL Server or Oracle. Quite a few objects in the universe need to contain database specific SQL like cast (SQL), decode (Oracle).
I am not sure how and where to add an if then else (if possible) so that the following condition could work for the objects. if database = sql
do xyz
else if database = oracle
do abc
endif

Thanks,
Anuradha

Listserv Archives (BOB member since 2002-06-25)

4

In a message dated 10/23/01 3:51:50 PM Mountain Standard Time, dn_anuradha@YAHOO.COM writes:

Backend database for my client could be either SQL Server or Oracle. Quite a few objects in the universe need to contain database specific SQL like cast (SQL), decode (Oracle).
I am not sure how and where to add an if then else (if possible) so that the following condition could work for the objects. if database = sql
do xyz
else if database = oracle
do abc

Ummm, don’t think this is going to work. And I would not really want to try. Building two universes is a better solution; start with a generic version, then add on your database specific requirements later on. It will double your maintenance but will be a cleaner solution.

If you are determined to go the single universe route, best suggestion I can give you is to create user defined functions for Oracle that mimic SQL Server functions, or the other way around. In other words, write a “cast” function as a stored function in Oracle, then use cast() in your universe. It will either call the native function for SQL Server, or your custom function in Oracle.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

5

— DRathbun@AOL.COM wrote:

If you are determined to go the single universe route, best suggestion I can
give you is to create user defined functions for Oracle that mimic SQL Server
functions, or the other way around. In other words, write a “cast” function
as a stored function in Oracle, then use cast() in your universe. It will
either call the native function for SQL Server, or your custom function in
Oracle.

Me:
Now there seems to be two issues.

  1. Oracle would accept a function name cast1 but not cast. I wrote function cast to mimic typecasting and it would not go through. 2. Iam not able to execute a statement like select xxxcast() in SQL Server where xxxcast would be my user defined function.
    If issue2 is solved, then I could name the functions xxxcast and use it against both SQL and Oracle Database. Moreover, using the universe for other database will be independent of the database since I need to write the stored functions on the database side.
    I will be glad if you could throw some light on this (sql server user defined functions) If nothing works out, then I would start maintaining one version for each database. Thanks,
    Anuradha

__________________________________________________ Do You Yahoo!?
Find a job, post your resume.
http://careers.yahoo.com

Listserv Archives (BOB member since 2002-06-25)

6

CAST is a reserved word in Oracle, so you can not have your own function called “cast”. It is used in converting a datatype or collection in Oracle. Look at Oracle SQL Reference manual.

You may need to come up with your own naming convention for your stored functions and map your universe to them accordingly. Sorry, I’m not sure of the sql syntax for calling a stored function in SQL server.

Listserv Archives (BOB member since 2002-06-25)