Re: Havy SQL

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: Thu, 20 Apr 2006 09:30:41 GMT
Message-ID: <5xI1g.11123$vy1.8662@news-server.bigpond.net.au>

Try an inner query to remove the unwanted rows as in: SELECT 'x3y2q1__q1',

          partition_name
    FROM ( /* begin inner query */

        SELECT partition_name
              FROM all_tab_partitions
              WHERE
    UPPER(partition_name)  not like  '%CUR'

    AND UPPER(table_name) = UPPER('A1_MSC_TERMINATION_D')     AND UPPER(table_owner) = UPPER('bus')     ) /* end of inner query */
    where (SYSDATE - TO_DATE(SUBSTR(partition_name,-8,8),'YYYYMMDD')) > 186

Douglas

"Wind.Dancer" <wind.dancer_at_hotmail.com> wrote in message news:1145520521.216999.178520_at_g10g2000cwb.googlegroups.com...
> My Sql is
>
> SELECT 'x3y2q1__q1',
> partition_name
> FROM all_tab_partitions
> where (SYSDATE - TO_DATE(SUBSTR(partition_name,-8,8),'YYYYMMDD')) >
> 186
> and UPPER(partition_name) not like '%CUR'
> AND UPPER(table_name) = UPPER('A1_MSC_TERMINATION_D')
> AND UPPER(table_owner) = UPPER('bus')
> ;
>
>
> If I run it I got
> [1]: (Error): ORA-01841: (full) year must be between -4713 and +9999,
> and not be 0
>
> Because it executes the
> where (SYSDATE - TO_DATE(SUBSTR(partition_name,-8,8),'YYYYMMDD')) > 186
> before the
> and UPPER(partition_name) not like '%CUR'
>
>
> Any Ideas
>
> thx in advanced
>
Received on Thu Apr 20 2006 - 04:30:41 CDT