Archives of the TeradataForum

Message Posted: Fri, 09 Dec 2005 @ 14:33:10 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Saurabh_Dinesh wrote:

          > Delete FROM Table1
          > Where
          > (
          >    Primary index columns
          > )
          > IN
          > (SEL Primary index columns
          >      FROM Table1
          >      Where some_condition and
          >      Having count (*) >1
          >      Group by Primary index columns)
          >      Qualify row_number () OVER (partition BY
          >                                  Primary index columns
          >                                  Order by column asc) =1;
          >
          >
          > Error 3706: expected something between ')' and the 'Qualify' Keyword
          > /********************************************************/

Qualify can't be used within delete.

If there's a Primary Key you can rewrite it:

     Delete FROM Table1
     Where
     (
        Primary KEY columns
     )
     IN
       (select * from
         (SEL Primary KEY columns
          FROM Table1
          Where some_condition
          Qualify
            row_number () OVER (partition BY Primary index columns
                                Order by column asc) =1
          and
            count(*) OVER (partition BY Primary index columns) > 1
         ) dt
       );

As you already noticed it's not allowed to use OLAP-Functions within subqueries. But if you put it in a Derived Table within the subquery, it works :-)

Another way would be to rewrite as a Join-Delete.

If there's no Primary Key use ROWID (if it's enabled on your system) to get unique rows.

Dieter

	<Prev	Next>		<<First	<Prev	Next>	Last>>