Re: SQL in SQR

["Wagner, Jennifer A" <WAGNERJ@CDER.FDA.GOV>]

Well, I've tried various and assorted things... this is the most recent one
I tried:

BEGIN-SQL  ON-ERROR=SKIP

  CREATE TABLE PMCGEN_TEMP (GENERIC_NAME    VARCHAR2(40)),
  create table pmcapp_temp (appl_type     varchar2(1),
                            appl_no       varchar2(6))
     TABLESPACE USER_ACCOUNTS
        STORAGE
        (INITIAL     5000K
         NEXT         500K);
end-sql
end-setup


I also tried it without the comma separating the two create tables and I
tried it with the semi-colon after the first table.  Also I tried this:

BEGIN-SQL  ON-ERROR=SKIP


  CREATE TABLE PMCGEN_TEMP (GENERIC_NAME    VARCHAR2(40))
     TABLESPACE USER_ACCOUNTS
        STORAGE
        (INITIAL     5000K
         NEXT         500K);

  create table pmcapp_temp (appl_type     varchar2(1),
                            appl_no       varchar2(6))
     TABLESPACE USER_ACCOUNTS
        STORAGE
        (INITIAL     5000K
         NEXT         500K);

end-sql

Each time it seems to create the first but the second comes up with the
error:  table or view does not exist when it goes to do the insert.


Thanks,


Jenny

> -----Original Message-----
> From: the dragon [mailto:ceprn@hotmail.com]
> Sent: Thursday, April 05, 2001 10:42 AM
> To: sqr-users@list.iex.net
> Cc: WAGNERJ@CDER.FDA.GOV
> Subject: Re: SQL in SQR
>
>
> show us the code you're attempting to use to create multiple tables.
>
> clark 'the dragon' willis
> dragon enterprises, consulting services
>
>
> ----Original Message Follows----
>
> I'm trying to create temp tables in my SQR program.  It will
> allow me to
> create one, but I can't seem to get it to create more than
> one.  My ultimate
> goal would be to have a a multi-module program where I select
> different
> things from different tables and then insert them into the
> various temp
> tables.
>
> Do you know if you can create more than one temp table in a
> SQL paragraph in
> SQR?
>
> My temp table create statement looks like this:
>
> begin-setup
> page-size 56 132
>
> BEGIN-SQL  ON-ERROR=SKIP
>
>    CREATE TABLE PMCGEN_TEMP (GENERIC_NAME    VARCHAR2(40))
>       TABLESPACE USER_ACCOUNTS
>          STORAGE
>          (INITIAL     5000K
>           NEXT         500K);
> end-sql
> end-setup
>
> and the insert statement looks like this:
>
> begin-procedure INSERT_TEMP
> BEGIN-SQL
> INSERT INTO PMCGEN_TEMP (GENERIC_NAME)
> values (&GEN);
> END-SQL
> COMMIT
> end-procedure
>
>
> That works fine.  But, I've tried to add another create
> table... and insert
> into... and I get the message:  "table for view does not
> exist" for the
> second table.
>
>
> Thanks,
>
>
> Jenny
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.msn.com
>
>