db2 export with table join(?)

jbglotz · #1 (**permalink**) 05-14-11, 02:27

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!

tonkuma · #2 (**permalink**) 05-14-11, 07:09

Quote:

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.

tonkuma · #3 (**permalink**) 05-14-11, 07:36

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.

jbglotz · #4 (**permalink**) 05-14-11, 11:08

Quote:

Originally Posted by tonkuma

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.

n_i · #5 (**permalink**) 05-14-11, 13:09

Quote:

Originally Posted by jbglotz

I just thought someone would see what's wrong quickly.

Each subselect contains only one WHERE clause.

jbglotz · #6 (**permalink**) 05-14-11, 22:47

Quote:

Originally Posted by n_i

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.

jamiforyou · #7 (**permalink**) 05-17-11, 03:21

Quote:

Originally Posted by jbglotz

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

Internet Services

Web Development

Digital Marketing

Consumer Tech