Re: SQL statement inside a CL

["Brian Johnson" <brian.johnson.mn@xxxxxxxxx>]

It's a feature!

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzahz/quoting.htm

Escape the $ signs (and other characters with special meaning to QSH) with
back-slash and you will have better results:


db2 " select A$A from lib.file"
Column A not in specified tables.


db2 " select A\$A from lib.file"
Column A$A not in specified tables.


On Jan 17, 2008 10:07 AM, Josh Diggs <JDiggs@xxxxxxxxxxxx> wrote:

> Looks like the '$' in CN$CAB is causing problems.  I was circling in on a
> definitive cause, but ran out of steam.
>
> If you're a glutton for punishment, you can probably find the answer here
>
>
> http://www.cse.iitb.ac.in/dbms/Data/Papers-Other/SQL1999/ansi-iso-9075-2-1999.pdf
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of
> GUY_HENZA@xxxxxxxxxxxxxx
> Sent: Thursday, January 17, 2008 6:47 AM
> To: Midrange Systems Technical Discussion
> Subject: SQL statement inside a CL
>
>
>
> First, thanks.
> Second, I added the following to my CL;
>
> CLRPFM     FILE(GHENZA/FSHCNP)
> QSH        CMD('DB2 "INSERT INTO GHENZA/FSHCNP
>             (CNREP#, CNPLTS, CN$CAB, CNDESN)
>             SELECT DISTINCT
>             CNREP#, CNPLTS, CN$CAB, CNDESN
>             FROM NDSPRDFIL/FSHCNP"')
>
> and I get;
>  **** CLI ERROR *****
>         SQLSTATE: 42703
> NATIVE ERROR CODE: -206
> Column CN not in specified tables.
>
> Message . . . . :   Column CN not in specified tables.
> Cause . . . . . :   CN is not a column of table *N in *N. If the table is
> *N,
>  CN is not a column of any table or view that can be referenced. Recovery
> .
>  . . :   Do one of the following and try the request again: -- Ensure that
>  the column and table names are specified correctly in the statement. --
> If
>  this is a SELECT statement, ensure that all the required tables were
> named
>  in the FROM clause. -- If the column was intended to be a correlated
>  reference, qualify the column with the correct table designator.
>
>
> It works when I run it interactively...
> Does it not like the CN$CAB field name?  I tried to enclose it in double
> quotes with the same results and it won't accept single quotes.  Any
> suggestions?
>
> Regards,
>
> Guy
>
> Opinions expressed are my own and do not necessarily represent the views
> of
> my employer or anyone in their right mind.
>
>
>
>                      rob@xxxxxxxxx
>                      Sent by:                    To:       Midrange
> Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
>                      midrange-l-bounces@m        cc:
>                      idrange.com                 Subject:  Re: Can I run
> an SQL statement inside a CL?
>
>
>                      01/17/2008 08:38 AM
>                      Please respond to
>                      Midrange Systems
>                      Technical Discussion
>
>
>
>
>
>
> <snip>
>     QSH CMD('db2 "select * from lib.file FETCH FIRST 12 ROWS ONLY"')
> </snip>
>
> First, thanks.
> Second, ...
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.