Results 1 to 7 of 7
  1. #1
    Join Date
    May 2011
    Posts
    3

    Unanswered: db2 export with table join(?)

    We're using V9.5_FP5 on AIX 5.3

    I have one table call_detail with call, calldate, callid and many more columns. I have 2nd table CALLIDLIST_TAB with a list of callids.

    I want to export all the records from table call_detail for date 5/7/11 that also have callids in table CALLIDLIST_TAB.

    I was thinking it would be something like this:

    db2 -v "export to arcexp050711.ixf of ixf messages arcexp050711.msg select * from schemaname.call_detail where calldate = '05/07/2011' and
    where callid in (select CALLID from schemaname.CALLIDLIST_TAB)";

    but I'm getting this error:

    SQL3022N An SQL error "-104" occurred while processing the SELECT string in the Action String parameter.
    SQL0104N An unexpected token "callid" was found following "5/07/2011' and
    where". Expected tokens may include: "<space>". SQLSTATE=42601


    Something's wrong with my syntax...I'm obviously not a sql expert...I think it's a simple fix though. I hope anyway ..Thanks for your help!
    Last edited by jbglotz; 05-14-11 at 12:02.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    select * from schemaname.call_detail where calldate = '05/07/2011' and
    where callid in (select CALLID from schemaname.CALLIDLIST_TAB)
    If you couldn't find an error in this SQL,
    you should learn SQL again from beginning.

    Don't stick too much on error message text.
    Most time, error message text is a key to solve the issue.
    But, if the error was syntax error(SQL0104N, SQLSTATE=42601),
    sometimes the message text may mislead you.
    Last edited by tonkuma; 05-14-11 at 08:21.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an example of SQL0104N error.

    Code:
    ------------------------------ Commands Entered ------------------------------
    select * from employee where where empno = '000100';
    ------------------------------------------------------------------------------
    SQL0104N  An unexpected token "empno" was found following "employee where 
    where".  Expected tokens may include:  "<space>".  SQLSTATE=42601
    But, this worked.
    Code:
    ------------------------------ Commands Entered ------------------------------
    select * from employee where_ where empno = '000100';
    ------------------------------------------------------------------------------
    
    EMPNO  FIRSTNME     MIDINIT LASTNAME        WORKDEPT PHONENO HIREDATE   JOB      EDLEVEL SEX BIRTHDATE  SALARY      BONUS       COMM       
    ------ ------------ ------- --------------- -------- ------- ---------- -------- ------- --- ---------- ----------- ----------- -----------
    000100 THEODORE     Q       SPENSER         E21      0972    2000-06-19 MANAGER       14 M   1980-12-18    86150.00      500.00     2092.00
    
      1 record(s) selected.

  4. #4
    Join Date
    May 2011
    Posts
    3
    Quote Originally Posted by tonkuma View Post
    If you couldn't find an error in this SQL,
    you should learn SQL again from beginning.

    Don't stick too much on error message text.
    Most time, error message text is a key to solve the issue.
    But, if the error was syntax error(SQL0104N, SQLSTATE=42601),
    sometimes the message text may mislead you.

    It would be great if I had time to learn SQL from the beginning. Thanks for taking the time to help. I realize it's a generic syntax error. I just thought someone would see what's wrong quickly. Thanks again.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by jbglotz View Post
    I just thought someone would see what's wrong quickly.
    Each subselect contains only one WHERE clause.

  6. #6
    Join Date
    May 2011
    Posts
    3

    solved

    Quote Originally Posted by n_i View Post
    Each subselect contains only one WHERE clause.
    Thank you! I knew it was something that would be obvious to someone! thanks again...query worked nicely.

  7. #7
    Join Date
    Aug 2010
    Posts
    3
    Quote Originally Posted by jbglotz View Post
    We're using V9.5_FP5 on AIX 5.3

    I have one table call_detail with call, calldate, callid and many more columns. I have 2nd table CALLIDLIST_TAB with a list of callids.

    I want to export all the records from table call_detail for date 5/7/11 that also have callids in table CALLIDLIST_TAB.

    I was thinking it would be something like this:

    db2 -v "export to arcexp050711.ixf of ixf messages arcexp050711.msg select * from schemaname.call_detail where calldate = '05/07/2011' and
    where callid in (select CALLID from schemaname.CALLIDLIST_TAB)";

    but I'm getting this error:



    SQL3022N An SQL error "-104" occurred while processing the SELECT string in the Action String parameter.
    SQL0104N An unexpected token "callid" was found following "5/07/2011' and
    where". Expected tokens may include: "<space>". SQLSTATE=42601


    Something's wrong with my syntax...I'm obviously not a sql expert...I think it's a simple fix though. I hope anyway ..Thanks for your help!

    hello
    When sql statements contain special characters, for example,
    line breaks, carriage returns, line feeds, receive the error SQLState=42601.
    Remove special characters from sql statements

    Regards
    jamil

Tags for this Thread

Posting Permissions

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