WITH enhancement in Oracle 12c

 No Responses »
Oct 102016
 

My colleague needed to use a function in the database in a query, but found that the function was not defined/reachable in his schema. So the solution is to ask for the function to be granted to you, or to create the function in your schema, or…

…use the new WITH enhancement available from Oracle 12c.

Here is a very simple example, that squares a number and returns it. The function is used in the query. How nice :-p

with
  function s(x in integer) return integer is
  begin
    return x*x;
  end;
  
  q as (select 1 n from dual union all select 2 from dual)
  
  select q.n,s(q.n) from q
/

Using ROW_NUMBER() in SQL Server update statement

 No Responses »
Sep 182014
 

You can’t use ROW_NUMBER() in an update statement in SQL Server, so:

UPDATE TheTable
SET    TheColumn = ROW_NUMBER();

won’t work. But sometimes that’s just what you want. This will do the trick:

UPDATE  Target
SET     TheColumn = RowNum
FROM
(
    SELECT  t.TheColumn, ROW_NUMBER() OVER(ORDER BY t.ID) AS RowNum
    FROM    TheTable t
	WHERE    ...
) AS Target;