Re: SQL Approach, Multiple Tables, UNION?

Tim Marshall wrote:

>On Sun, 7 Sep 2003, Daniel Morgan wrote:
>
>
>
>>>If you're still with me, thanks for reading and TIA for any suggestions
>>>other than beating up the developers of the maintenance application with
>>>which I'm working! 8)
>>>
>>>
>>>
>>Given the data you present I'd be thinking of using in-line views ...
>>not unions. And definitely native dynamic SQL.
>>
>>
>
>Thank you Daniel, I appreciate your toughing it through my post! I'm
>somewhat familiar with in-line views and will become even more familiar
>with them. I think this will be easier to construct than several Union
>queries...
>
>By dynamic native SQL, I hope I'm correct in assuming you mean using
>Oracle SQL and constructing it via a proc I run after a user has made
>his/her selections. Although I'm an Access hack, when working with Oracle
>databases, I much prefer to use pass through queries, ie, Oracle SQL and
>not the Jet SQL for several reasons. First, I find the theta joins in
>Oracle far, far easier to construct in code as opposed to the ansi style
>joins Access/Jet uses (the join is actually indicated in the from clause
>as opposed to the where clause as in Oracle). And secondly, from a
>performance point of view, using Jet linked tables means one is not taking
>advantage of the client server capabilities of Oracle: the SQL is
>processed in the Oracle server and then the ODBC link also means
>processing is performed by Jet on the client side. Which I think is kind
>of dumb if you can bypass the client and have the server return you your
>recordsets. Besides, depending on the nature of the Oracle database
>(often I am just writing apps for reporting on exisiting structures over
>which I have no control), Jet linked tables tend to crap out and drop
>returned data when Jet constructed queries contain more than 4 or 5 linked
>tables....
>
>Thanks again for the suggestion of in-line views.
>
>
>

Good instincts. The simplest form of using native dynamic SQL is a procedure like this:

CREATE OR REPLACE PROCEDURE nds (sql_string_in VARCHAR2) IS

BEGIN
   EXECUTE IMMEDIATE sql_string_in;
END nds;
/

Any executable string will run. For example you could pass in:

"BEGIN INSERT INTO mytable VALUES (1); END;"

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)