Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: An INTO clause is expected in this select statement

    Hi folks,

    I'm trying to get to the bottom of this error, when trying to run the following statement:

    Code:
    DECLARE AcYear number := TO_NUMBER(CASE WHEN TO_NUMBER(CURRENT_DATE, 'MM') < 9 THEN TO_NUMBER(CURRENT_DATE, 'YYYY') - 1 ELSE TO_NUMBER(CURRENT_DATE, 'YYYY') END);
    
    BEGIN
    SELECT (CASE WHEN c.COURSE IN ('ACHE01', 'ACHE02', 'ACHE06', 'ACHE03', 'ASHE01', 'ASHE02', 'ACHE05') THEN 'Chester' ELSE 'Harper' END) AS COLLEGE,
            c.COURSE, c.DESCRIPTION, ci.COURSE_YEAR, p.FIRST_NAME, p.SURNAME, TO_CHAR(p.DATE_OF_BIRTH, 'DD-MM-YYYY'), p.ID_NUMBER, ci.ACADEMIC_YEAR,
           (CASE WHEN mos.MODE_OF_STUDY IN ('FULL-TIME_FULL_Y', 'SANDWICH', 'FULL-TIME_LESS_T', 'FTS', '04') THEN 'FT' ELSE 'PT' END) AS STUDY
    FROM QUERCUS.COURSE c, QUERCUS.COURSE_INSTANCE ci, QUERCUS.STUDENT_COURSE_DETAIL scd, QUERCUS.PERSON p, MODE_OF_STUDY mos, STATUS s
    WHERE scd.PERSON = p.OBJECT_ID AND
          scd.COURSE_INSTANCE = ci.OBJECT_ID AND
          scd.STATUS = s.OBJECT_ID AND
          ci.COURSE = c.OBJECT_ID AND
          c.MODE_OF_STUDY = mos.OBJECT_ID AND
          (s.STATUS = 'R') AND ((c.COURSE LIKE '%HE%') AND NOT (c.COURSE LIKE 'BSHE%')) AND (ci.ACADEMIC_YEAR = AcYear);
    END;
    This was originally a SQL Server procedure, but it didn't work as I wanted.

    I've managed to iron out quite a few errors I was getting, but can't figure out how to solve this one.

    It seems to have happened after I declared the parameter.

    Many Thanks
    <- Hides behind a rock.

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    You have to create a local variable for every column in your SELECT clause, and push values INTO them:

    Code:
    declare
      val1 varchar2(10);
      val2 varchar2(10);
    begin
      select 'Chuck','Forbes'
      into val1, val2
      from dual;
    end;

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    from where does CURRENT_DATE get assigned any value?

    SELECT executed inside PL/SQL requires INTO clause so it has a destination for the values coming from the database.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by anacedent
    from where does CURRENT_DATE get assigned any value?
    From Oracle

    CURRENT_DATE

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Thanks. Needless to say, I did not know that.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Sep 2005
    Posts
    240
    Thanks, I've added this row into the statement and declared the other variables, but now it shows another error:

    ORA-06502: Numeric or value error
    ORA-06512: at line 1
    Code:
    DECLARE AcYear number(10) := TO_NUMBER(CASE WHEN TO_NUMBER(CURRENT_DATE, 'MM') < 9 THEN TO_NUMBER(CURRENT_DATE, 'YYYY') - 1 ELSE TO_NUMBER(CURRENT_DATE, 'YYYY') END);
    COLLEGE varchar(10);
    COURSE varchar(50);
    COURSE_TITLE varchar(255);
    COURSE_YEAR number;
    FIRST_NAME varchar(50);
    SURNAME varchar(50);
    DOB varchar(10);
    ID_NUMBER number;
    ACADEMIC_YEAR number(10);
    STUDY varchar(5);
    
    BEGIN
    SELECT (CASE WHEN c.COURSE IN ('ACHE01', 'ACHE02', 'ACHE06', 'ACHE03', 'ASHE01', 'ASHE02', 'ACHE05') THEN 'Chester' ELSE 'Harper' END) AS COLLEGE,
            c.COURSE, c.DESCRIPTION, ci.COURSE_YEAR, p.FIRST_NAME, p.SURNAME, TO_CHAR(p.DATE_OF_BIRTH, 'DD-MM-YYYY'), p.ID_NUMBER, ci.ACADEMIC_YEAR,
           (CASE WHEN mos.MODE_OF_STUDY IN ('FULL-TIME_FULL_Y', 'SANDWICH', 'FULL-TIME_LESS_T', 'FTS', '04') THEN 'FT' ELSE 'PT' END) AS STUDY
    INTO COLLEGE, COURSE, COURSE_TITLE, COURSE_YEAR, FIRST_NAME, SURNAME, DOB, ID_NUMBER, ACADEMIC_YEAR, STUDY
    FROM QUERCUS.COURSE c, QUERCUS.COURSE_INSTANCE ci, QUERCUS.STUDENT_COURSE_DETAIL scd, QUERCUS.PERSON p, MODE_OF_STUDY mos, STATUS s
    WHERE scd.PERSON = p.OBJECT_ID AND
          scd.COURSE_INSTANCE = ci.OBJECT_ID AND
          scd.STATUS = s.OBJECT_ID AND
          ci.COURSE = c.OBJECT_ID AND
          c.MODE_OF_STUDY = mos.OBJECT_ID AND
          (s.STATUS = 'R') AND ((c.COURSE LIKE '%HE%') AND NOT (c.COURSE LIKE 'BSHE%')) AND (ci.ACADEMIC_YEAR = AcYear);
    END;
    <- Hides behind a rock.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    By the way, VARCHAR2 data type is the preferred string variable type.

    >ORA-06502: Numeric or value error
    The most frequent reason this error occurs is when the value is larger than the variable can hold.


    NAME VARCHAR2(5);

    NAME := 'MY NAME'; -- would result in ORA-06502 error

    or

    ID NUMBER;
    ID := 'A'; -- would result in ORA-06502 error
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by anacedent
    By the way, VARCHAR2 data type is the preferred string variable type.
    Which is true and Oracle is telling that since version 7.x, but at the same time happily converts the VARCHAR definition in a CREATE TABLE to a VARCHAR2 (also since 7.x).

    So, the sample CREATE TABLE statement does in fact create a VARCHAR2 column.

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool To_number(to_char())

    Your CASE statement requires a combination of TO_NUMBER(TO_CHAR()) to work!

    Why don't you just add it to the query?:
    Code:
    SELECT (CASE
               WHEN c.course IN
                      ('ACHE01',
                       'ACHE02',
                       'ACHE06',
                       'ACHE03',
                       'ASHE01',
                       'ASHE02',
                       'ACHE05'
                      )
                  THEN 'Chester'
               ELSE 'Harper'
            END
           ) AS college,
           c.course, c.description, ci.course_year, p.first_name, p.surname,
           TO_CHAR (p.date_of_birth, 'DD-MM-YYYY'), p.id_number, ci.academic_year,
           (CASE
               WHEN mos.mode_of_study IN
                      ('FULL-TIME_FULL_Y',
                       'SANDWICH',
                       'FULL-TIME_LESS_T',
                       'FTS',
                       '04'
                      )
                  THEN 'FT'
               ELSE 'PT'
            END
           ) AS study
      FROM quercus.course c,
           quercus.course_instance ci,
           quercus.student_course_detail scd,
           quercus.person p,
           mode_of_study mos,
           status s
     WHERE scd.person = p.object_id
       AND scd.course_instance = ci.object_id
       AND scd.status = s.object_id
       AND ci.course = c.object_id
       AND c.mode_of_study = mos.object_id
       AND (s.status = 'R')
       AND ((c.course LIKE '%HE%') AND NOT (c.course LIKE 'BSHE%'))
       AND (ci.academic_year = (
         CASE WHEN TO_NUMBER (TO_CHAR (CURRENT_DATE, 'MM')) < 9
              THEN TO_NUMBER (TO_CHAR (CURRENT_DATE, 'YYYY')) - 1
              ELSE TO_NUMBER (TO_CHAR (CURRENT_DATE, 'YYYY')) END)
    /
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •