substr(bm.stat_date,-7) = '01/2003';
doesn't look right. If bm.stat_date is a date then a substr of a date is
not a good idea. (dates are not strings, dates are dates. They can be
formatted as strings.) So maybe you meant
substr(to_char(bm.stat_date,'mm/dd/yyyy'),-7) = '01/2003';
which could be done
to_char(bm.stat_date,'dd/yyyy') = '01/2003';
Jim
--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Mai Dang" <mdang_at_bigpond.com.au> wrote in message
news:b31brh$3p7$1_at_mws-stat-syd.cdn.telstra.com.au...
> Hi,
>
> I wonder if anyone can help me where I did wrong in the following PL/SQL
>
> If I only run the DELETE and INSERT Statements without CURSOR, then SQL
> works, by replacing
> to_char(aggr_date, 'DD/MM/YYYY') with '01/01/2003').
>
> But if I compile the whole PL/SQL then it ended up with
>
> Error Message
> ============
> Connected.
> (SELECT VERSION
> *
> ERROR at line 16:
> ORA-06550: line 16, column 8:
> PLS-00103: Encountered the symbol "SELECT" when expecting one of the
> following:
> ( - + mod not null others <an identifier>
> <a double-quoted delimited-identifier> <a bind variable> avg
> count current exists max min prior sql stddev sum variance
> execute forall time timestamp interval date
> <a string literal with character set specification>
> <a number> <a single-quoted SQL string>
> ORA-06550: line 19, column 33:
> PLS-00103: Encountered the symbol "VERSION" when expecting one of the
> following:
> ; return returning and or
> The symbol "return" was substituted for "VERSION" to continue.
> ORA-06550: line 23, column 7:
> PLS-00103: Encountered the symbol "FROM" when expecting one of the
> following:
> , into bulk
>
>
> PL/SQL:
> =======
>
> set serveroutput on;
> DECLARE
> CURSOR bm_cursor IS
> SELECT to_date(bm.stat_date, 'DD/MM/YYYY') As stat_date FROM
> cq_vps_aggr_cc_perform_bm_v8 bm where bm.BM= 'BM1' and
> substr(bm.stat_date,-7) = '01/2003';
> aggr_date DATE;
> BEGIN
> FOR bm_rec IN bm_cursor LOOP
> aggr_date := bm_rec.stat_date;
> IFaggr_date = '01-JAN-2003' THEN
> DELETE FROM CQ_VPS_AGGR_CC_PERFORM_CBM_V8
> WHERE BM = 'CBM4' AND stat_date = to_char(aggr_date, 'DD/MM/YYYY');
> COMMIT;
>
> INSERT INTO CQ_VPS_AGGR_CC_PERFORM_CBM_V8
> (SELECT
> to_char(aggr_date, 'DD/MM/YYYY') STAT_DATE,
> (SELECT VERSION
> FROM CQ_VPS_AGGR_CC_PERFORM_CBM_V8
> WHERE stat_date = to_char(aggr_date, 'DD/MM/YYYY')
> AND BM IN ('CBM1')) VERSION,
> 'CBM4' AS BM,
> TOTAL,
> SYSDATE AS RUN_DATE
> FROM
> (
> SELECT (
> SELECT TOTAL
> FROM CQ_VPS_AGGR_CC_PERFORM_CBM_V8
> WHERE stat_date = to_char(aggr_date, 'DD/MM/YYYY')
> AND BM IN ('CBM1')) +
> (SELECT TOTAL
> FROM CQ_VPS_AGGR_CC_PERFORM_CBM_V8
> WHERE stat_date = to_char(aggr_date, 'DD/MM/YYYY')
> AND BM IN ('CBM2')) +
> (SELECT TOTAL
> FROM CQ_VPS_AGGR_CC_PERFORM_CBM_V8
> WHERE stat_date = to_char(aggr_date, 'DD/MM/YYYY')
> AND BM IN ('CBM3')
> ) AS TOTAL FROM DUAL
> ));
> COMMIT;
>
> END IF;
> dbms_output.put_line(aggr_date);
> END LOOP;
> END;
>
>
>
>
Received on Wed Feb 19 2003 - 20:06:26 CST
