Re: Want to use Sybase non-sharable temporary # tables in SQR

[Rebecca Barilleaux <Rebecca.Barilleaux@ANTHEM.COM>]

Vijay,

Here's a basic sample:

Begin-Setup
 Begin-SQL -XP
  CREATE table ##names_table
  (
  first_name  char(15),
  last_name   char(20)
  );
 End-SQL
End-Setup

Begin-Program
 Do LoadNames
 Do PrintReport
 Do CleanUp
End-Program

Begin-Procedure LoadNames
! Load the temp table with all members
! who have the last name of Jones
 Begin-SQL -XP
  insert into ##names_table
  select MEME_FIRST_NAME as first_name,
         MEME_LAST_NAME as last_name
  from rptenv01..CMC_MEME_MEMBER
  where MEME_LAST_NAME = "Jones"
 End-SQL
End-Procedure ! LoadNames

Begin-Procedure PrintReport
Begin-Select
first_name   (+1,5,15)
last_name    (,17,20)
>From  ##names_table
End-Select
End-Procedure ! PrintReport

Begin-Procedure CleanUp
 Begin-SQL -XP
  drop table ##names_table;
 End-SQL
End-Procedure ! CleanUp

Begin-Heading 4
 Print $current-date (1,1) edit 'MM/DD/YYYY'
 Page-Number (1,5)
 Print 'FIRST NAME' (+1,5,15) Underline  Bold
 Print 'LAST NAME'  (,17,20)  Underline  Bold
End-Heading

Rebecca

>Date:    Mon, 25 Jun 2001 09:11:10 -0400
>From:    "Pimple, Vijay C Mr USAREC" <Vijay.Pimple@USAREC.ARMY.MIL>
>Subject: Re: Want to use Sybase non-sharable temporary # tables in  SQR
>MIME-Version: 1.0
>Content-Type: text/plain
>
>Hi Rebecca,
>
>Thanks for your information.
>
>Do you have any example for the same ?
>
>Vijay
>
>> -----Original Message-----
>> From: Rebecca Barilleaux [SMTP:Rebecca.Barilleaux@ANTHEM.COM]
>> Sent: Monday, June 25, 2001 8:09 AM
>> To:   SQR-USERS@list.iex.net
>> Subject:      Re: Want to use Sybase non-sharable temporary # tables in
>> SQR
>>
>> Vijay,
>>
>> Yes, it is possible to use temp tables in SQR.  But the table must be
>> defined at compile time in order for you to use it in your program, so
you
>> need to define the table in the Begin-Setup section using Begin-SQL and
a
>> CREATE TABLE statement.  Then you can either change your select..into
into
>> an insert statement, or you can drop the table before recreating it with
>> your select..into.
>>
>> Rebecca
>>
>>
>> >Date:    Fri, 22 Jun 2001 14:21:41 -0400
>> >From:    "Pimple, Vijay C Mr USAREC" <Vijay.Pimple@USAREC.ARMY.MIL>
>> >Subject: Want to use Sybase non-sharable temporary # tables in  SQR
>> >MIME-Version: 1.0
>> >Content-Type: text/plain; charset="iso-8859-1"
>> >
>> >Hi all,
>> >
>> >I am running SQR against sybase 11.9.2 database and I am testing
whether
>> I
>> >can use sybase non-sharable temporary # tables in Begin-sql section and
>> >select columns from the # tables in BEGIN-SELECT section.
>> >
>> >I created the following sqr report and I am getting compile error -
>> >
>> >Begin-Program
>> > Position (1,1)
>> > Do Master_Query
>> >End-Program
>> >
>> >Begin-Procedure Master_Query
>> >begin-sql
>> >select sname, etable into #vptest from dbo.adp_t
>> >go
>> >end-sql
>> >Begin-Select
>> >sname   (+1,5,15)
>> >etable  (,17,20)
>> >From  #vptest
>> >End-Select
>> >End-Procedure
>> >
>> >Begin-Heading 4
>> > Print $current-date (1,1) edit 'MM/DD/YYYY'
>> > Page-Number (1,5)
>> > Print 'SNAME' (+1,1,5) Underline  Bold
>> > Print 'ETABLE' (,17,8) Underline  Bold
>> >End-Heading
>> >
>> >I can call stored procedure in begin-sql and within stored procedure I
>> can
>> >create #tables but I cannot access the same table in Begin-select
>> section.
>> >
>> >Is it possible to create #temporary tables in Begin-sql section and use
>> in
>> >begin-select section ?
>> >
>> >
>> >Please advise me.
>> >
>> >Regards,
>> >
>> >Vijay
>>
>>
>> CONFIDENTIALITY NOTICE: This e-mail message, including any attachments,
is
>> for the sole use of the intended recipient(s) and may contain
confidential
>> and privileged information.  Any unauthorized review, use, disclosure or
>> distribution is prohibited.  If you are not the intended recipient,
please
>> contact the sender by reply e-mail and destroy all copies of the
original
>> message.



CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is
for the sole use of the intended recipient(s) and may contain confidential
and privileged information.  Any unauthorized review, use, disclosure or
distribution is prohibited.  If you are not the intended recipient, please
contact the sender by reply e-mail and destroy all copies of the original
message.