Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Can I create dynamic local temprary table.

10 posts in General Discussion Last posting was on 2004-02-26 21:03:14.0Z
Chandresh Patel Posted on 2004-02-25 23:22:07.0Z
From: "Chandresh Patel" <bapashree@yahoo.com>
Newsgroups: ianywhere.public.general
Subject: Can I create dynamic local temprary table.
Lines: 17
Organization: Shreeji Smart Solution
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: ac93c88f.ipt.aol.com
X-Original-NNTP-Posting-Host: ac93c88f.ipt.aol.com
Message-ID: <403d2e1f$1@forums-1-dub>
Date: 25 Feb 2004 15:22:07 -0800
X-Trace: forums-1-dub 1077751327 172.147.200.143 (25 Feb 2004 15:22:07 -0800)
X-Original-Trace: 25 Feb 2004 15:22:07 -0800, ac93c88f.ipt.aol.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2636
Article PK: 6112

Sybase Anywhere 9.0.0.1108

In stored procedure at run time i know how many field I need.

DECLARE LOCAL TEMPORARY TABLE
ReportData(
Here I want to supply dynamic value some time 5 column some time 12 and some
time 20 we know at run time
)
NOT TRANSACTIONAL;

Does anybudy knows how to create this table using dynamic column value.

Thanks in Advance.


Chris Keating (iAnywhere Solutions) Posted on 2004-02-26 00:54:37.0Z
From: "Chris Keating \(iAnywhere Solutions\)" <FightSpam_keating@iAnywhere.com>
Newsgroups: ianywhere.public.general
References: <403d2e1f$1@forums-1-dub>
Subject: Re: Can I create dynamic local temprary table.
Lines: 51
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: vpn-concord-060.sybase.com
X-Original-NNTP-Posting-Host: vpn-concord-060.sybase.com
Message-ID: <403d43cd$1@forums-1-dub>
Date: 25 Feb 2004 16:54:37 -0800
X-Trace: forums-1-dub 1077756877 158.159.8.60 (25 Feb 2004 16:54:37 -0800)
X-Original-Trace: 25 Feb 2004 16:54:37 -0800, vpn-concord-060.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2639
Article PK: 6109

See SELECT statement, Transact-SQL. You can use SELECT INTO # {temptable}.

--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

****************************************************************************
*
Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
and try out the market-leading database for mobile, embedded and small to
medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

****************************************************************************
*

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use CaseXpress to report bugs http://casexpress.sybase.com

****************************************************************************
*

"Chandresh Patel" <bapashree@yahoo.com> wrote in message
news:403d2e1f$1@forums-1-dub...
> Sybase Anywhere 9.0.0.1108
>
> In stored procedure at run time i know how many field I need.
>
> DECLARE LOCAL TEMPORARY TABLE
> ReportData(
> Here I want to supply dynamic value some time 5 column some time 12 and
some
> time 20 we know at run time
> )
> NOT TRANSACTIONAL;
>
> Does anybudy knows how to create this table using dynamic column value.
>
> Thanks in Advance.
>
>
>


Chandresh Patel Posted on 2004-02-26 04:35:57.0Z
From: "Chandresh Patel" <bapashree@yahoo.com>
Newsgroups: ianywhere.public.general
References: <403d2e1f$1@forums-1-dub> <403d43cd$1@forums-1-dub>
Subject: Re: Can I create dynamic local temprary table.
Lines: 84
Organization: Shreeji Smart Solution
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: ac945acf.ipt.aol.com
Message-ID: <403d77bf@forums-2-dub>
X-Original-Trace: 25 Feb 2004 20:36:15 -0800, ac945acf.ipt.aol.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 25 Feb 2004 20:35:17 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 25 Feb 2004 20:35:57 -0800
X-Trace: forums-1-dub 1077770157 10.22.108.75 (25 Feb 2004 20:35:57 -0800)
X-Original-Trace: 25 Feb 2004 20:35:57 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2644
Article PK: 6119

I use this command in Interactive SQL like

SELECT StoreName INTO #CompSalesData from StoreInfo where StoreID IN (select
storeID from storegroup where StoreGroupID=2)
go
SELECT * from #CompSalesData
go

Result is

StoreName
'BRYNMAWR'
'WEST CHESTER'
'WEST GHOSHEN'
'GLENDORA'
'BELLMAWR'

this create one column name "StoreName" and store all result in to this
column (Result is 5 diffrent store name) and what I want is to create 5
column using this store name.


Thanks in Advance



"Chris Keating (iAnywhere Solutions)" <FightSpam_keating@iAnywhere.com>

wrote in message news:403d43cd$1@forums-1-dub...
> See SELECT statement, Transact-SQL. You can use SELECT INTO # {temptable}.
>
> --
>
> Chris Keating
> Sybase Adaptive Server Anywhere Professional Version 8
>
>
****************************************************************************
> *
> Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
> and try out the market-leading database for mobile, embedded and small to
> medium sized business environments for free!
>
> http://www.ianywhere.com/promos/deved/index.html
>
>
****************************************************************************
> *
>
> iAnywhere Solutions http://www.iAnywhere.com
>
> ** Please only post to the newsgroup
>
> ** Whitepapers can be found at http://www.iAnywhere.com/developer
> ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
> ** Use CaseXpress to report bugs http://casexpress.sybase.com
>
>
****************************************************************************
> *
>
> "Chandresh Patel" <bapashree@yahoo.com> wrote in message
> news:403d2e1f$1@forums-1-dub...
> > Sybase Anywhere 9.0.0.1108
> >
> > In stored procedure at run time i know how many field I need.
> >
> > DECLARE LOCAL TEMPORARY TABLE
> > ReportData(
> > Here I want to supply dynamic value some time 5 column some time 12 and
> some
> > time 20 we know at run time
> > )
> > NOT TRANSACTIONAL;
> >
> > Does anybudy knows how to create this table using dynamic column value.
> >
> > Thanks in Advance.
> >
> >
> >
>
>


Chris Keating (iAnywhere Solutions) Posted on 2004-02-26 05:17:20.0Z
From: "Chris Keating \(iAnywhere Solutions\)" <FightSpam_keating@iAnywhere.com>
Newsgroups: ianywhere.public.general
References: <403d2e1f$1@forums-1-dub> <403d43cd$1@forums-1-dub> <403d77bf@forums-2-dub>
Subject: Re: Can I create dynamic local temprary table.
Lines: 140
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: vpn-concord-060.sybase.com
Message-ID: <403d8172@forums-2-dub>
X-Original-Trace: 25 Feb 2004 21:17:38 -0800, vpn-concord-060.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 25 Feb 2004 21:16:39 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 25 Feb 2004 21:17:20 -0800
X-Trace: forums-1-dub 1077772640 10.22.108.75 (25 Feb 2004 21:17:20 -0800)
X-Original-Trace: 25 Feb 2004 21:17:20 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2647
Article PK: 6121

This is a far different requirement that that defined in your original post.
One option is to create the sql statement that creates the local temp table
dynamically and using EXECUTE IMMEDIATE once the statement is build. An
example algorithm is:

