Re: CREATE TABLE AS ... WITH DATA

[R Bruce Hoffman <bruce.hoffman@xxxxxxxxxxxxxxxxx>]

Why is simple... it's in the reference manual. You can not use Variables 
or parameter markers in the select statement of the CREATE TABLE <name> 
AS (<select>) statement.
Bottom of page 794.

Michael_Schutte@xxxxxxxxxxxx wrote:
> I see that documented everywhere but haven't found an explanation as to
> why.  It's not like I'm trying create a table with a host variable in the
> table.  I'm just trying to select records.   But doesn't matter anyway.  It
> can be dropped.  I've changed it to create the table first and then do a
> insert into.
>
>
>
>
>
>
> --
>
> Michael Schutte
> Admin Professional
>
>
>
> Try Bob Evans GRILLING SAUSAGE! This summerâs hottest destination is your
> own backyard with Bob Evans Brats and Italian Sausage! For tasty recipes
> using Bob Evans grilling sausage, visit http://www.BobEvans.com/Recipes
>
>
>
> rpg400-l-bounces@xxxxxxxxxxxx wrote on 05/11/2009 11:10:43 AM:
>
>   
> > Michael, I think the answer is simply that host variables are not
> > allowed on a CREATE statement.
> >
> > Regards
> >
> > Paul Tuohy
> > ComCon
> > www.comconadvisor.com
> > www.systemideveloper.com
> >
> >
> >
> >
> >
> > Michael_Schutte@xxxxxxxxxxxx wrote:
> >     
> > > Is this suppose to answer my question? Because I'm not following you. I
> > > understand that host variables are used in prepared statements but why
> > >       
> are
>   
> > > they not permitted in this prepared statement?
> > >
> > > Exec SQL
> > > Create table qtemp/tmptable as (
> > > select * from myfile
> > > where company = :hostcpy) with data;
> > >
> > > I'm wanting this run multiple times in the program, so reuse and
> > > performance is needed.
> > >
> > > What I don't get is that this is okay.
> > >
> > > Exec SQL
> > > Create table qtemp/tmptable as (
> > > select * from myfile
> > > where company = '001') with data;
> > >
> > >
> > >
> > >
> > > --
> > >
> > > Michael Schutte
> > > Admin Professional
> > >
> > >
> > >
> > > Try Bob Evans GRILLING SAUSAGE! This summer's hottest destination is
> > >       
> your
>   
> > > own backyard with Bob Evans Brats and Italian Sausage! For tasty
> > >       
> recipes
>   
> > > using Bob Evans grilling sausage, visit http://www.BobEvans.com/Recipes
> > >
> > >
> > >
> > > rpg400-l-bounces@xxxxxxxxxxxx wrote on 05/07/2009 06:47:29 PM:
> > >
> > >
> > >       
> > > > Host variables are used in 'Prepared' statements. The reason for that
> > > >         
> is
>   
> > > as
> > >
> > >       
> > > > the name suggests, they are prepared. ie they are created before they
> > > >         
> are
>   
> > > > run, and when run, the host variables are passed to the already
> > > >         
> prepared
>   
> > > > statement as input values.
> > > > This is basically for performance reasons, as the prepared statement
> > > >         
> can
>   
> > > > then be reused multiple times efficiently
> > > >
> > > >
> > > > 2009/5/7 <Michael_Schutte@xxxxxxxxxxxx>
> > > >
> > > >
> > > >         
> > > > > Okay, I've found where is says that it's not allowed.
> > > > >
> > > > >      The select-statement must not refer to variables or include
> > > > >
> > > > >           
> > > parameter
> > >
> > >       
> > > > > markers.
> > > > >
> > > > >
> > > > > But why, I dont see the explaination why?  I'm not seeing why the
> > > > >
> > > > >           
> > > compiler
> > >
> > >       
> > > > > would care if I use a hard coded value or a host variable to select
> > > > > records.
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > >           
> > --
> > This is the RPG programming on the IBM i / System i (RPG400-L) mailing
> >     
> list
>   
> > To post a message email: RPG400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/rpg400-l.
> >
> >     
> >