Re: SQL statement inside a CL

[GUY_HENZA@xxxxxxxxxxxxxx]

That worked.

Thank you,

Guy

Opinions expressed are my own and do not necessarily represent the views of
my employer or anyone in their right mind.


                                                                                                                                          
                      "Brian Johnson"                                                                                                     
                      <brian.johnson.mn@gm        To:       "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>             
                      ail.com>                    cc:                                                                                     
                      Sent by:                    Subject:  Re: SQL statement inside a CL                                                 
                      midrange-l-bounces@m                                                                                                
                      idrange.com                                                                                                         
                                                                                                                                          
                                                                                                                                          
                      01/17/2008 11:26 AM                                                                                                 
                      Please respond to                                                                                                   
                      Midrange Systems                                                                                                    
                      Technical Discussion                                                                                                
                                                                                                                                          
                                                                                                                                          




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, ...