Database

toticow wrote:

I was just kind of hoping there was a way to store the value in a temp variable on a row by row basis

toticow wrote:

so thanks

WITH Results AS
(
    SELECT ColA - ColB [Result]
      FROM TABLE
)
SELECT *
  FROM Results 
 WHERE Result > 0

SELECT ColA - ColB [Result]
  INTO #temp
  FROM TABLE

SELECT *
  FROM #temp
 WHERE Result > 0

DROP TABLE #temp

DECLARE @temp TABLE (Result int)

INSERT @temp
SELECT ColA - ColB [Result]
  FROM TABLE

SELECT *
  FROM @temp
 WHERE Result > 0

Mika Wendelius wrote:

comes first in mind

PIEBALDconsult wrote:

Probably more portable too?

Jörgen Andersson wrote:

In Oracle 9i the maximum length of a list used in an IN clause is 1000 records

Jörgen Andersson wrote:

Does anyone know if it's still the same limit in version 10g or 11g?

Jörgen Andersson wrote:

A related question is what the limit is in SQLServer

Mika Wendelius wrote:

In Oracle 1000 limitation is not for records, but only for comma separated values in the list. So you cannot pass more literal values for an IN-list than 1000

Mika Wendelius wrote:

At least in 10g this applies

Jörgen Andersson wrote:

Thanks!

Jörgen Andersson wrote:

Which means I'll have to keep on using temp tables

Mika Wendelius wrote:

hat's one possibility, but since the the values for IN come from a subquery, you can use basically all the techniques what the database offers to create the result set (normal tables, temporary tables, xml tables, external tables etc etc).

• Can you put the data into an xml format and in the subquery use xquery to fetch the data again
• if you're inserting data into the database using this IN operator, you could use arrays to pass the data to a procedure
• if you're selecting data you can do the same and then return ref cursor
• possibly you could use UDT's in the above scenarios
• you could create a function as pipelined, pass the data to that with an array and then use the return value of the function as a table in your subquery etc.

CREATETRIGGER trigger_Update_ABC ON ABC
FOR Update AS
  UPDATE ABC
  SET ABC.Post = GetDate()
  WHERE ABC.ID IN (SELECT ID from inserted)

papy-boom wrote:

i don't have the column name of SELECT @@IDENTITY how can i manage it to get the value with a column name

papy-boom wrote:

you mean if i replaced by select max from my table it will works?

CREATE TABLE Artist (
   [Artist#] int           NOT NULL IDENTITY(1,1) PRIMARY KEY,
   [Name]    nvarchar(100) NOT NULL
);

INSERT INTO Artist ([Name]) VALUES (@name);
SET @artistIdentity= @@IDENTITY;

papy-boom wrote:

tried to get select @@IDENTITY AS returnedvalue to get the name of column but it doesn't work

papy-boom wrote:

i have a datareader to read the value returned value and the argument to pass to datareader is the name of the column so if my procedure return me a value without the name of column it's not simple to get the value