Hi, I need to define a global or static variable, but I don't know how I can do it.
I need to number each "Detail" line, but I can't use the "Record Number" of "Special Fields" because each database record can create more than one detail line. I try to solve this problem using a global or static variable, but if isn't possible, what do you suggest me?
Thanks and sorry for my bad English.I've just done this very thing in a report I've been working on. The method I used is dependent on using a group header along with the details section.
1) Create a formula field (I called mine CounterReset) and use the following formula:
WhilePrintingRecords;
Global numbervar GroupRecCount;
GroupRecCount := 0;
2) Add this field to your group header. You can supress it if you don't want it to show on your report.
3) Crate another formula field (I called mine CountOfRecordsInGroup) and paste the following formula:
WhilePrintingRecords;
Global numbervar GroupRecCount;
GroupRecCount := GroupRecCount + 1;
4) Use this field in your details section as the line number. It will reset to zero when your groupnumber advances and *sounds* like what you are looking for from the description of your problem.
Hope this helps!
Z|||Thank you very much zilonox!! I've been asking this for several days and nobody could help me.
Greetings,
Christian J.
Showing posts with label variables. Show all posts
Showing posts with label variables. Show all posts
Thursday, March 22, 2012
Can I create index on table variables?
I was not able to find any document on how to create
index on table variables. Please let me know if you are
able to do so.
Thanks,
RachanFrom http://www.aspfaq.com/2475:
You cannot explicitly add an index to a table variable, however you can
create a system index through a PRIMARY KEY CONSTRAINT, and you can add as
many indexes via UNIQUE CONSTRAINTs as you like. What the optimizer does
with them is another story. <G>
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rachan Terrell" <web24by7@.hotmail.com> wrote in message
news:021601c3af95$66dc5710$a301280a@.phx.gbl...
> I was not able to find any document on how to create
> index on table variables. Please let me know if you are
> able to do so.
> Thanks,
> Rachan|||This is a multi-part message in MIME format.
--=_NextPart_000_0147_01C3AF6C.A83E9BB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
You're not allowed to run CREATE INDEX against a table variable. However,
you can declare the variable with primary key and unique constraints:
declare @.MyTable table
(
id int primary key
, x char (1) not null
, z int not null unique
)
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Rachan Terrell" <web24by7@.hotmail.com> wrote in message
news:021601c3af95$66dc5710$a301280a@.phx.gbl...
I was not able to find any document on how to create
index on table variables. Please let me know if you are
able to do so.
Thanks,
Rachan
--=_NextPart_000_0147_01C3AF6C.A83E9BB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You're not allowed to run CREATE INDEX =against a table variable. However, you can declare the variable with primary =key and unique constraints:
declare @.MyTable =table
(
=id int primary key
, =x char (1) not null
, =z int not null =unique
)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Rachan Terrell" wrote =in message news:021601c3af95$66=dc5710$a301280a@.phx.gbl...I was not able to find any document on how to create index on table variables. Please let me know if you are able to do so.Thanks,Rachan
--=_NextPart_000_0147_01C3AF6C.A83E9BB0--|||Rachan,
Take a look at BOL for "table variable". You will see that you can indeed
create keys on table variables.
J.R.
Largo SQL Tools
The Finest Collection of SQL Tools Available
http://www.largosqltools.com
"Rachan Terrell" <web24by7@.hotmail.com> wrote in message
news:021601c3af95$66dc5710$a301280a@.phx.gbl...
> I was not able to find any document on how to create
> index on table variables. Please let me know if you are
> able to do so.
> Thanks,
> Rachan|||All,
Thanks so much for all your helps.
Take Care,
Rachan
>--Original Message--
>Rachan,
>Take a look at BOL for "table variable". You will see
that you can indeed
>create keys on table variables.
>J.R.
>Largo SQL Tools
>The Finest Collection of SQL Tools Available
>http://www.largosqltools.com
>"Rachan Terrell" <web24by7@.hotmail.com> wrote in message
>news:021601c3af95$66dc5710$a301280a@.phx.gbl...
>> I was not able to find any document on how to create
>> index on table variables. Please let me know if you
are
>> able to do so.
>> Thanks,
>> Rachan
>
>.
>
index on table variables. Please let me know if you are
able to do so.
Thanks,
RachanFrom http://www.aspfaq.com/2475:
You cannot explicitly add an index to a table variable, however you can
create a system index through a PRIMARY KEY CONSTRAINT, and you can add as
many indexes via UNIQUE CONSTRAINTs as you like. What the optimizer does
with them is another story. <G>
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rachan Terrell" <web24by7@.hotmail.com> wrote in message
news:021601c3af95$66dc5710$a301280a@.phx.gbl...
> I was not able to find any document on how to create
> index on table variables. Please let me know if you are
> able to do so.
> Thanks,
> Rachan|||This is a multi-part message in MIME format.
--=_NextPart_000_0147_01C3AF6C.A83E9BB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
You're not allowed to run CREATE INDEX against a table variable. However,
you can declare the variable with primary key and unique constraints:
declare @.MyTable table
(
id int primary key
, x char (1) not null
, z int not null unique
)
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Rachan Terrell" <web24by7@.hotmail.com> wrote in message
news:021601c3af95$66dc5710$a301280a@.phx.gbl...
I was not able to find any document on how to create
index on table variables. Please let me know if you are
able to do so.
Thanks,
Rachan
--=_NextPart_000_0147_01C3AF6C.A83E9BB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You're not allowed to run CREATE INDEX =against a table variable. However, you can declare the variable with primary =key and unique constraints:
declare @.MyTable =table
(
=id int primary key
, =x char (1) not null
, =z int not null =unique
)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Rachan Terrell"
--=_NextPart_000_0147_01C3AF6C.A83E9BB0--|||Rachan,
Take a look at BOL for "table variable". You will see that you can indeed
create keys on table variables.
J.R.
Largo SQL Tools
The Finest Collection of SQL Tools Available
http://www.largosqltools.com
"Rachan Terrell" <web24by7@.hotmail.com> wrote in message
news:021601c3af95$66dc5710$a301280a@.phx.gbl...
> I was not able to find any document on how to create
> index on table variables. Please let me know if you are
> able to do so.
> Thanks,
> Rachan|||All,
Thanks so much for all your helps.
Take Care,
Rachan
>--Original Message--
>Rachan,
>Take a look at BOL for "table variable". You will see
that you can indeed
>create keys on table variables.
>J.R.
>Largo SQL Tools
>The Finest Collection of SQL Tools Available
>http://www.largosqltools.com
>"Rachan Terrell" <web24by7@.hotmail.com> wrote in message
>news:021601c3af95$66dc5710$a301280a@.phx.gbl...
>> I was not able to find any document on how to create
>> index on table variables. Please let me know if you
are
>> able to do so.
>> Thanks,
>> Rachan
>
>.
>
Subscribe to:
Posts (Atom)