db2 - query works in db2 cli app but not at cli - need output in text

[sherdmt]

I am running a query in that i need the output in a text file.

if i am in the db2 cli program and run the query it works;

db2 => SELECT usr.USERS_ID,ureg.LOGONID,adr.FIRSTNAME,adr.LASTNAME,adr.ADDRESS1,adr.ADDRESS2,adr.CITY,adr.STATE,adr.ZIPCODE FROM USERS usr JOIN USERREG ureg ON usr.USERS_ID = ureg.USERS_ID JOIN ADDRESS adr ON usr.USERS_ID = adr.MEMBER_ID WHERE usr.REGISTRATION > '2006-02-16-22.10.02.221000' AND adr.ADDRESSTYPE = 'B' AND adr.LASTCREATE = (SELECT MAX(LASTCREATE) FROM ADDRESS WHERE MEMBER_ID = usr.USERS_ID AND ADDRESSTYPE = 'B')

if I run it from cli

db2 SELECT "usr.USERS_ID,ureg.LOGONID,adr.FIRSTNAME,adr.LASTNAME,adr.ADDRESS1,adr.ADDRESS2,adr.CITY,adr.STATE,adr.ZIPCODE" FROM "USERS" usr JOIN "USERREG" ureg ON "usr.USERS_ID = ureg.USERS_ID" JOIN "ADDRESS" adr ON "usr.USERS_ID = adr.MEMBER_ID" WHERE "usr.REGISTRATION > '2006-02-16-22.10.02.221000'" AND "adr.ADDRESSTYPE = 'B'" AND "adr.LASTCREATE = (SELECT MAX(LASTCREATE)" FROM "ADDRESS" WHERE "'MEMBER_ID = usr.USERS_ID' AND 'ADDRESSTYPE = 'B')'"
SQL0105N The string constant beginning with "B')'" is not valid.
SQLSTATE=42604


two things

1. Can someone tell me how to send the result from a query to a text file while in db2 console (I know I can pipe it if i'm not in db2)
2. If not, what is wrong with the second command being run from cli >

So, that's where I'm stuck. If i can get the syntax right it would be simple to pipe it. Unfortunately every change I make causes another error..
The query works, inside of db2. Is there no way to output the result to text from inside the db2 shell?

I've tried the following, with results;

[db2@wcs db2]$ db2 SELECT "usr.USERS_ID,ureg.LOGONID,adr.FIRSTNAME,adr.LASTNAME,adr.ADDRESS1,adr.ADDRESS2,adr.CITY,adr.STATE,adr.ZIPCODE" FROM "USERS" usr JOIN "USERREG" ureg ON "usr.USERS_ID = ureg.USERS_ID" JOIN "ADDRESS" adr ON "usr.USERS_ID = adr.MEMBER_ID" WHERE "usr.REGISTRATION > '2006-02-16-22.10.02.221000'" AND "adr.ADDRESSTYPE = 'B'" AND "adr.LASTCREATE = (SELECT MAX(LASTCREATE)" FROM "ADDRESS" WHERE "'MEMBER_ID = usr.USERS_ID' AND "ADDRESSTYPE = 'B')'"
bash: syntax error near unexpected token `'B')''
[db2@wcs db2]$ db2 SELECT "usr.USERS_ID,ureg.LOGONID,adr.FIRSTNAME,adr.LASTNAME,adr.ADDRESS1,adr.ADDRESS2,adr.CITY,adr.STATE,adr.ZIPCODE" FROM "USERS" usr JOIN "USERREG" ureg ON "usr.USERS_ID = ureg.USERS_ID" JOIN "ADDRESS" adr ON "usr.USERS_ID = adr.MEMBER_ID" WHERE "usr.REGISTRATION > '2006-02-16-22.10.02.221000'" AND "adr.ADDRESSTYPE = 'B'" AND "adr.LASTCREATE = (SELECT MAX(LASTCREATE)" FROM "ADDRESS" WHERE "'MEMBER_ID = usr.USERS_ID' AND "ADDRESSTYPE = 'B'")"
SQL0104N An unexpected token "AND" was found following "R_ID =
usr.USERS_ID'". Expected tokens may include: "<comp_op> <quantifier>

 

[gregsimpson]

sherdmt,

I notice you've done a lot more quoting on your query which you say you run from the cli. Might I suggest all you need to do is take the first query and put a " just bfore the SELECT and another " just after the last part 'B')

Therefore try running

db2 "SELECT usr.USERS_ID,ureg.LOGONID,adr.FIRSTNAME,adr.LASTNAME,adr.ADDRESS1,adr.ADDRESS2,adr.CITY,adr.STATE,adr.ZIPCODE FROM USERS usr JOIN USERREG ureg ON usr.USERS_ID = ureg.USERS_ID JOIN ADDRESS adr ON usr.USERS_ID = adr.MEMBER_ID WHERE usr.REGISTRATION > '2006-02-16-22.10.02.221000' AND adr.ADDRESSTYPE = 'B' AND adr.LASTCREATE = (SELECT MAX(LASTCREATE) FROM ADDRESS WHERE MEMBER_ID = usr.USERS_ID AND ADDRESSTYPE = 'B')"

I noticed in your second one, that your quotes appear to be mismatched, for example

AND 'ADDRESSTYPE = 'B')'"

appears to have a mismatch of quotes.

Hope this helps.

Cheers
Greg