- determine the required columns
- Initialize the create table sql statement based on the syntax "DECLARE
LOCAL TEMPORARY TABLE table-name ( "
- for each column
- add syntax to define column name and properties (i.e., datatype)
- add appropriate separator (comma if more columns exist or a
closing parenthesis for last column
- Complete the sql statement with table options ( i.e., ON COMMIT....)
- Execute the statement to create the table.


--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

****************************************************************************
*
Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
and try out the market-leading database for mobile, embedded and small to
medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

****************************************************************************
*

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use CaseXpress to report bugs http://casexpress.sybase.com

****************************************************************************
*

"Chandresh Patel" <bapashree@yahoo.com> wrote in message
news:403d77bf@forums-2-dub...
> I use this command in Interactive SQL like
>
> SELECT StoreName INTO #CompSalesData from StoreInfo where StoreID IN
(select
> storeID from storegroup where StoreGroupID=2)
> go
> SELECT * from #CompSalesData
> go
>
> Result is
>
> StoreName
> 'BRYNMAWR'
> 'WEST CHESTER'
> 'WEST GHOSHEN'
> 'GLENDORA'
> 'BELLMAWR'
>
> this create one column name "StoreName" and store all result in to this
> column (Result is 5 diffrent store name) and what I want is to create 5
> column using this store name.
>
>
> Thanks in Advance
>
>
>
> "Chris Keating (iAnywhere Solutions)" <FightSpam_keating@iAnywhere.com>
> wrote in message news:403d43cd$1@forums-1-dub...
> > See SELECT statement, Transact-SQL. You can use SELECT INTO #
{temptable}.
> >
> > --
> >
> > Chris Keating
> > Sybase Adaptive Server Anywhere Professional Version 8
> >
> >
>
****************************************************************************
> > *
> > Sign up today for your copy of the SQL Anywhere Studio 9 Developer
Edition
> > and try out the market-leading database for mobile, embedded and small
to
> > medium sized business environments for free!
> >
> > http://www.ianywhere.com/promos/deved/index.html
> >
> >
>
****************************************************************************
> > *
> >
> > iAnywhere Solutions http://www.iAnywhere.com
> >
> > ** Please only post to the newsgroup
> >
> > ** Whitepapers can be found at http://www.iAnywhere.com/developer
> > ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
> > ** Use CaseXpress to report bugs http://casexpress.sybase.com
> >
> >
>
****************************************************************************
> > *
> >
> > "Chandresh Patel" <bapashree@yahoo.com> wrote in message
> > news:403d2e1f$1@forums-1-dub...
> > > Sybase Anywhere 9.0.0.1108
> > >
> > > In stored procedure at run time i know how many field I need.
> > >
> > > DECLARE LOCAL TEMPORARY TABLE
> > > ReportData(
> > > Here I want to supply dynamic value some time 5 column some time 12
and
> > some
> > > time 20 we know at run time
> > > )
> > > NOT TRANSACTIONAL;
> > >
> > > Does anybudy knows how to create this table using dynamic column
value.
> > >
> > > Thanks in Advance.
> > >
> > >
> > >
> >
> >
>
>


Chandresh Patel Posted on 2004-02-26 19:13:46.0Z
From: "Chandresh Patel" <bapashree@yahoo.com>
Newsgroups: ianywhere.public.general
References: <403d2e1f$1@forums-1-dub> <403d43cd$1@forums-1-dub> <403d77bf@forums-2-dub> <403d8172@forums-2-dub>
Subject: Re: Can I create dynamic local temprary table.
Lines: 184
Organization: Shreeji Smart Solution
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: aca0500d.ipt.aol.com
X-Original-NNTP-Posting-Host: aca0500d.ipt.aol.com
Message-ID: <403e456a@forums-1-dub>
Date: 26 Feb 2004 11:13:46 -0800
X-Trace: forums-1-dub 1077822826 172.160.80.13 (26 Feb 2004 11:13:46 -0800)
X-Original-Trace: 26 Feb 2004 11:13:46 -0800, aca0500d.ipt.aol.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2660
Article PK: 6133

Here is my code please help me....

ALTER PROCEDURE "DBA"."GetSalesReportData"(IN mSGroupID Integer, IN
ReportType TinyInt, IN sDate DATE, IN eDate DATE, IN cID Integer)
BEGIN
declare err_notfound exception for sqlstate value '02000';
declare mStoreName varchar(40);
declare i integer;

declare crsr cursor for call GetStoreName(mSGroupID);
open crsr;

DECLARE LOCAL TEMPORARY TABLE SalesData
(
MyLoop: loop
fetch next crsr into mStoreName;

if sqlstate = err_notfound then
leave MyLoop
else
select mStoreName, VARCHAR(30) ???
end if;

end loop MyLoop;
) NOT TRANSACTIONAL;
close crsr
END


Thanks in Advance.


"Chris Keating (iAnywhere Solutions)" <FightSpam_keating@iAnywhere.com>

wrote in message news:403d8172@forums-2-dub...
> This is a far different requirement that that defined in your original
post.
> One option is to create the sql statement that creates the local temp
table
> dynamically and using EXECUTE IMMEDIATE once the statement is build. An
> example algorithm is:
>
> - determine the required columns
> - Initialize the create table sql statement based on the syntax "DECLARE
> LOCAL TEMPORARY TABLE table-name ( "
> - for each column
> - add syntax to define column name and properties (i.e., datatype)
> - add appropriate separator (comma if more columns exist or a
> closing parenthesis for last column
> - Complete the sql statement with table options ( i.e., ON COMMIT....)
> - Execute the statement to create the table.
>
>
> --
>
> Chris Keating
> Sybase Adaptive Server Anywhere Professional Version 8
>
>
****************************************************************************
> *
> Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
> and try out the market-leading database for mobile, embedded and small to
> medium sized business environments for free!
>
> http://www.ianywhere.com/promos/deved/index.html
>
>
****************************************************************************
> *
>
> iAnywhere Solutions http://www.iAnywhere.com
>
> ** Please only post to the newsgroup
>
> ** Whitepapers can be found at http://www.iAnywhere.com/developer
> ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
> ** Use CaseXpress to report bugs http://casexpress.sybase.com
>
>
****************************************************************************
> *
>
> "Chandresh Patel" <bapashree@yahoo.com> wrote in message
> news:403d77bf@forums-2-dub...
> > I use this command in Interactive SQL like
> >
> > SELECT StoreName INTO #CompSalesData from StoreInfo where StoreID IN
> (select
> > storeID from storegroup where StoreGroupID=2)
> > go
> > SELECT * from #CompSalesData
> > go
> >
> > Result is
> >
> > StoreName
> > 'BRYNMAWR'
> > 'WEST CHESTER'
> > 'WEST GHOSHEN'
> > 'GLENDORA'
> > 'BELLMAWR'
> >
> > this create one column name "StoreName" and store all result in to this
> > column (Result is 5 diffrent store name) and what I want is to create 5
> > column using this store name.
> >
> >
> > Thanks in Advance
> >
> >
> >
> > "Chris Keating (iAnywhere Solutions)" <FightSpam_keating@iAnywhere.com>
> > wrote in message news:403d43cd$1@forums-1-dub...
> > > See SELECT statement, Transact-SQL. You can use SELECT INTO #
> {temptable}.
> > >
> > > --
> > >
> > > Chris Keating
> > > Sybase Adaptive Server Anywhere Professional Version 8
> > >
> > >
> >
>
****************************************************************************
> > > *
> > > Sign up today for your copy of the SQL Anywhere Studio 9 Developer
> Edition
> > > and try out the market-leading database for mobile, embedded and small
> to
> > > medium sized business environments for free!
> > >
> > > http://www.ianywhere.com/promos/deved/index.html
> > >
> > >
> >
>
****************************************************************************
> > > *
> > >
> > > iAnywhere Solutions http://www.iAnywhere.com
> > >
> > > ** Please only post to the newsgroup
> > >
> > > ** Whitepapers can be found at http://www.iAnywhere.com/developer
> > > ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
> > > ** Use CaseXpress to report bugs http://casexpress.sybase.com
> > >
> > >
> >
>
****************************************************************************
> > > *
> > >
> > > "Chandresh Patel" <bapashree@yahoo.com> wrote in message
> > > news:403d2e1f$1@forums-1-dub...
> > > > Sybase Anywhere 9.0.0.1108
> > > >
> > > > In stored procedure at run time i know how many field I need.
> > > >
> > > > DECLARE LOCAL TEMPORARY TABLE
> > > > ReportData(
> > > > Here I want to supply dynamic value some time 5 column some time 12
> and
> > > some
> > > > time 20 we know at run time
> > > > )
> > > > NOT TRANSACTIONAL;
> > > >
> > > > Does anybudy knows how to create this table using dynamic column
> value.
> > > >
> > > > Thanks in Advance.
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


Chandresh Patel Posted on 2004-02-26 19:52:41.0Z
From: "Chandresh Patel" <bapashree@yahoo.com>
Newsgroups: ianywhere.public.general
References: <403d2e1f$1@forums-1-dub> <403d43cd$1@forums-1-dub> <403d77bf@forums-2-dub> <403d8172@forums-2-dub>
Subject: Here i build string but don't know how to use it
Lines: 197
Organization: Shreeji Smart Solution
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: aca0500d.ipt.aol.com
Message-ID: <403e4e9e@forums-2-dub>
X-Original-Trace: 26 Feb 2004 11:53:02 -0800, aca0500d.ipt.aol.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 26 Feb 2004 11:51:57 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 26 Feb 2004 11:52:41 -0800
X-Trace: forums-1-dub 1077825161 10.22.108.75 (26 Feb 2004 11:52:41 -0800)
X-Original-Trace: 26 Feb 2004 11:52:41 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2661
Article PK: 6135

I create one mFieldData string but I don't know how to use it in Declare
temarary table statement.

ALTER PROCEDURE "DBA"."GetSalesReportData"(IN mSGroupID Integer, IN
ReportType TinyInt, IN sDate DATE, IN eDate DATE, IN cID Integer)
BEGIN
declare err_notfound exception for sqlstate value '02000';
declare mStoreName varchar(40);
declare mFieldData long varchar;

declare crsr cursor for call GetStoreName(mSGroupID);
open crsr;

MyLoop: loop
fetch next crsr into mStoreName;

if sqlstate = err_notfound then
leave MyLoop
else
select mStorName;

if mFieldData = '' THEN
SET mFieldData = STRING (sName,' VARCHAR(30)');
else
SET mFieldData = STRING (mFieldData,', ',sName,'
VARCHAR(30)');
end if;

end if;

end loop MyLoop;

close crsr

//Here I don't know how to use this string to build this table.
?????????
DECLARE LOCAL TEMPORARY TABLE SalesData('mFieldData') NOT TRANSACTIONAL;

END



Thanks in Advance.


"Chris Keating (iAnywhere Solutions)" <FightSpam_keating@iAnywhere.com>

wrote in message news:403d8172@forums-2-dub...
> This is a far different requirement that that defined in your original
post.
> One option is to create the sql statement that creates the local temp
table
> dynamically and using EXECUTE IMMEDIATE once the statement is build. An
> example algorithm is:
>
> - determine the required columns
> - Initialize the create table sql statement based on the syntax "DECLARE
> LOCAL TEMPORARY TABLE table-name ( "
> - for each column
> - add syntax to define column name and properties (i.e., datatype)
> - add appropriate separator (comma if more columns exist or a
> closing parenthesis for last column
> - Complete the sql statement with table options ( i.e., ON COMMIT....)
> - Execute the statement to create the table.
>
>
> --
>
> Chris Keating
> Sybase Adaptive Server Anywhere Professional Version 8
>
>
****************************************************************************
> *
> Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
> and try out the market-leading database for mobile, embedded and small to
> medium sized business environments for free!
>
> http://www.ianywhere.com/promos/deved/index.html
>
>
****************************************************************************
> *
>
> iAnywhere Solutions http://www.iAnywhere.com
>
> ** Please only post to the newsgroup
>
> ** Whitepapers can be found at http://www.iAnywhere.com/developer
> ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
> ** Use CaseXpress to report bugs http://casexpress.sybase.com
>
>
****************************************************************************
> *
>
> "Chandresh Patel" <bapashree@yahoo.com> wrote in message
> news:403d77bf@forums-2-dub...
> > I use this command in Interactive SQL like
> >
> > SELECT StoreName INTO #CompSalesData from StoreInfo where StoreID IN
> (select
> > storeID from storegroup where StoreGroupID=2)
> > go
> > SELECT * from #CompSalesData
> > go
> >
> > Result is
> >
> > StoreName
> > 'BRYNMAWR'
> > 'WEST CHESTER'
> > 'WEST GHOSHEN'
> > 'GLENDORA'
> > 'BELLMAWR'
> >
> > this create one column name "StoreName" and store all result in to this
> > column (Result is 5 diffrent store name) and what I want is to create 5
> > column using this store name.
> >
> >
> > Thanks in Advance
> >
> >
> >
> > "Chris Keating (iAnywhere Solutions)" <FightSpam_keating@iAnywhere.com>
> > wrote in message news:403d43cd$1@forums-1-dub...
> > > See SELECT statement, Transact-SQL. You can use SELECT INTO #
> {temptable}.
> > >
> > > --
> > >
> > > Chris Keating
> > > Sybase Adaptive Server Anywhere Professional Version 8
> > >
> > >
> >
>
****************************************************************************
> > > *
> > > Sign up today for your copy of the SQL Anywhere Studio 9 Developer
> Edition
> > > and try out the market-leading database for mobile, embedded and small
> to
> > > medium sized business environments for free!
> > >
> > > http://www.ianywhere.com/promos/deved/index.html
> > >
> > >
> >
>
****************************************************************************
> > > *
> > >
> > > iAnywhere Solutions http://www.iAnywhere.com
> > >
> > > ** Please only post to the newsgroup
> > >
> > > ** Whitepapers can be found at http://www.iAnywhere.com/developer
> > > ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
> > > ** Use CaseXpress to report bugs http://casexpress.sybase.com
> > >
> > >
> >
>
****************************************************************************
> > > *
> > >
> > > "Chandresh Patel" <bapashree@yahoo.com> wrote in message
> > > news:403d2e1f$1@forums-1-dub...
> > > > Sybase Anywhere 9.0.0.1108
> > > >
> > > > In stored procedure at run time i know how many field I need.
> > > >
> > > > DECLARE LOCAL TEMPORARY TABLE
> > > > ReportData(
> > > > Here I want to supply dynamic value some time 5 column some time 12
> and
> > > some
> > > > time 20 we know at run time
> > > > )
> > > > NOT TRANSACTIONAL;
> > > >
> > > > Does anybudy knows how to create this table using dynamic column
> value.
> > > >
> > > > Thanks in Advance.
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


Chris Keating (iAnywhere Solutions) Posted on 2004-02-26 20:10:08.0Z
From: "Chris Keating \(iAnywhere Solutions\)" <FightSpam_keating@iAnywhere.com>
Newsgroups: ianywhere.public.general
References: <403d2e1f$1@forums-1-dub> <403d43cd$1@forums-1-dub> <403d77bf@forums-2-dub> <403d8172@forums-2-dub> <403e4e9e@forums-2-dub>
Subject: Re: Here i build string but don't know how to use it
Lines: 252
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: keating-xp.sybase.com
Message-ID: <403e52b4$1@forums-2-dub>
X-Original-Trace: 26 Feb 2004 12:10:28 -0800, keating-xp.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 26 Feb 2004 12:09:24 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 26 Feb 2004 12:10:08 -0800
X-Trace: forums-1-dub 1077826208 10.22.108.75 (26 Feb 2004 12:10:08 -0800)
X-Original-Trace: 26 Feb 2004 12:10:08 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2663
Article PK: 6138

use string concatenation to build the string. Obvously

i.e.,
set sqlStmt = String( strDeclareSyntax, strColumnSyntax,
strNotTransactionSmt );
execute immediate sqlStmt;



--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

****************************************************************************
*
Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
and try out the market-leading database for mobile, embedded and small to
medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

****************************************************************************
*

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use CaseXpress to report bugs http://casexpress.sybase.com

****************************************************************************
*

"Chandresh Patel" <bapashree@yahoo.com> wrote in message
news:403e4e9e@forums-2-dub...
> I create one mFieldData string but I don't know how to use it in Declare
> temarary table statement.
>
> ALTER PROCEDURE "DBA"."GetSalesReportData"(IN mSGroupID Integer, IN
> ReportType TinyInt, IN sDate DATE, IN eDate DATE, IN cID Integer)
> BEGIN
> declare err_notfound exception for sqlstate value '02000';
> declare mStoreName varchar(40);
> declare mFieldData long varchar;
>
> declare crsr cursor for call GetStoreName(mSGroupID);
> open crsr;
>
> MyLoop: loop
> fetch next crsr into mStoreName;
>
> if sqlstate = err_notfound then
> leave MyLoop
> else
> select mStorName;
>
> if mFieldData = '' THEN
> SET mFieldData = STRING (sName,' VARCHAR(30)');
> else
> SET mFieldData = STRING (mFieldData,', ',sName,'
> VARCHAR(30)');
> end if;
>
> end if;
>
> end loop MyLoop;
>
> close crsr
>
> //Here I don't know how to use this string to build this table.
> ?????????
> DECLARE LOCAL TEMPORARY TABLE SalesData('mFieldData') NOT
TRANSACTIONAL;
>
> END
>
>
>
> Thanks in Advance.
>
>
> "Chris Keating (iAnywhere Solutions)" <FightSpam_keating@iAnywhere.com>
> wrote in message news:403d8172@forums-2-dub...
> > This is a far different requirement that that defined in your original
> post.
> > One option is to create the sql statement that creates the local temp
> table
> > dynamically and using EXECUTE IMMEDIATE once the statement is build. An
> > example algorithm is:
> >
> > - determine the required columns
> > - Initialize the create table sql statement based on the syntax "DECLARE
> > LOCAL TEMPORARY TABLE table-name ( "
> > - for each column
> > - add syntax to define column name and properties (i.e.,
datatype)
> > - add appropriate separator (comma if more columns exist or a
> > closing parenthesis for last column
> > - Complete the sql statement with table options ( i.e., ON COMMIT....)
> > - Execute the statement to create the table.
> >
> >
> > --
> >
> > Chris Keating
> > Sybase Adaptive Server Anywhere Professional Version 8
> >
> >
>
****************************************************************************
> > *
> > Sign up today for your copy of the SQL Anywhere Studio 9 Developer
Edition
> > and try out the market-leading database for mobile, embedded and small
to
> > medium sized business environments for free!
> >
> > http://www.ianywhere.com/promos/deved/index.html
> >
> >
>
****************************************************************************
> > *
> >
> > iAnywhere Solutions http://www.iAnywhere.com
> >
> > ** Please only post to the newsgroup
> >
> > ** Whitepapers can be found at http://www.iAnywhere.com/developer
> > ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
> > ** Use CaseXpress to report bugs http://casexpress.sybase.com
> >
> >
>
****************************************************************************
> > *
> >
> > "Chandresh Patel" <bapashree@yahoo.com> wrote in message
> > news:403d77bf@forums-2-dub...
> > > I use this command in Interactive SQL like
> > >
> > > SELECT StoreName INTO #CompSalesData from StoreInfo where StoreID IN
> > (select
> > > storeID from storegroup where StoreGroupID=2)
> > > go
> > > SELECT * from #CompSalesData
> > > go
> > >
> > > Result is
> > >
> > > StoreName
> > > 'BRYNMAWR'
> > > 'WEST CHESTER'
> > > 'WEST GHOSHEN'
> > > 'GLENDORA'
> > > 'BELLMAWR'
> > >
> > > this create one column name "StoreName" and store all result in to
this
> > > column (Result is 5 diffrent store name) and what I want is to create
5
> > > column using this store name.
> > >
> > >
> > > Thanks in Advance
> > >
> > >
> > >
> > > "Chris Keating (iAnywhere Solutions)"
<FightSpam_keating@iAnywhere.com>
> > > wrote in message news:403d43cd$1@forums-1-dub...
> > > > See SELECT statement, Transact-SQL. You can use SELECT INTO #
> > {temptable}.
> > > >
> > > > --
> > > >
> > > > Chris Keating
> > > > Sybase Adaptive Server Anywhere Professional Version 8
> > > >
> > > >
> > >
> >
>
****************************************************************************
> > > > *
> > > > Sign up today for your copy of the SQL Anywhere Studio 9 Developer
> > Edition
> > > > and try out the market-leading database for mobile, embedded and
small
> > to
> > > > medium sized business environments for free!
> > > >
> > > > http://www.ianywhere.com/promos/deved/index.html
> > > >
> > > >
> > >
> >
>
****************************************************************************
> > > > *
> > > >
> > > > iAnywhere Solutions http://www.iAnywhere.com
> > > >
> > > > ** Please only post to the newsgroup
> > > >
> > > > ** Whitepapers can be found at http://www.iAnywhere.com/developer
> > > > ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
> > > > ** Use CaseXpress to report bugs http://casexpress.sybase.com
> > > >
> > > >
> > >
> >
>
****************************************************************************
> > > > *
> > > >
> > > > "Chandresh Patel" <bapashree@yahoo.com> wrote in message
> > > > news:403d2e1f$1@forums-1-dub...
> > > > > Sybase Anywhere 9.0.0.1108
> > > > >
> > > > > In stored procedure at run time i know how many field I need.
> > > > >
> > > > > DECLARE LOCAL TEMPORARY TABLE
> > > > > ReportData(
> > > > > Here I want to supply dynamic value some time 5 column some time
12
> > and
> > > > some
> > > > > time 20 we know at run time
> > > > > )
> > > > > NOT TRANSACTIONAL;
> > > > >
> > > > > Does anybudy knows how to create this table using dynamic column
> > value.
> > > > >
> > > > > Thanks in Advance.
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


Chris Keating (iAnywhere Solutions) Posted on 2004-02-26 20:13:06.0Z
From: "Chris Keating \(iAnywhere Solutions\)" <FightSpam_keating@iAnywhere.com>
Newsgroups: ianywhere.public.general
References: <403d2e1f$1@forums-1-dub> <403d43cd$1@forums-1-dub> <403d77bf@forums-2-dub> <403d8172@forums-2-dub> <403e4e9e@forums-2-dub> <403e52b4$1@forums-2-dub>
Subject: Re: Here i build string but don't know how to use it
Lines: 299
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: keating-xp.sybase.com
Message-ID: <403e5367@forums-2-dub>
X-Original-Trace: 26 Feb 2004 12:13:27 -0800, keating-xp.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 26 Feb 2004 12:12:22 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 26 Feb 2004 12:13:06 -0800
X-Trace: forums-1-dub 1077826386 10.22.108.75 (26 Feb 2004 12:13:06 -0800)
X-Original-Trace: 26 Feb 2004 12:13:06 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2664
Article PK: 6136

Let me finish the statement that ended with Obviously....

"Obviously, the final string must be a valid sql statement"

--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

****************************************************************************
*
Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
and try out the market-leading database for mobile, embedded and small to
medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

****************************************************************************
*

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use CaseXpress to report bugs http://casexpress.sybase.com

****************************************************************************
*

"Chris Keating (iAnywhere Solutions)" <FightSpam_keating@iAnywhere.com>

wrote in message news:403e52b4$1@forums-2-dub...
> use string concatenation to build the string. Obvously
>
> i.e.,
> set sqlStmt = String( strDeclareSyntax, strColumnSyntax,
> strNotTransactionSmt );
> execute immediate sqlStmt;
>
>
>
> --
>
> Chris Keating
> Sybase Adaptive Server Anywhere Professional Version 8
>
>
****************************************************************************
> *
> Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
> and try out the market-leading database for mobile, embedded and small to
> medium sized business environments for free!
>
> http://www.ianywhere.com/promos/deved/index.html
>
>
****************************************************************************
> *
>
> iAnywhere Solutions http://www.iAnywhere.com
>
> ** Please only post to the newsgroup
>
> ** Whitepapers can be found at http://www.iAnywhere.com/developer
> ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
> ** Use CaseXpress to report bugs http://casexpress.sybase.com
>
>
****************************************************************************
> *
>
> "Chandresh Patel" <bapashree@yahoo.com> wrote in message
> news:403e4e9e@forums-2-dub...
> > I create one mFieldData string but I don't know how to use it in Declare
> > temarary table statement.
> >
> > ALTER PROCEDURE "DBA"."GetSalesReportData"(IN mSGroupID Integer, IN
> > ReportType TinyInt, IN sDate DATE, IN eDate DATE, IN cID Integer)
> > BEGIN
> > declare err_notfound exception for sqlstate value '02000';
> > declare mStoreName varchar(40);
> > declare mFieldData long varchar;
> >
> > declare crsr cursor for call GetStoreName(mSGroupID);
> > open crsr;
> >
> > MyLoop: loop
> > fetch next crsr into mStoreName;
> >
> > if sqlstate = err_notfound then
> > leave MyLoop
> > else
> > select mStorName;
> >
> > if mFieldData = '' THEN
> > SET mFieldData = STRING (sName,' VARCHAR(30)');
> > else
> > SET mFieldData = STRING (mFieldData,', ',sName,'
> > VARCHAR(30)');
> > end if;
> >
> > end if;
> >
> > end loop MyLoop;
> >
> > close crsr
> >
> > //Here I don't know how to use this string to build this table.
> > ?????????
> > DECLARE LOCAL TEMPORARY TABLE SalesData('mFieldData') NOT
> TRANSACTIONAL;
> >
> > END
> >
> >
> >
> > Thanks in Advance.
> >
> >
> > "Chris Keating (iAnywhere Solutions)" <FightSpam_keating@iAnywhere.com>
> > wrote in message news:403d8172@forums-2-dub...
> > > This is a far different requirement that that defined in your original
> > post.
> > > One option is to create the sql statement that creates the local temp
> > table
> > > dynamically and using EXECUTE IMMEDIATE once the statement is build.
An
> > > example algorithm is:
> > >
> > > - determine the required columns
> > > - Initialize the create table sql statement based on the syntax
"DECLARE
> > > LOCAL TEMPORARY TABLE table-name ( "
> > > - for each column
> > > - add syntax to define column name and properties (i.e.,
> datatype)
> > > - add appropriate separator (comma if more columns exist or a
> > > closing parenthesis for last column
> > > - Complete the sql statement with table options ( i.e., ON COMMIT....)
> > > - Execute the statement to create the table.
> > >
> > >
> > > --
> > >
> > > Chris Keating
> > > Sybase Adaptive Server Anywhere Professional Version 8
> > >
> > >
> >
>
****************************************************************************
> > > *
> > > Sign up today for your copy of the SQL Anywhere Studio 9 Developer
> Edition
> > > and try out the market-leading database for mobile, embedded and small
> to
> > > medium sized business environments for free!
> > >
> > > http://www.ianywhere.com/promos/deved/index.html
> > >
> > >
> >
>
****************************************************************************
> > > *
> > >
> > > iAnywhere Solutions http://www.iAnywhere.com
> > >
> > > ** Please only post to the newsgroup
> > >
> > > ** Whitepapers can be found at http://www.iAnywhere.com/developer
> > > ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
> > > ** Use CaseXpress to report bugs http://casexpress.sybase.com
> > >
> > >
> >
>
****************************************************************************
> > > *
> > >
> > > "Chandresh Patel" <bapashree@yahoo.com> wrote in message
> > > news:403d77bf@forums-2-dub...
> > > > I use this command in Interactive SQL like
> > > >
> > > > SELECT StoreName INTO #CompSalesData from StoreInfo where StoreID IN
> > > (select
> > > > storeID from storegroup where StoreGroupID=2)
> > > > go
> > > > SELECT * from #CompSalesData
> > > > go
> > > >
> > > > Result is
> > > >
> > > > StoreName
> > > > 'BRYNMAWR'
> > > > 'WEST CHESTER'
> > > > 'WEST GHOSHEN'
> > > > 'GLENDORA'
> > > > 'BELLMAWR'
> > > >
> > > > this create one column name "StoreName" and store all result in to
> this
> > > > column (Result is 5 diffrent store name) and what I want is to
create
> 5
> > > > column using this store name.
> > > >
> > > >
> > > > Thanks in Advance
> > > >
> > > >
> > > >
> > > > "Chris Keating (iAnywhere Solutions)"
> <FightSpam_keating@iAnywhere.com>
> > > > wrote in message news:403d43cd$1@forums-1-dub...
> > > > > See SELECT statement, Transact-SQL. You can use SELECT INTO #
> > > {temptable}.
> > > > >
> > > > > --
> > > > >
> > > > > Chris Keating
> > > > > Sybase Adaptive Server Anywhere Professional Version 8
> > > > >
> > > > >
> > > >
> > >
> >
>
****************************************************************************
> > > > > *
> > > > > Sign up today for your copy of the SQL Anywhere Studio 9 Developer
> > > Edition
> > > > > and try out the market-leading database for mobile, embedded and
> small
> > > to
> > > > > medium sized business environments for free!
> > > > >
> > > > > http://www.ianywhere.com/promos/deved/index.html
> > > > >
> > > > >
> > > >
> > >
> >
>
****************************************************************************
> > > > > *
> > > > >
> > > > > iAnywhere Solutions http://www.iAnywhere.com
> > > > >
> > > > > ** Please only post to the newsgroup
> > > > >
> > > > > ** Whitepapers can be found at http://www.iAnywhere.com/developer
> > > > > ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
> > > > > ** Use CaseXpress to report bugs http://casexpress.sybase.com
> > > > >
> > > > >
> > > >
> > >
> >
>
****************************************************************************
> > > > > *
> > > > >
> > > > > "Chandresh Patel" <bapashree@yahoo.com> wrote in message
> > > > > news:403d2e1f$1@forums-1-dub...
> > > > > > Sybase Anywhere 9.0.0.1108
> > > > > >
> > > > > > In stored procedure at run time i know how many field I need.
> > > > > >
> > > > > > DECLARE LOCAL TEMPORARY TABLE
> > > > > > ReportData(
> > > > > > Here I want to supply dynamic value some time 5 column some time
> 12
> > > and
> > > > > some
> > > > > > time 20 we know at run time
> > > > > > )
> > > > > > NOT TRANSACTIONAL;
> > > > > >
> > > > > > Does anybudy knows how to create this table using dynamic column
> > > value.
> > > > > >
> > > > > > Thanks in Advance.
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


Chandresh Patel Posted on 2004-02-26 20:57:14.0Z
From: "Chandresh Patel" <bapashree@yahoo.com>
Newsgroups: ianywhere.public.general
References: <403d2e1f$1@forums-1-dub> <403d43cd$1@forums-1-dub> <403d77bf@forums-2-dub> <403d8172@forums-2-dub> <403e4e9e@forums-2-dub> <403e52b4$1@forums-2-dub>
Subject: Re: Here i build string but don't know how to use it
Lines: 285
Organization: Shreeji Smart Solution
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: aca0500d.ipt.aol.com
X-Original-NNTP-Posting-Host: aca0500d.ipt.aol.com
Message-ID: <403e5daa@forums-1-dub>
Date: 26 Feb 2004 12:57:14 -0800
X-Trace: forums-1-dub 1077829034 172.160.80.13 (26 Feb 2004 12:57:14 -0800)
X-Original-Trace: 26 Feb 2004 12:57:14 -0800, aca0500d.ipt.aol.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2665
Article PK: 6137

Finally I am done.

I get this string

DECLARE LOCAL TEMPORARY TABLE SalesData(SHARON HILL VARCHAR(30),
COLLINGDALE VARCHAR(30), DREXEL HILL VARCHAR(30), HAVERTOWN VARCHAR(30) )
NOT TRANSACTIONAL



I get space in store name and thats why it fail to create table.

So how can i remove space from string.

Thanks in Advance.


"Chris Keating (iAnywhere Solutions)" <FightSpam_keating@iAnywhere.com>

wrote in message news:403e52b4$1@forums-2-dub...
> use string concatenation to build the string. Obvously
>
> i.e.,
> set sqlStmt = String( strDeclareSyntax, strColumnSyntax,
> strNotTransactionSmt );
> execute immediate sqlStmt;
>
>
>
> --
>
> Chris Keating
> Sybase Adaptive Server Anywhere Professional Version 8
>
>
****************************************************************************
> *
> Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
> and try out the market-leading database for mobile, embedded and small to
> medium sized business environments for free!
>
> http://www.ianywhere.com/promos/deved/index.html
>
>
****************************************************************************
> *
>
> iAnywhere Solutions http://www.iAnywhere.com
>
> ** Please only post to the newsgroup
>
> ** Whitepapers can be found at http://www.iAnywhere.com/developer
> ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
> ** Use CaseXpress to report bugs http://casexpress.sybase.com
>
>
****************************************************************************
> *
>
> "Chandresh Patel" <bapashree@yahoo.com> wrote in message
> news:403e4e9e@forums-2-dub...
> > I create one mFieldData string but I don't know how to use it in Declare
> > temarary table statement.
> >
> > ALTER PROCEDURE "DBA"."GetSalesReportData"(IN mSGroupID Integer, IN
> > ReportType TinyInt, IN sDate DATE, IN eDate DATE, IN cID Integer)
> > BEGIN
> > declare err_notfound exception for sqlstate value '02000';
> > declare mStoreName varchar(40);
> > declare mFieldData long varchar;
> >
> > declare crsr cursor for call GetStoreName(mSGroupID);
> > open crsr;
> >
> > MyLoop: loop
> > fetch next crsr into mStoreName;
> >
> > if sqlstate = err_notfound then
> > leave MyLoop
> > else
> > select mStorName;
> >
> > if mFieldData = '' THEN
> > SET mFieldData = STRING (sName,' VARCHAR(30)');
> > else
> > SET mFieldData = STRING (mFieldData,', ',sName,'
> > VARCHAR(30)');
> > end if;
> >
> > end if;
> >
> > end loop MyLoop;
> >
> > close crsr
> >
> > //Here I don't know how to use this string to build this table.
> > ?????????
> > DECLARE LOCAL TEMPORARY TABLE SalesData('mFieldData') NOT
> TRANSACTIONAL;
> >
> > END
> >
> >
> >
> > Thanks in Advance.
> >
> >
> > "Chris Keating (iAnywhere Solutions)" <FightSpam_keating@iAnywhere.com>
> > wrote in message news:403d8172@forums-2-dub...
> > > This is a far different requirement that that defined in your original
> > post.
> > > One option is to create the sql statement that creates the local temp
> > table
> > > dynamically and using EXECUTE IMMEDIATE once the statement is build.
An
> > > example algorithm is:
> > >
> > > - determine the required columns
> > > - Initialize the create table sql statement based on the syntax
"DECLARE
> > > LOCAL TEMPORARY TABLE table-name ( "
> > > - for each column
> > > - add syntax to define column name and properties (i.e.,
> datatype)
> > > - add appropriate separator (comma if more columns exist or a
> > > closing parenthesis for last column
> > > - Complete the sql statement with table options ( i.e., ON COMMIT....)
> > > - Execute the statement to create the table.
> > >
> > >
> > > --
> > >
> > > Chris Keating
> > > Sybase Adaptive Server Anywhere Professional Version 8
> > >
> > >
> >
>
****************************************************************************
> > > *
> > > Sign up today for your copy of the SQL Anywhere Studio 9 Developer
> Edition
> > > and try out the market-leading database for mobile, embedded and small
> to
> > > medium sized business environments for free!
> > >
> > > http://www.ianywhere.com/promos/deved/index.html
> > >
> > >
> >
>
****************************************************************************
> > > *
> > >
> > > iAnywhere Solutions http://www.iAnywhere.com
> > >
> > > ** Please only post to the newsgroup
> > >
> > > ** Whitepapers can be found at http://www.iAnywhere.com/developer
> > > ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
> > > ** Use CaseXpress to report bugs http://casexpress.sybase.com
> > >
> > >
> >
>
****************************************************************************
> > > *
> > >
> > > "Chandresh Patel" <bapashree@yahoo.com> wrote in message
> > > news:403d77bf@forums-2-dub...
> > > > I use this command in Interactive SQL like
> > > >
> > > > SELECT StoreName INTO #CompSalesData from StoreInfo where StoreID IN
> > > (select
> > > > storeID from storegroup where StoreGroupID=2)
> > > > go
> > > > SELECT * from #CompSalesData
> > > > go
> > > >
> > > > Result is
> > > >
> > > > StoreName
> > > > 'BRYNMAWR'
> > > > 'WEST CHESTER'
> > > > 'WEST GHOSHEN'
> > > > 'GLENDORA'
> > > > 'BELLMAWR'
> > > >
> > > > this create one column name "StoreName" and store all result in to
> this
> > > > column (Result is 5 diffrent store name) and what I want is to
create
> 5
> > > > column using this store name.
> > > >
> > > >
> > > > Thanks in Advance
> > > >
> > > >
> > > >
> > > > "Chris Keating (iAnywhere Solutions)"
> <FightSpam_keating@iAnywhere.com>
> > > > wrote in message news:403d43cd$1@forums-1-dub...
> > > > > See SELECT statement, Transact-SQL. You can use SELECT INTO #
> > > {temptable}.
> > > > >
> > > > > --
> > > > >
> > > > > Chris Keating
> > > > > Sybase Adaptive Server Anywhere Professional Version 8
> > > > >
> > > > >
> > > >
> > >
> >
>
****************************************************************************
> > > > > *
> > > > > Sign up today for your copy of the SQL Anywhere Studio 9 Developer
> > > Edition
> > > > > and try out the market-leading database for mobile, embedded and
> small
> > > to
> > > > > medium sized business environments for free!
> > > > >
> > > > > http://www.ianywhere.com/promos/deved/index.html
> > > > >
> > > > >
> > > >
> > >
> >
>
****************************************************************************
> > > > > *
> > > > >
> > > > > iAnywhere Solutions http://www.iAnywhere.com
> > > > >
> > > > > ** Please only post to the newsgroup
> > > > >
> > > > > ** Whitepapers can be found at http://www.iAnywhere.com/developer
> > > > > ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
> > > > > ** Use CaseXpress to report bugs http://casexpress.sybase.com
> > > > >
> > > > >
> > > >
> > >
> >
>
****************************************************************************
> > > > > *
> > > > >
> > > > > "Chandresh Patel" <bapashree@yahoo.com> wrote in message
> > > > > news:403d2e1f$1@forums-1-dub...
> > > > > > Sybase Anywhere 9.0.0.1108
> > > > > >
> > > > > > In stored procedure at run time i know how many field I need.
> > > > > >
> > > > > > DECLARE LOCAL TEMPORARY TABLE
> > > > > > ReportData(
> > > > > > Here I want to supply dynamic value some time 5 column some time
> 12
> > > and
> > > > > some
> > > > > > time 20 we know at run time
> > > > > > )
> > > > > > NOT TRANSACTIONAL;
> > > > > >
> > > > > > Does anybudy knows how to create this table using dynamic column
> > > value.
> > > > > >
> > > > > > Thanks in Advance.
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


Chandresh Patel Posted on 2004-02-26 21:03:14.0Z
From: "Chandresh Patel" <bapashree@yahoo.com>
Newsgroups: ianywhere.public.general
References: <403d2e1f$1@forums-1-dub> <403d43cd$1@forums-1-dub> <403d77bf@forums-2-dub> <403d8172@forums-2-dub> <403e4e9e@forums-2-dub> <403e52b4$1@forums-2-dub> <403e5daa@forums-1-dub>
Subject: DONE
Lines: 319
Organization: Shreeji Smart Solution
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: aca0500d.ipt.aol.com
X-Original-NNTP-Posting-Host: aca0500d.ipt.aol.com
Message-ID: <403e5f12@forums-1-dub>
Date: 26 Feb 2004 13:03:14 -0800
X-Trace: forums-1-dub 1077829394 172.160.80.13 (26 Feb 2004 13:03:14 -0800)
X-Original-Trace: 26 Feb 2004 13:03:14 -0800, aca0500d.ipt.aol.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2666
Article PK: 6139

SET mStoreName = REPLACE(mStoreName, ' ', '');


Thank you everybudy.

"Chandresh Patel" <bapashree@yahoo.com> wrote in message
news:403e5daa@forums-1-dub...
> Finally I am done.
>
> I get this string
>
> DECLARE LOCAL TEMPORARY TABLE SalesData(SHARON HILL VARCHAR(30),
> COLLINGDALE VARCHAR(30), DREXEL HILL VARCHAR(30), HAVERTOWN VARCHAR(30) )
> NOT TRANSACTIONAL
>
>
>
> I get space in store name and thats why it fail to create table.
>
> So how can i remove space from string.
>
> Thanks in Advance.
>
>
> "Chris Keating (iAnywhere Solutions)" <FightSpam_keating@iAnywhere.com>
> wrote in message news:403e52b4$1@forums-2-dub...
> > use string concatenation to build the string. Obvously
> >
> > i.e.,
> > set sqlStmt = String( strDeclareSyntax, strColumnSyntax,
> > strNotTransactionSmt );
> > execute immediate sqlStmt;
> >
> >
> >
> > --
> >
> > Chris Keating
> > Sybase Adaptive Server Anywhere Professional Version 8
> >
> >
>
****************************************************************************
> > *
> > Sign up today for your copy of the SQL Anywhere Studio 9 Developer
Edition
> > and try out the market-leading database for mobile, embedded and small
to
> > medium sized business environments for free!
> >
> > http://www.ianywhere.com/promos/deved/index.html
> >
> >
>
****************************************************************************
> > *
> >
> > iAnywhere Solutions http://www.iAnywhere.com
> >
> > ** Please only post to the newsgroup
> >
> > ** Whitepapers can be found at http://www.iAnywhere.com/developer
> > ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
> > ** Use CaseXpress to report bugs http://casexpress.sybase.com
> >
> >
>
****************************************************************************
> > *
> >
> > "Chandresh Patel" <bapashree@yahoo.com> wrote in message
> > news:403e4e9e@forums-2-dub...
> > > I create one mFieldData string but I don't know how to use it in
Declare
> > > temarary table statement.
> > >
> > > ALTER PROCEDURE "DBA"."GetSalesReportData"(IN mSGroupID Integer, IN
> > > ReportType TinyInt, IN sDate DATE, IN eDate DATE, IN cID Integer)
> > > BEGIN
> > > declare err_notfound exception for sqlstate value '02000';
> > > declare mStoreName varchar(40);
> > > declare mFieldData long varchar;
> > >
> > > declare crsr cursor for call GetStoreName(mSGroupID);
> > > open crsr;
> > >
> > > MyLoop: loop
> > > fetch next crsr into mStoreName;
> > >
> > > if sqlstate = err_notfound then
> > > leave MyLoop
> > > else
> > > select mStorName;
> > >
> > > if mFieldData = '' THEN
> > > SET mFieldData = STRING (sName,' VARCHAR(30)');
> > > else
> > > SET mFieldData = STRING (mFieldData,', ',sName,'
> > > VARCHAR(30)');
> > > end if;
> > >
> > > end if;
> > >
> > > end loop MyLoop;
> > >
> > > close crsr
> > >
> > > //Here I don't know how to use this string to build this table.
> > > ?????????
> > > DECLARE LOCAL TEMPORARY TABLE SalesData('mFieldData') NOT
> > TRANSACTIONAL;
> > >
> > > END
> > >
> > >
> > >
> > > Thanks in Advance.
> > >
> > >
> > > "Chris Keating (iAnywhere Solutions)"
<FightSpam_keating@iAnywhere.com>
> > > wrote in message news:403d8172@forums-2-dub...
> > > > This is a far different requirement that that defined in your
original
> > > post.
> > > > One option is to create the sql statement that creates the local
temp
> > > table
> > > > dynamically and using EXECUTE IMMEDIATE once the statement is build.
> An
> > > > example algorithm is:
> > > >
> > > > - determine the required columns
> > > > - Initialize the create table sql statement based on the syntax
> "DECLARE
> > > > LOCAL TEMPORARY TABLE table-name ( "
> > > > - for each column
> > > > - add syntax to define column name and properties (i.e.,
> > datatype)
> > > > - add appropriate separator (comma if more columns exist or
a
> > > > closing parenthesis for last column
> > > > - Complete the sql statement with table options ( i.e., ON
COMMIT....)
> > > > - Execute the statement to create the table.
> > > >
> > > >
> > > > --
> > > >
> > > > Chris Keating
> > > > Sybase Adaptive Server Anywhere Professional Version 8
> > > >
> > > >
> > >
> >
>
****************************************************************************
> > > > *
> > > > Sign up today for your copy of the SQL Anywhere Studio 9 Developer
> > Edition
> > > > and try out the market-leading database for mobile, embedded and
small
> > to
> > > > medium sized business environments for free!
> > > >
> > > > http://www.ianywhere.com/promos/deved/index.html
> > > >
> > > >
> > >
> >
>
****************************************************************************
> > > > *
> > > >
> > > > iAnywhere Solutions http://www.iAnywhere.com
> > > >
> > > > ** Please only post to the newsgroup
> > > >
> > > > ** Whitepapers can be found at http://www.iAnywhere.com/developer
> > > > ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
> > > > ** Use CaseXpress to report bugs http://casexpress.sybase.com
> > > >
> > > >
> > >
> >
>
****************************************************************************
> > > > *
> > > >
> > > > "Chandresh Patel" <bapashree@yahoo.com> wrote in message
> > > > news:403d77bf@forums-2-dub...
> > > > > I use this command in Interactive SQL like
> > > > >
> > > > > SELECT StoreName INTO #CompSalesData from StoreInfo where StoreID
IN
> > > > (select
> > > > > storeID from storegroup where StoreGroupID=2)
> > > > > go
> > > > > SELECT * from #CompSalesData
> > > > > go
> > > > >
> > > > > Result is
> > > > >
> > > > > StoreName
> > > > > 'BRYNMAWR'
> > > > > 'WEST CHESTER'
> > > > > 'WEST GHOSHEN'
> > > > > 'GLENDORA'
> > > > > 'BELLMAWR'
> > > > >
> > > > > this create one column name "StoreName" and store all result in to
> > this
> > > > > column (Result is 5 diffrent store name) and what I want is to
> create
> > 5
> > > > > column using this store name.
> > > > >
> > > > >
> > > > > Thanks in Advance
> > > > >
> > > > >
> > > > >
> > > > > "Chris Keating (iAnywhere Solutions)"
> > <FightSpam_keating@iAnywhere.com>
> > > > > wrote in message news:403d43cd$1@forums-1-dub...
> > > > > > See SELECT statement, Transact-SQL. You can use SELECT INTO #
> > > > {temptable}.
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Chris Keating
> > > > > > Sybase Adaptive Server Anywhere Professional Version 8
> > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
****************************************************************************
> > > > > > *
> > > > > > Sign up today for your copy of the SQL Anywhere Studio 9
Developer
> > > > Edition
> > > > > > and try out the market-leading database for mobile, embedded and
> > small
> > > > to
> > > > > > medium sized business environments for free!
> > > > > >
> > > > > > http://www.ianywhere.com/promos/deved/index.html
> > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
****************************************************************************
> > > > > > *
> > > > > >
> > > > > > iAnywhere Solutions http://www.iAnywhere.com
> > > > > >
> > > > > > ** Please only post to the newsgroup
> > > > > >
> > > > > > ** Whitepapers can be found at
http://www.iAnywhere.com/developer
> > > > > > ** EBFs can be found at
http://downloads.sybase.com/swx/sdmain.stm
> > > > > > ** Use CaseXpress to report bugs http://casexpress.sybase.com
> > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
****************************************************************************
> > > > > > *
> > > > > >
> > > > > > "Chandresh Patel" <bapashree@yahoo.com> wrote in message
> > > > > > news:403d2e1f$1@forums-1-dub...
> > > > > > > Sybase Anywhere 9.0.0.1108
> > > > > > >
> > > > > > > In stored procedure at run time i know how many field I need.
> > > > > > >
> > > > > > > DECLARE LOCAL TEMPORARY TABLE
> > > > > > > ReportData(
> > > > > > > Here I want to supply dynamic value some time 5 column some
time
> > 12
> > > > and
> > > > > > some
> > > > > > > time 20 we know at run time
> > > > > > > )
> > > > > > > NOT TRANSACTIONAL;
> > > > > > >
> > > > > > > Does anybudy knows how to create this table using dynamic
column
> > > > value.
> > > > > > >
> > > > > > > Thanks in Advance.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>