|
Hi Blumen
There are several ways of emulating temporary tables in Oracle -
Global Temporary Tables
This is set up in the same way as a normal database table:
create global temporary table MyTable (
MyColumn INT,
etc ...
) on commit preserve Each connection can then insert/update/delete/select to their heart's content - safe in the knowledge that the activity is entirely local to that session.
PL/SQL Tables
If you don't need to store much data then a PL/SQL table might do the trick:
TYPE primary_keys_tabtype IS TABLE OF NUMBER NOT NULL
INDEX BY BINARY_INTEGER;
ClientIdList primary_keys_tabtype;See here[^] for details.
Refactoring SQL
You can sometimes remove the need for temporary tables by refactoring your select statements. Inline views allow you to use a select statement within the from-clause of another statement:
select A.*
from (
select 'Some complex stuff'
from SomeTable
) as A Newer versions of Oracle now supports a feature called "Subquery Refactoring". See here[^] for details.
Hope that helps.
Andy
|
|
|
|
|
Hi chaps,
I'm wondering, is there anything out there that can parse a parametereised SQL statement and guess at what the parameter types should be? Similar to how datasets work. I figure that they compare it with the table schema, but i've no idea.
I take it i start from a standard SELECT * statement, and move from there, but are there any tools out there that could help me? I suck at Regex, and while i'm learning, i far from a Guru.
Cheers
T
-------------------------------
Carrier Bags - 21st Century Tumbleweed.
|
|
|
|
|
HI
I want to add index in existing table. Can any person help me on this to generate script. What would be the syntax if possible give me example.
Thanx
Shahzad Aslam
|
|
|
|
|
For SQL Server 2005:
CREATE INDEX [IndexName] ON [TableName]([ColumnName])
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it! 
|
|
|
|
|
|
In my application when a record is updated for the flights in an itinerary they original flights are first moved to a history table for reporting purposes. What I am wanting to know is how would I use something like a timestamp of when they were moved to the history table, that way when reporting they can see exactly when the flights were last moved/modified/updated in the history table. Since there could possibly be 20 flights for a single record (since the flights can be updated multiple times before the itinerary is flown) simply updating the last_update field for a given record locator wont work as it will update ALL records, when I only want to update the ones currently being moved to the history table. We are using SQL 2000 and I'm not finding much online for using a timestamp feature.
"Okay, I give up: which is NOT a real programming language????"
Michael Bergman
"Well yes, it is an Integer, but it's a metrosexual Integer. For all we know, under all that hair gel it could be a Boolean."
Tom Welch
"Let's face it, the average computer user has the brain of a Spider Monkey."
Bill Gates
|
|
|
|
|
Why not set a date stamp with GETDATE() on insert to the Audit table?
Or is that not what you are looking for?
-------------------------------
Carrier Bags - 21st Century Tumbleweed.
|
|
|
|
|
Hi.
I want to insert a record in a table and then get it's ID(which is an autonumber field) to use it as a reference in another table. What is the best approach?
Thanks.
|
|
|
|
|
blackjack2150 wrote: I want to insert a record in a table and then get it's ID(which is an autonumber field) to use it as a reference in another table. What is the best approach?
What is the database?
|
|
|
|
|
It can be either Access, MySql or SQL Server. The only thing that changes to select the database engine is the connection string. Therefor I don't want to do any programming at the database side(like stored procedures).
The trivial solution is to do insert and after select the max value of the id, hoping that nothing was inserted in between the two operations.
Other than that I don't see other options, I'm afraid. Can you?
|
|
|
|
|
blackjack2150 wrote: It can be either Access, MySql or SQL Server. The only thing that changes to select the database engine is the connection string. Therefor I don't want to do any programming at the database side(like stored procedures).
Since SQL is implemented slightly differently on each database you'll need to do more than just change the connection string. And you are already doing "programming at the database side" by just sending a SQL command through.
The way you are using now actually implies a gaping security hole as parameters are implemented differently in each langage. If you inject values in to SQL you are opening yourself up for a SQL Injection Attack. You should read SQL Injection Attacks and Tips on How to Prevent Them[^]
If you design your DAL (Data Abstraction Layer) well you can take advantage of the Strategy pattern and have one common interface to your application, but the actual concrete class will know exactly how to communicate most efficiently with the chosen database. This is the design you should be aiming for for the type of application you've described.
blackjack2150 wrote: The trivial solution is to do insert and after select the max value of the id, hoping that nothing was inserted in between the two operations.
Well, that is risky. The other thing is to SELECT the most recent row with all the information you just inserted.
SELECT Id, Column1, Column2, Column3, Etc
FROM MyTable
WHERE Column1 = @column1
AND Column2 = @Column2
AND Column3 = @Column3
ORDER BY Id DESC
But, as you can see, it requires parameters, which are, as I've mention, defined differently in each implementation of SQL.
|
|
|
|
|
I ended up reading the record back . The quick and nasty way was by a table read , the better way is via a stored procedure
Create a stored procedure to insert and then return the autoint .At least you can handle concurrent ionserts in the SQL in the sproc which is better than in embedded SQL in an app.
|
|
|
|
|
If the table has a Primary Key that is set to autoincrement, all you need to do is:
INSERT INTO TABLE (COLUMN1, COLUMN2) VALUES (VALUE1, VALUE2); SELECT @@IDENTITY
This will return the ID of the last affected row.
|
|
|
|
|
I am also needing the primary key returned because I'm adding other dependent data to database.
My db is Access and I tried the "..SELECT @@ IDENTITY" to my command and the program threw an error. The Access db is in 2000 format. Any suggestions on getting this to work?
may your code be error free
|
|
|
|
|
Hi,
The above query is working fine. But how will I get the return value ? Shall I put as return @@identity?
Pls help.
Meeram395
|
|
|
|
|
A. This only works in SQL Server.
B. This is really bad practice. You shouldn't use @@Identity because there are problems with using it with triggers. Use Scope_Identity() instead.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
|
my application is based on just one independent table there's no relations ..
what's the best choise to build this database app.
i mean which db engine you recommend to use , SQL , access ,
i don't know , please help
many regards
|
|
|
|
|
Go for Access, i think its a better Option for you right now. but its nice to get familiar with SQL, because later maybe by any reason you might want to upgrade to relational Database System.So like that it wont be an effort to Change, what do you think
Vuyiswa
|
|
|
|
|
i have updated a wrong set of data in SQl, how do i restore it, would i restore them based on the Query that i have Done. PLease help.
here is the Update Statement
update property
set attrib_code = substring(attrib_code,1,6)+'19'+ substring(attrib_code,9,2)
where
substring(attrib_code,1,1)='1' and
substring(attrib_code,3,1)='1'
and substring(Attrib_code,9,2)='00'
I WAS sUPPOSED TO MAKE THE LAST PAIR 19
Vuyiswa
Vuyiswa
|
|
|
|
|
you might use triggers for the update operation and in that trigger using the inserted and deleted tables you can retsore the previous values if required.
Gautham
|
|
|
|
|
Hi to all,
I want to write storedprocedure which take Name or Year as Parameter and Display Records Order by Name or Year.
Note:From UI User may select Orderby Name or Year.
Mkanchha
|
|
|
|
|
We can't write this for you because we don't know what your database schema is. If this is the only two orders you may want, I'd write a proc for each, otherwise I believe you need to build your SQL as a string and execute it.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Pass "1" or "2" to the database
and
using simple if statement sort accordingly.
Regards,
Arun Kumar.A
|
|
|
|
|
Or try this:
select *
from
tblName
order by
case
WHEN @parmaeter=1 then
Name
else
Year
end ASC
Regards,
Arun Kumar.A
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
