locked
What is wrong with my query??? RRS feed

  • Question

  • User-260044566 posted
    I'm trying to insert the result from the with clause into a table and it produces the following error,
    ORA-06550: line 11, column 29:
    PL/SQL: ORA-00928: missing SELECT keyword
    ORA-06550: line 5, column 1:
    PL/SQL: SQL Statement ignored
    declare startdate date := TO_DATE('01/01/2011','MM/dd/yyyy');
    enddate date := LAST_DAY(startdate);
    weekCount number := 1;
    BEGIN
    INSERT INTO DATECHECK(WEEKNAME,DAYNAME)
    with amonth(WeekName,day) as
    (select 1,startdate FROM DUAL
    union all
    select CASE WHEN (7 - TO_CHAR(CAST(day+1 AS DATE),'D')) = 0 THEN WeekName+1 ELSE WeekName END, day + 1
    from amonth
    where day < enddate);
    END;


    Thursday, December 8, 2011 6:36 AM

All replies

  • User551462331 posted

    it dosent have select statement at the end... copy this code and try

    BEGIN
    INSERT INTO DATECHECK(WEEKNAME,DAYNAME)
    with amonth(WeekName,day) as
    (select 1,startdate FROM DUAL
    union all
    select CASE WHEN (7 - TO_CHAR(CAST(day+1 AS DATE),'D')) = 0 THEN WeekName+1 ELSE WeekName END, day + 1
    from amonth
    where day < enddate)
    select * from amonth;
    END;

    hope this helps...1

    Thursday, December 8, 2011 8:54 AM