Re: Will the following code run when connected to Oracle?

Comments embedded.

kgoff_at_worldnet.att.net wrote in message news:<14Eb9.1427$jG2.96165_at_bgtnsc05-news.ops.worldnet.att.net>...
> Richard,
>
> While I appreciate your response, you really didn't answer my question. Let
> me try this again.

Richard really did answer your question. He said you don't wan't to create temporary tables 'on the fly' in Oracle. You don't.  

> We have a vertical market app, written in .NET. We sell the app to
> manufacturers. Most use SQL Server, though we are running into some
> potential customers who use Oracle. Our app must support both databases.
>
> We have a reporting module, where an end-user can run many different reports
> based on certain accounts/products. The user might pick 1 account and 10
> products, he might pick 20 accounts and 20 products....or maybe 50 accounts
> and 2000 products. The software must query the invoice tables in the
> back-end database against what the user selected. There may be dozens of
> users doing the same thing at the same time.
>
> So the application needs to collect the user selections for accounts/items,
> build temporary tables in the back-end database that represent the list of
> accts/items that the user selected, and then run a SQL statement that joins
> the back-end invoice table with the account/product temp tables that, again,
> represent what the user selected.

In SQL Server this may be the case. In Oracle it is unnecessary.

> The code that I posted represents a stripped down version of what we need to
> do. It isn't 'SQL Server' logic...it represents the only way that I know to
> address this reporting requirements. Much of our reporting requirements
> involve things that are variable at run-time. So my question is...will this
> code run without modification when connected to SQL Server, or is the syntax
> for creating temp tables a bit different?

It is SQL Server logic. It needs to be done because SQL Server does not support read consistency, multi versioning and concurrency out of the box like Oracle does.

The code will not run in Oracle as '#' is not a valid character in this context. Removing that should allow you to create and reference regular tables. You should look into the syntax for Global temporary tables to do something similar in Oracle. However it is unnecessary, a waste of time, code and resources and will not scale as well as if you just didn't do it at all.

> If you know of a better way to address this reporting requirement, I'd be
> very interested to know what it is.

Don't create temporary tables for the queries. Throw all that code away.

The alternative is to tweak the syntax of of your SQL Server application and then blame the database when your customer complains that for the performance she's getting she might as well have saved some money and bought SQL Server...

Martin Received on Fri Aug 30 2002 - 14:23:30 CEST