Results 1 to 4 of 4

Thread: QUERY Help

  1. #1
    Join Date
    Sep 2004
    Posts
    111

    Angry Unanswered: QUERY Help

    Team

    Here is the three tables created succssfully
    ================================

    db2 "create table emp (eid int not null primary key,salary int)"
    DB20000I The SQL command completed successfully.


    db2 "create table mgr (eid int not null primary key,bonus int)"
    DB20000I The SQL command completed successfully.


    db2 "create table src (eid int not null primary key,salary int,bonus int,ismgr char (1))"
    DB20000I The SQL command completed successfully.

    1st query is success
    =================

    db2 "with i1 as (select eid, bonus, ismgr from new table (insert into emp include (bonus int, ismgr char(1)) select eid, salary,bonus,ismgr from src)) select count(*) from new table (insert into mgr select eid,bonus from i1 where ismgr='Y')"

    1
    -----------
    0

    1 record(s) selected.

    2nd Query failed <--- Please help
    =============
    db2 "with i1 as (select eid, salary, bonus from new table (merge into emp include (bonus int, ismgr char(1)) using src on (1=0) when not matched and ismgr='N' then insert (eid,salary) values(src.eid,src.salary) when not matched nd ismgr='Y' then set eid=src.eid,salary=src.salary,bonus=src.bonus,ismg r=src.ismgr)select
    count(*) from new table (insert into mgr select eid,salary,bonus from i1 where ismgr='Y')"

    SQL0104N An unexpected token "with i1 as (select eid, sala" was found
    following "BEGIN-OF-STATEMENT". Expected tokens may include:
    "<labeled_begin_atomic>". SQLSTATE=42601

    Thanks
    Ra

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What are you trying to do with the second query?

    Andy

  3. #3
    Join Date
    Sep 2004
    Posts
    111
    To accomplish merge statement/output

    Thanks
    Raj

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The MERGE statement is for modifying data only. You cannot use it in a select--just like you cannot use INSERT, UPDATE, or DELETE.

    Andy

Posting Permissions

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