Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2010
    Posts
    4

    Unanswered: Help with insert into select statement

    Hello all...I am relatively new to coding. I have a project to take an input file, take a portion of the data, join it with data from another table and insert it into a third table. What I have done is taken that data and inserted into a temporary table. What I am trying to do is select some of the columns from my temporary table, massage it and insert it into my final destination table. I get the error message :ORA-00936: missing expression...

    insert into remark_summary(REMARK_CAT,REMARK_WHO,OP_NBR, REMARK,RMKTYPE,TRANSCODE,TYPE_X,YMDEFF,YMDEND,YMDE NTERED,YMDTRANS)
    values
    (select (RTRIM(v_contract_nbr||' '||v_type_x||v_csw_code)),
    (RTRIM(v_contract_nbr||' '||v_type_x))
    from health_edu_remark
    'SYS',
    (select DISTINCT cd.description
    from code_detail cd,
    health_edu_remark hr
    where substr(cd.code_nbr,3,2) = hr.v_csw_code),
    'CL',
    'AD',
    'ME',
    ((V_file_year||v_file_month||'0'||v_file_day) from health_edu_remark),
    '99991231',
    (to_char(sysdate,'yyyymmdd')),
    (to_char(sysdate,'yyyymmdd')from dual)
    );

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You don't need the VALUES clause if you use INSERT .. SELECT ...
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    you must have only matched pairs of SELECT & FROM
    In other words for every SELECT, a corresponding FROM must exist
    Conversely every FROM requires a matching SELECT
    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
    Jan 2010
    Posts
    4
    So are you saying for every entry, i need a select and from?


    insert into remark_summary(REMARK_CAT,REMARK_WHO,OP_NBR, REMARK,RMKTYPE,TRANSCODE,TYPE_X,YMDEFF,YMDEND,YMDE NTERED,YMDTRANS)
    ((select (RTRIM(v_contract_nbr||' '||v_type_x||v_csw_code)) from health_edu_remark),
    (select (RTRIM(v_contract_nbr||' '||v_type_x)) from health_edu_remark),
    'SYS',
    (select DISTINCT cd.description
    from code_detail cd,
    health_edu_remark hr
    where substr(cd.code_nbr,3,2) = hr.v_csw_code),
    'CL',
    'AD',
    'ME',
    (select (V_file_year||v_file_month||'0'||v_file_day) from health_edu_remark),
    '99991231',
    (select to_char(sysdate,'yyyymmdd') from dual),
    (select to_char(sysdate,'yyyymmdd')from dual)
    );
    Last edited by arkansasgal; 01-28-10 at 14:30.

  5. #5
    Join Date
    Jan 2010
    Posts
    4
    when i ran the above, it is now saying missing right parenthesis and I know that is not....am i missing something?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >So are you saying for every entry, i need a select and from?
    NO

    Previously you had

    >(to_char(sysdate,'yyyymmdd')from dual)
    where a FROM existed without SELECT

    one way to correct that would be to do as follows:

    (to_char(sysdate,'yyyymmdd')),

    >(select (V_file_year||v_file_month||'0'||v_file_day) from health_edu_remark)
    How many rows are returned by SELECT above?
    How does the results get assigned into a scalar?
    Do you know what a scalar value is?
    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.

  7. #7
    Join Date
    Jan 2010
    Posts
    4
    In this particular instance all 25 rows that were inserted into the temporary table are being returned. So basically every record that was inserted into the temp table, i wantg it inserted into the destination table.
    A scalar value is a single value I believe.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    example SQL to follow

    INSERT INTO NEW_SUMMARY SELECT * FROM MY_TMP_TBL;

    above assumes the table structures for both tables are identical; same number of field,of same datatype, in same order.

    It would be helpful if you provided DDL for tables involved.
    It would be helpful if you provided DML for test data.
    It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

    I really have no idea what you have or what needs to be done.
    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.

Posting Permissions

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