Replacing NULLS

Table1 Definition
[timestamp], datetime
[System201], int
[System500], int
[System345], int
... (the number of [System###] fields will be different for each facility I deploy this in)

Current Table1 Data
[timestamp] [System201] [System500] [System345] ...
1/1/03 1 2 4
1/2/02 3 NULL NULL
1/3/03 NULL 5 8

I would like to replace the NULL's with the value from the previous record. Is there a quick and dirty way to do this?

Future Table1 Data
[timestamp] [System201] [System500] [System345] ...
1/1/03 1 2 4
1/2/02 3 2 4
1/3/03 3 5 8

JeffreyI see a cursor in your future (ducks and hides)...

Seriously, the only way the I know of to transpose one row of data onto another in the type of scenario you describe is to do it with a cursor:

-- Declare Local variables (one for each column, plus an
-- additional variable for each column to store the previous value)
DECLARE
@.TimeStamp datetime,
@.System201 int,
@.System500 int,
@.System345 int,
...
@.System201_Old int,
@.System500_Old int,
@.System345_Old int,
...
@.Counter int

-- Declare the cursor
DECLARE rolling_cursor CURSOR
FROM SELECT * FROM TABLE1 ORDER BY TimeStamp

-- Open the cursor
OPEN rolling_cursor

-- Set an initial counter
Set @.Counter = 1

-- Fetch the first row of values into the local variables
FETCH NEXT FROM rolling_cursor INTO
@.TimeStamp,
@.System201,
@.System500,
@.System345

-- WHILE your way through the table, updating the next row
-- with the previous rows data when the next row's columns
-- are null (see COALESCE)
WHILE fetch_status <> -1
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
IF @.Counter = 1
BEGIN
@.System201_Old = @.System_201
@.System500_Old = @.System_500
@.System345_Old = @.System_345
END
ELSE
BEGIN
UPDATE TABLE1 SET
@.System201 = COALESCE(@.System201, @.System201_Old),
@.System201 = COALESCE(@.System201, @.System201_Old),
@.System201 = COALESCE(@.System201, @.System201_Old),
...
WHERE TimeStamp = @.TimeStamp

@.System201_Old = @.System_201
@.System500_Old = @.System_500
@.System345_Old = @.System_345
@.Counter = @.Counter + 1
END

FETCH NEXT FROM rolling_cursor INTO
@.TimeStamp,
@.System201,
@.System500,
@.System345
END

-- Close and deallocate the cursor
CLOSE rolling_cursor
DEALLOCATE rolling_cursor

-- Print a message to the user to show how many records were updated
Print @.Counter + ' records updated.'

Notes:
1. Not tested
2. Not guaranteed
3. Mileage may vary
4. Please don't sue me
5. Don't sic the DBA gods on me for suggesting a cursor
6. I am assuming that the PK on this table is TimeStamp (and that Timestamp is sequential). This solution does not work otherwise.

Best of luck,

Hugh Scott
Originally posted by JeffreyELewis
Table1 Definition
[timestamp], datetime
[System201], int
[System500], int
[System345], int
... (the number of [System###] fields will be different for each facility I deploy this in)

Current Table1 Data
[timestamp] [System201] [System500] [System345] ...
1/1/03 1 2 4
1/2/02 3 NULL NULL
1/3/03 NULL 5 8

I would like to replace the NULL's with the value from the previous record. Is there a quick and dirty way to do this?

Future Table1 Data
[timestamp] [System201] [System500] [System345] ...
1/1/03 1 2 4
1/2/02 3 2 4
1/3/03 3 5 8

Jeffrey|||Jeez, i spend all that time formatting the script with spaces and everything (to show nesting) and it comes out looking out looking like that!?!?!

First, I humbly apologize if it is difficult to read through. Second, it anyone knows how to make spaces or tabs work on this forum, I would be grateful.

Regards,

Hugh Scott|||keeping my comments on cursors to myself...

edit your post and put a code at the beggining and a /code at the end like using bold, that should do it.

Also I think your logic may be flawed, your cursor sorts by time stamp and the examples don't reflect that.|||I appreciate your amendments. You're right on the comment about timestamps -- I missed that in the original statement of requirements.

Oh well, back to the drawing board!!!

Regards,

Hugh Scott

Originally posted by Paul Young
keeping my comments on cursors to myself...

edit your post and put a code at the beggining and a /code at the end like using bold, that should do it.

Also I think your logic may be flawed, your cursor sorts by time stamp and the examples don't reflect that.|||1/1/03 1/2/02 1/3/03

Q1 What PK is on Table1?
Q2 How do you know the record is previous?|||I assumed that the PK was Timestamp. I inferred that the poster made an error and meant to put /03 instead of /02. I know what happens when you assume...

Kindest regards,

Hugh Scott

Originally posted by ispaleny
1/1/03 1/2/02 1/3/03

Q1 What PK is on Table1?
Q2 How do you know the record is previous?|||Your right. That was a typo. The year should have been 03 instead of 02 and the PK is the [timestamp] field.|||it looks like hmscott provided the answer. Post back and let us know if it worked.|||Now, when your information is clear, I can post my solution.

/*
Script drops Table1, uses transaction to support 2 solutions in one script
*/

--INIT
drop table Table1
GO
create table Table1(
[timestamp] datetime primary key clustered
,[System201] int null
,[System500] int null
,[System345] int null
)
GO
set dateformat MDY
insert Table1( [timestamp],[System201],[System500],[System345] ) values ('1/1/03', 1, 2, 4)
insert Table1( [timestamp],[System201],[System500],[System345] ) values ('1/2/03', 3,NULL,NULL)
insert Table1( [timestamp],[System201],[System500],[System345] ) values ('1/3/03',NULL, 5, 8)
GO

begin tran
select * from Table1

--SOLUTION WITHOUT CURSOR OR PSEDOCURSOR
/*
Recomended use of clustered PK on [timestamp] and separate index on each (System...) column for large table
*/
update t1 set
t1.[System201]=
(
select t2.[System201]
from Table1 t2
join
(
select [timestamp]=max(t3.[timestamp])
from Table1 t3
where (t1.[timestamp]>=t3.[timestamp]) and (t3.[System201] is not null)
) x on t2.[timestamp]=x.[timestamp]
)
from Table1 t1
where t1.[System201] is null
update t1 set
t1.[System500]=
(
select t2.[System500]
from Table1 t2
join
(
select [timestamp]=max(t3.[timestamp])
from Table1 t3
where (t1.[timestamp]>=t3.[timestamp]) and (t3.[System500] is not null)
) x on t2.[timestamp]=x.[timestamp]
)
from Table1 t1
where t1.[System500] is null
update t1 set
t1.[System345]=
(
select t2.[System345]
from Table1 t2
join
(
select [timestamp]=max(t3.[timestamp])
from Table1 t3
where (t1.[timestamp]>=t3.[timestamp]) and (t3.[System345] is not null)
) x on t2.[timestamp]=x.[timestamp]
)
from Table1 t1
where t1.[System345] is null

select * from Table1
rollback tran
begin tran
select * from Table1

--PSEDOCURSOR SOLUTION
declare @.timestamp datetime
declare @.System201 int
declare @.System500 int
declare @.System345 int
declare @.timestampPre datetime
declare @.System201Pre int
declare @.System500Pre int
declare @.System345Pre int
set nocount on
select @.timestampPre=[timestamp],@.System201Pre=[System201],@.System500Pre=[System500],@.System345Pre=[System345] from Table1 t1
where t1.[timestamp]=(select min(t2.[timestamp]) from Table1 t2)
while 1=1 begin
select @.timestamp=[timestamp],@.System201=[System201],@.System500=[System500],@.System345=[System345] from Table1 t1
where t1.[timestamp]=(select min(t2.[timestamp]) from Table1 t2 where t2.[timestamp]>@.timestampPre)
if @.@.rowcount=0 break
set @.System201Pre=isnull(@.System201,@.System201Pre)
set @.System500Pre=isnull(@.System500,@.System500Pre)
set @.System345Pre=isnull(@.System345,@.System345Pre)
if @.System201 is null or @.System500 is null or @.System345 is null
update Table1 set
[System201]=@.System201Pre
,[System500]=@.System500Pre
,[System345]=@.System345Pre
where [timestamp]=@.timestamp
set @.timestampPre=@.timestamp
end
set nocount off

select * from Table1
rollback tran|||Forgive me for being simple but there is a quick and dirty way to update previous record with data from next record.

Select *, identity(int ,1,1) as seq
into #temp
from "your table"
order by timestamp

update t1
set t1.System201 = t2.System201
from #temp t1 inner join #temp t2 on
t1.seq = t2.seq - 1|||Yes, I call that algorithm "ordered streams". But I was trying to solve
situation like this

1 1
2 NULL
3 NULL
->
1 1
2 1
3 1

by query without cursor.|||Why not use a subquery?

select t.fldnam
from tblnam t
where t.valfld is not null
and t.valfld < ( select t1.valfld
from tblnam t1
where t1.keyfld = t.keyfld )

I'm suppose it isn't the finnest but I'm shure it's cheaper than cursors, no?|||Cesar, I do not uderstand what you mean by your code.

It can be solved by single quary

select t.keyfld,t.valfld,t1.keyfld,t1.valfld
from tblnam t
join tblnam t1 on t1.keyfld=(select max(t2.keyfld) from tblnam t2 where t2.keyfld<=t.keyfld and t2.valfld is not null)

But this query is not scalable (N^3), cursor is better algorithm.|||I don't know exactly how to calculate the difference between subqueries and joins, focusing in througput, because I used to work only with lower than 100,000 records tables, so my point is I agree about cursor structures and algorithms in theoretical terms, but not in speed, at least in MSSQL7 implementation.
So I'm always preffer to complicate my scripts with more sophisticated selects/joins/temporary tables/indexes, etc...
The only drawback I've found is maintenance, because almost any VB programmer can deal with cursors but complex SQL statements.
So in my record's range its a question of maintenance/performance for me and I've choseen performance until now, against cursors.
What do you think?|||I already compared cursor and noncursor solutions at this forum.
Problem solvable without cursor can be faster without cursor always.

1. I just say, that single query solution of this problem is slower than simple cursor solution.
2. I do not understand your code

select t.fldnam
from tblnam t
where t.valfld is not null
and t.valfld < ( select t1.valfld
from tblnam t1
where t1.keyfld = t.keyfld
)|||Originally posted by ispaleny
1/1/03 1/2/02 1/3/03

Q1 What PK is on Table1?
Q2 How do you know the record is previous?

I just tried to insert a non-cursor sample about finding previous record.

Replacing characters in a text field

I have a large table, tblMessage, which stores e-mail messages in text
fields. I need to remove the carriage returns the data in these fields,
but I have not yet figured out how to do so.

I thought that the way to do this would be with the REPLACE function;
unfortunately, of course, the REPLACE function cannot work with TEXT
fields. I tried CASTing the text field to VARCHAR(8000); however, some
of the rows have more than 8000 characters in the text field, so it bombs.

Here is the SQL that I tried:

select
msgID,
msgSent,
msgFromType,
msgFromID,
msgSubject,
REPLACE (CAST(msgMessage AS varchar(8000)), CHAR(13), '<BR>') AS
newMessage,
msgOriginal,
attID
into tblMessageNew
from tblMessage

I'm at my wit's end. Truncating the text field to 8000 character is an
acceptable option, but I can't even seem to be able to do that.

I'm using SQL Server version 7.Richard S. Crawford (rscrawfordDUCK@.mossREMOVEWATERFOWLroot.com) writes:
> I have a large table, tblMessage, which stores e-mail messages in text
> fields. I need to remove the carriage returns the data in these fields,
> but I have not yet figured out how to do so.
> I thought that the way to do this would be with the REPLACE function;
> unfortunately, of course, the REPLACE function cannot work with TEXT
> fields. I tried CASTing the text field to VARCHAR(8000); however, some
> of the rows have more than 8000 characters in the text field, so it bombs.

Bombs with what? It's always helpful if you include the error message.

I was able to run this on SQL Server 7:

create table hh (a text not null)
go
declare @.d varchar(8000), @.df varchar(8000)
select @.d = replicate('Why are you here? You should be there!', 8000/30)
select @.df = replicate('Why are you here? You should be there!', 8000/30)
insert hh (a)
exec ('select ''' + @.d + @.df + '''')
select datalength(a) from hh
select replace(cast(a as varchar(8000)), 'Why', 'Porque') from hh
go
drop table hh

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

Taking the CR/LF out before inserting the text would avoid your problem!!!

The only way I can think of doing this is to chunk the text field into 8000
character as in
http://tinyurl.com/3fqxv

One way of doing this:
http://tinyurl.com/236hf

John

"Richard S. Crawford" <rscrawfordDUCK@.mossREMOVEWATERFOWLroot.com> wrote in
message news:ca4rog$d8b$1@.woodrow.ucdavis.edu...
> I have a large table, tblMessage, which stores e-mail messages in text
> fields. I need to remove the carriage returns the data in these fields,
> but I have not yet figured out how to do so.
> I thought that the way to do this would be with the REPLACE function;
> unfortunately, of course, the REPLACE function cannot work with TEXT
> fields. I tried CASTing the text field to VARCHAR(8000); however, some
> of the rows have more than 8000 characters in the text field, so it bombs.
> Here is the SQL that I tried:
> select
> msgID,
> msgSent,
> msgFromType,
> msgFromID,
> msgSubject,
> REPLACE (CAST(msgMessage AS varchar(8000)), CHAR(13), '<BR>') AS
> newMessage,
> msgOriginal,
> attID
> into tblMessageNew
> from tblMessage
> I'm at my wit's end. Truncating the text field to 8000 character is an
> acceptable option, but I can't even seem to be able to do that.
> I'm using SQL Server version 7.

Replacement for my LIKE Clause

Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.
SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?
[vbcol=seagreen]
Why did you avoid clear names?
CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN ?
-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.
Can two prefixes belong to the same SAN? No specs given.
Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?
CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);
The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.
[vbcol=seagreen]
No. Clear specs would have been nice, along with real DDL.
Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:
INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @.my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),
Etc.);
You will need error handling code for prefixes that do not match.
Roy,
Thanks a tonne for your prompt and timely response... I could modify my
script on the lines of your code and it worked (smile)..
Celko,
Thanks to you as well, for your valuable suggestions... And I can
understand your outburst... I just jotted down something(without even
proof reading it) because the intend was to get the question out
yesterday, to hopefully get a response by today... Clear names were not
used, Redundancy was there etc... because it was a cooked up scenario,
but my requirement was very like the one I had outlined ...
I really appreciate the time you have taken to progressively take apart
my question... But as long as you understood the original intend on
where I was stuck and I got a solution to my problem, Believe me I am
happy...
I will remember that I might upset Guru's like you with my questions,
in future, and be more careful with its structure and wording...
Thanks once again...
VM
--CELKO-- wrote:
> Why did you fail to post DDL, screw up the syntax and violate ISO-11179
> naming rules? Probably because you also confuse fields and columns.
> Let's start by cleaning up you code, so it looks like SQL.
> SQL uses single quotes for strings. A data element can be a location or
> an identifier, never both. A transaction is some kind of transaction.
> Etc. You need a data modeling course. Your sample data failed to give
> values of the improperly named 'EmployeeLocationID' - I hope to
> ghod you are not using IDENTITY and thinking that it is a key!!
> Don't you know about SAN and other industry standard address numbers?
>
> Why did you avoid clear names?
> CREATE TABLE LocationCodes
> (loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
> loc_code INTEGER NOT NULL); -- industry SAN ?
> -- put wildcards in the table for indexing
> INSERT INTO LocationCodes VALUES ('B%', 100);
> INSERT INTO LocationCodes VALUES ('BO%', 101);
> INSERT INTO LocationCodes VALUES ('BOM%', 102);
> Etc.
> Can two prefixes belong to the same SAN? No specs given.
> Without a key in that vague transactions table, you do not have a
> proper table at all. I had to make up one. Why do you have employee
> id and not find the employee name via a join to the Personnel table?
> Isn't the idea of RDBMS to get rid of redudant data?
> CREATE TABLE FoobarTrans
> (foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
> -- CHECK (<<needs validation rule here>>),
> emp_id INTEGER NOT NULL
> REFERENCES Personnel(emp_id)
> ON UPDATE CASCADE,
> loc_code INTEGER NOT NULL
> REFERENCES LocationCodes(loc_code)
> ON UPDATE CASCADE,
> Etc.);
> The prefix should have been used when you inserted the initial row (NOT
> field!!!) into the table. Because you are confusing fields and
> columns, files and tables, you are thinking in procedural *steps* with
> updates just like a punch card file, not in sets like an SQL
> programmer.
>
> No. Clear specs would have been nice, along with real DDL.
> Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
> in the VALUES list, but if you have SQL-2005, try this little untested
> statement:
> INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
> VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
> (WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
> FROM LocationCodes AS L1
> WHERE L1.loc_prefix LIKE @.my_loc_prefix)
> AS M(loc_code, fit)
> SELECT loc_code
> FROM M AS M1
> WHERE M1.fit
> = (SELECT MAX(M2.fit) FROM M AS M2)),
> Etc.);
> You will need error handling code for prefixes that do not match.
sql

Replace LoginName control with other data fields from aspnetdb.mdf

I currently have a LoginStatus and LoginName control on my asp.net 2.0 master page. When successfully logged in a message displays "You are logged in as" LoginName. Since I am using the user's e-mail address as the UserName the message will display as "You are logged in as johndoe@.foo.com".

What I want to do is have the message display as "You are logged in as " FirstName LastName. FirstName and LastName are two new fields that I added to the table, aspnet_Users.

What I have done is to use the FormView control to display FirstName and LastName; however this always displays the first record in the aspnetdb database.

Is there a cleaner way (than the FormView control) to display the requested data, using the currently logged in user? I have access to Microsoft Expression Web and Visual Studio 2005, but I prefer Expression Web.

Thank you in advance

Hi,

From your description, it seems that you want to get the FirstName and LastName field for the logon user, right?

If so, I think you can get the current user's name by using User.Identity.Name, and then, invoke the GetUser method of Membership with the current user's name, which can return a MembershipUser typed object. You can access the data filed in the membership tables.

Thanks.

Replace data using sql

I have a database of about 300,000 records.
The records were imported from a csv file.
One of the fields is duration.

The data in duration are like ths:
1 second: 0:01
26 minutes: 26:00

If i put the format of the field as time, the data are messed up.
0:01 becomes 1 minute.
26:00 becomes 1 day 2 hours.

I currently have duration as text.

How can i use sql or visual basic to replace all the data so that they can have the format "00:00:00"?
(0:01 becomes 00:00:01, 26:00 becomes 00:26:00)

I need the duration in time format in order to be able to make sum calculations.

I will be doing the same calculations every month so i need the above procedure to be able to execute it every time i need to.

Thank you in advace

GeorgeThis smells easy. I'd try:CREATE TABLE tRousoug (
foo VARCHAR(8) NOT NULL
)

INSERT INTO tRousoug (foo)
SELECT '0:01' UNION ALL SELECT '26:00'

SELECT Left('00:00:00', 8 - Len(foo)) + foo
FROM tRousoug-PatP|||My Table is named "June 2004" and the field containing the data "Duration".

Can you rewrite the code using the above information since i am not able to make it work?

Thanks|||Ok.SELECT Left('00:00:00', 8 - Len(Duration)) + Duration
FROM [June 2004]-PatP|||Ok. That seems to work. But ut does not replace the data. It only creates a query with the changes. The field data in the table remain the same.|||Write an UPDATE statement.

Perhaps you should at least bother to open a book or try to look at the documentation before posting to forums asking people to do your work for you. I'm sorry for being so harsh but it really doesn't seem like you're trying very hard. If I am wrong and you did try with the documentation please accept my apologies.

Dag|||dagjo, you sound quite experienced, im currently on some microsoft courses, could you reccommend any good books at all?|||Oh, and by the way, the solution is

UPDATE [June 2004]
SET Duration = Left('00:00:00', 8 - Len(Duration)) + Duration|||Sorry about this, end of a working day, i'm a little bit tired. I think i need some time off and a revision of my books.

Thank you|||Pace,

sorry, but I'm not primarily a db developer and thus I've got pretty limited knowledge of books and certainly not enough to tell which ones are good and which ones aren't.

I can and do recommend to use the help files that ship with SQL Server. As a general rule, I recommend trying to look it up in the doc before posting to forums.

:) Dag|||on my querying using transact sql course they were describing how to use the help effectively as I never had a clue when I used it... its surprising just how much help the help is, once you know how to read it properly of course.
Thanks all the same Dag! ;)sql

Replace blank fields

Hi
I am replacing null and blank fields in a table.
Does anyone know the syntax to replace a blank field?
Update site
Set postcode = '-'
Where postcode is nullOn Fri, 11 Feb 2005 06:37:05 -0800, Jaco wrote:

>Hi
>I am replacing null and blank fields in a table.
>Does anyone know the syntax to replace a blank field?
>Update site
>Set postcode = '-'
>Where postcode is null
Hi Jaco,
UPDATE site
SET postcode = '-'
WHERE postcode IS NULL
OR postcode = ''
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

repeating free form list item fields for each group

Hi, I have a nested report where i have some dept name and address fields placed in the free form list, below the address fields i have the related employee info in a table. i have grouped on the dept id for both the list and table. for a particular dept , when the related employee table information goes on to the next page i want to repeat the dept name and address fields on each page for the group.i cannot place the fields in the group header of the table, since there are a lot of fields at different positions .so i have kept in free form in the list region.It's basically the group header information, i wanted repeated on each page, but i have placed the fields above the table, in free form.Please help. Really apreciate it . Thanks.

You can nest rectangles and text boxes in group and table header cells. This means it is possible to create a free form layout in the table or group header.

For example, you can add a rectangle to the group header cell in your table and then add multiple text boxes or images to that rectangle at any position you desire.

|||Thanks a lot.

Repeating data

I want a table with fields from the dataset that repeats for every page in
the report. How can I do this...?
DanHello,
Have you tried to put your fields in table or group header and ask for
repeat on noew page in report properties ?
Jerome Berthaud (MCSD)
http://www.winsight.fr
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:umDmb4$eEHA.1356@.TK2MSFTNGP09.phx.gbl...
> I want a table with fields from the dataset that repeats for every page in
> the report. How can I do this...?
> Dan
>

Repeating a textbox on every page of the report

Hi,
I have something like this in the report body
>> TEXTBOX1 = Fields ! MyFieldName.Value <<
>> the table definition here <<
My problem is that I want to repeat TEXTBOX1 on all the pages if the table
has more than one page,
Right now only appear in the first page.
I tried to set TEXTBOX1.RepeatWith = TABLE1 but it has no effect.
In case it helps the value will not change of value in the report. I'm open
to use any other mean like a parameter, etcyou have to put this information in the table header himself
and setup this table header row to be repeated on each page.
"Ignacio Machin" <Ignacio Machin@.discussions.microsoft.com> wrote in message
news:DA36576E-D1C4-4112-B1C8-C7A0B20631EE@.microsoft.com...
> Hi,
> I have something like this in the report body
> >> TEXTBOX1 = Fields ! MyFieldName.Value <<
>> the table definition here <<
> My problem is that I want to repeat TEXTBOX1 on all the pages if the table
> has more than one page,
> Right now only appear in the first page.
> I tried to set TEXTBOX1.RepeatWith = TABLE1 but it has no effect.
> In case it helps the value will not change of value in the report. I'm
> open
> to use any other mean like a parameter, etc

repeating a header with fields from the dataset

Hi,

I'm designing a report and have the requirement that the report title needs to be repeated on each page. However, the report title is dynamic, retrieved from the database, so i cannot place it in the page header (what an annoyance). The solution i first thought of was placing the title in a table, repeating the table header on each page, however the problem i'm encountering is that when i have a subreport in that table, that subreport is in one row

--> if the subreport spans across multiple pages, the header will only be shown on the first page the row where the subreport is actually in...

any thoughts anyone on a possible solution?

1. Place a hidden textbox in the report body with a value of =First(Fields!<TitleField>.Value).

2. Place another textbox in the page header referencing the first textbox with a value of =ReportItems!<Textbox1Name>.Value.

|||

hi

thanks for the reply but that actually doesn't quite fully solve my problem; apparently it will only display the value of the textbox on the page where that textbox WOULD be rendered; my report consists of several subreports, i placed the hidden textbox "after" all the subreports, and the title would only appear on the last page.

Putting the textbox at the first place in the report yielded similar results; it would only display on the first page...

|||i'm testing the report by exporting to pdf, by the way|||

Yes, the report item will come back as null if not rendered on the page. If possible, make the hidden textbox repeatable on every page. For example, if the report has a table put the hidden textbox on a detail table row. You will need to change the scope of the hidden textbox expression to reference the dataset, e.g. =First(Fields!<TitleField>.Value, "<DatasetName").

An ugly hack, I know.

repeating a header with fields from the dataset

Hi,

I'm designing a report and have the requirement that the report title needs to be repeated on each page. However, the report title is dynamic, retrieved from the database, so i cannot place it in the page header (what an annoyance). The solution i first thought of was placing the title in a table, repeating the table header on each page, however the problem i'm encountering is that when i have a subreport in that table, that subreport is in one row

--> if the subreport spans across multiple pages, the header will only be shown on the first page the row where the subreport is actually in...

any thoughts anyone on a possible solution?

1. Place a hidden textbox in the report body with a value of =First(Fields!<TitleField>.Value).

2. Place another textbox in the page header referencing the first textbox with a value of =ReportItems!<Textbox1Name>.Value.

|||

hi

thanks for the reply but that actually doesn't quite fully solve my problem; apparently it will only display the value of the textbox on the page where that textbox WOULD be rendered; my report consists of several subreports, i placed the hidden textbox "after" all the subreports, and the title would only appear on the last page.

Putting the textbox at the first place in the report yielded similar results; it would only display on the first page...

|||i'm testing the report by exporting to pdf, by the way|||

Yes, the report item will come back as null if not rendered on the page. If possible, make the hidden textbox repeatable on every page. For example, if the report has a table put the hidden textbox on a detail table row. You will need to change the scope of the hidden textbox expression to reference the dataset, e.g. =First(Fields!<TitleField>.Value, "<DatasetName").

An ugly hack, I know.

Repeat List across page, not just down?

I want to use a List (or similar), but I want the fields from each row of the source datatable to repeat across the page before wrapping to the next line. For example:

row1-FieldA row1-FieldB row2-FieldA row2-FieldB
row3-FieldA row3-FieldB row4... etc

Is this possible?

Quick and dirthy is to write the expression

select the required report item(table in ur list)

choose expression for property(value)

==Iif(RowNumber("table") mod 2,FieldA,"")

repeat data in matrix row group on every line

Hello everyone,
I have a matrix report that is grouped on 4 fields. I need the report
to display data for every line and not hide data for a group.
i.e
Currently:
Apex Merlot 750ml
375ml
1.5L
I need:
Apex Merlot 750ml
Apex Merlot 375ml
Apex Merlot 1.5L
thank you,
JustinSee the attached report for a sample of how to repeat your group data on
each line.
The structure below uses the terminology of your example
Row Group 1 Row Group 2 Row Group 3
Group by Wine Type Group by Quantity Group by Quantity
Display Wine Type Display Wine Type Display Quantity
You might be able to place Row Group 3 in a value cell.
You will need to strink Row Group 1's cell width and mostl like set its
background and text color to white.
You might consider using a table for this type of report since it becomes
rather trival to setup.
In this approach you would place the Wine Type and Quantity in a detail.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Justin" <stupy1@.hotmail.com> wrote in message
news:2e90a66b.0410050545.200d149@.posting.google.com...
> Hello everyone,
> I have a matrix report that is grouped on 4 fields. I need the report
> to display data for every line and not hide data for a group.
> i.e
> Currently:
> Apex Merlot 750ml
> 375ml
> 1.5L
> I need:
> Apex Merlot 750ml
> Apex Merlot 375ml
> Apex Merlot 1.5L
>
> thank you,
> Justin
RepeatMatrixGroupData.rdl
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.25in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>CustomerDataSet</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_ColumnGroup1">
<GroupExpressions>
<GroupExpression />
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>3.875in</Width>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_Country">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>0.875in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_RowGroup1">
<GroupExpressions>
<GroupExpression>=Fields!CompanyName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Country_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>Country_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_CompanyName">
<GroupExpressions>
<GroupExpression>=Fields!CompanyName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<CanGrow>true</CanGrow>
<rd:DefaultName>CompanyName</rd:DefaultName>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
</ReportItems>
<Style />
<Height>0.875in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>5f06a1b6-3d14-4331-a0a7-3192c902022e</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.50001in</Width>
<DataSets>
<DataSet Name="CustomerDataSet">
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactName">
<DataField>ContactName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactTitle">
<DataField>ContactTitle</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Address">
<DataField>Address</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Region">
<DataField>Region</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PostalCode">
<DataField>PostalCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Phone">
<DataField>Phone</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Fax">
<DataField>Fax</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT *
FROM Customers</CommandText>
</Query>
</DataSet>
<DataSet Name="CountryDataSet">
<Fields>
<Field Name="country">
<DataField>country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>select country from customers</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>8bc78064-37e7-4500-aba6-1ed9e3eb7429</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>|||Thanks Bruce. My customers are going to have to wait on this one.
Hopefully, this feature will be included in a future release.
thanks again,
Justin

Repeat a group header field in every page

I want a field in a group header to be repeated in every page. I have 3 fields in the group header - they are Account Name,Debit Balance and Credit Balance.

When i use the option repeat group header on each page...it repeats all the 3 fields..but i wanted to repeat only the account name on all the pages...
Can any one help me out..
Urgent.
Thanks in advance
Rohini RHi,

Split the group as two (Create two groups), GroupOne is Account name Group Two is debitbalance and credit balance.

now you can make "repeat group header" for individual group.

yours friendly,
K.Babu

Originally posted by RohiniR
I want a field in a group header to be repeated in every page. I have 3 fields in the group header - they are Account Name,Debit Balance and Credit Balance.

When i use the option repeat group header on each page...it repeats all the 3 fields..but i wanted to repeat only the account name on all the pages...
Can any one help me out..
Urgent.
Thanks in advance
Rohini R|||Thank U.

This is wat i have done for now.

But this makes a line waste.

I have group by account name and that particular account name's credit and debit balances..so i have to create 2 groups,both group by account name...place the balances in one group and account name in one group..repeat only the group with acc name.
But it makes one line extra for each acc name.

Is there any other solution.?

Regards,
Rohini

:p|||You can use a Conditional Suppress to hide the fields on the 2nd (3rd, 4th, etc) page.

(This uses CR 8.5 and RDC to create the reports) --> Right-click the field you only want to appear on the first page, Click Format. On the Common tab, click the box labeled 'x-2' next to Suppress. In the formula editor that comes up, type: InRepeatedGroupHeader = False|||Hi,

1. account name aligned left corner of the first group.

2. Credit and Debit Balances aligned right corner of the second group.

3. right click the group one select the "section format", then check the underlay following section:

Note: make sure the group one and group two section height be same for good view.

a line saved :thumb:

Originally posted by RohiniR
Thank U.

This is wat i have done for now.

But this makes a line waste.

I have group by account name and that particular account name's credit and debit balances..so i have to create 2 groups,both group by account name...place the balances in one group and account name in one group..repeat only the group with acc name.
But it makes one line extra for each acc name.

Is there any other solution.?

Regards,
Rohini

:p|||malleyo,
Thank U.
I tried as u said...but the credit & debit balances get supressed for full of the page except for the first time...
i. e . the balances appear in the group header only once for a page... for the rest of the group headers the balance does not appear.. Can u Help me out !! :blush:

Babu,
I tried to underlay the following section but the detail section overlaps on the group header..??What to do?

Regards,
Rohini|||Where you set the overlaps

you should set the overlaps option in the top of the groupone, so it overlap at bottom grouptwo

Group#1 (set here overlap)
AccountName

Group#2
Credit and Debit

now group#1 overlap to group#2

output look like this.
AccountName Credit and Debit

----

Originally posted by RohiniR

Babu,
I tried to underlay the following section but the detail section overlaps on the group header..??What to do?

Regards,
Rohini|||Originally posted by RohiniR
I tried as u said...but the credit & debit balances get supressed for full of the page except for the first time...
i. e . the balances appear in the group header only once for a page... for the rest of the group headers the balance does not appear.. Can u Help me out !!

Set the Suppress for ONLY the fields that you DO NOT want repeated after the first page. Set the Suppress for the Debit Balance and Credit Balance, but don't set anything for Account Name. Make sure you have Repeat Group Header on every page turned on. Your results wil be as follows:

{Page1}
Account Name DebitBalance Credit Balance

{Page2}
Account Name

{Page3}
Account Name

{Page4}
Account Name

... etc ...

For this, you would put all the fields into the same Group Header and you wouldn't need the overlay as KBabu suggested (although the overlay would work too ;) ).|||malleyo,
This option did not work well. Thanks for ur idea..

Babu,
This worked out well for other reports..But not for my particular report...I dont know why..i shd study over it......
Thank U

Rohini|||Hi Rohini,
Try the following.
Right click on Debit Balance field, select format field. In the suppress option, write the formula as given below
If GroupNumber>1 then True
Do the same for Credit Balance field also

Madhivanan|||Thanks for ur suggesstion
No this did not work well.

The balance displays for the first group..in a page and is suppressed for the others.
There may be more than one group in a page itself..

Rohini|||Hi Rohini,
Can you post the expected outcome with some sample data?

Madhivanan|||Hai Madhivanan,

Just Have a look at this sample data

Name Debit Bal Credit Bal
-------------------------
1.XYZ 1000
ggdgfdfgd gfsdg 200
shfghfghfgh 500
gjgsdftdf 300

2.ABC 2000
sfggfsdfsd 1500
hngdfgdf 500

3.........

This is the way the report goes..
The name XYZ,ABC are in a group header.
The other details are in detail section.
the balance for XYZ is 1000..the transactions exist for that 1000 rs are shown here.If it extends to the next page...Then the name XYZ should be displayed on the next page too... But not the balance 1000.i.e. 1000 should not be displayed in next page/

Now did u follow my problem?

Thanks ,
Rohini|||Hi Rohini,
Now I clearly understand what the problem is. To overcome this problem use running total, select type of summary as count, Evaluate for each record and reset on change of group. Then place that running total in the details sections. Now right click on Debit Bal ,select format field and in the suppress option write the formula as

If RunningTotalField > 1 then True

Do the same for Credit Bal also

If you dont want to display the running total suppress it.

I think this will give you a solution

Madhivanan|||Hi Rohini,
Did you get the solution? Let me know.

Madhivanan|||Hi madhi,
This did not work out becaz i cannot use running total field there
Just Have a look at this sample data

Name Debit Bal Credit Bal
-------------------------
1.XYZ 1000 -- always this will not be 200+500+300..it can be also 0 or it can be a derived formula field..that is opening or closing balance...
ggdgfdfgd gfsdg 200
shfghfghfgh 500
gjgsdftdf 300

2.ABC 2000
sfggfsdfsd 1500
hngdfgdf 500

3.........

sorry for no replying u.
Regards,
Rohini|||Hi madhi,
This did not work out becaz i cannot use running total field there

Just Have a look at this sample data again

Name Debit Bal Credit Bal
-------------------------
1.XYZ 1000 -- always this will not be 200+500+300..it can be also 0 or it can be a derived formula field..that is opening or closing balance...
ggdgfdfgd gfsdg 200
shfghfghfgh 500
gjgsdftdf 300

2.ABC 2000
sfggfsdfsd 1500
hngdfgdf 500

3.........

sorry for no replying u.
Regards,
Rohini|||Hi Rohini,
Actually there is no need to worry about the result of the running total. Just create a running total field having any one of the fileds of the table.select type of summary as count, Evaluate for each record and reset on change of group. Then place that running total in the details sections. Now right click on Debit Bal ,select format field and in the suppress option write the formula as

If RunningTotalField > 1 then True

Do the same for Credit Bal also

If you dont want to display the running total suppress it.

I tried this and it worked well. The running total field will not generate any sum as the type selected is count.

Madhivanan|||Hey, Sorry for asking such a dumb quest...where is this option of repeating group header on each page?

I have 3 group headers ...2 are suppresed...I want to repeat the unsupressed group header on each page. Please give me the steps to do that.

Thank you
Shruti|||hi sruti,
right click the group header and go to the 'change group' option
In that General and Options will be there...click 'Options'..and then select the 'Repeat Group Header On Each Page' check box

Try this & revert back..

Regards,
Rohini R :)

Re-ordering Table Fields

When I use the ALTER TABLE statement to Add new fields to a table, is there a
way to specify it's position on the table? I don't want it to be in the end
(last position) of the fields...
In Enterprise Manager, it does this by making a copy of the table with new
structure, droping the old and renaming the new... This is no god when I add
fields to a table with 1 million regs, in production environment... But I
also want to keep the structure the same as the development environment...
Does anyone knows how can I do that simply with T-SQL commands?
Thanks!http://vyaskn.tripod.com/administration_faq.htm#q11
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rafa®" <Rafa@.discussions.microsoft.com> wrote in message
news:3BC02B7C-2B8A-4A3A-9D3C-ABC7134A9F8D@.microsoft.com...
When I use the ALTER TABLE statement to Add new fields to a table, is there
a
way to specify it's position on the table? I don't want it to be in the end
(last position) of the fields...
In Enterprise Manager, it does this by making a copy of the table with new
structure, droping the old and renaming the new... This is no god when I add
fields to a table with 1 million regs, in production environment... But I
also want to keep the structure the same as the development environment...
Does anyone knows how can I do that simply with T-SQL commands?
Thanks!|||"Rafa®" <Rafa@.discussions.microsoft.com> wrote in message
news:3BC02B7C-2B8A-4A3A-9D3C-ABC7134A9F8D@.microsoft.com...
> When I use the ALTER TABLE statement to Add new fields to a table, is
> there a
> way to specify it's position on the table? I don't want it to be in the
> end
> (last position) of the fields...
> In Enterprise Manager, it does this by making a copy of the table with new
> structure, droping the old and renaming the new... This is no god when I
> add
> fields to a table with 1 million regs, in production environment... But I
> also want to keep the structure the same as the development environment...
> Does anyone knows how can I do that simply with T-SQL commands?
> Thanks!
Is there any particular reason why the column *needs* to be in a different
location? SQL Server internally will move the data around in it's storage
scheme depending on what datatype the column has.
You can use EM to do this, but it basically copies the data to a temp table,
drops the original table, recreates the original table and then copies the
data back in.
Rick Sawtell
MCT, MCSD, MCDBA

Re-ordering Table Fields

When I use the ALTER TABLE statement to Add new fields to a table, is there a
way to specify it's position on the table? I don't want it to be in the end
(last position) of the fields...
In Enterprise Manager, it does this by making a copy of the table with new
structure, droping the old and renaming the new... This is no god when I add
fields to a table with 1 million regs, in production environment... But I
also want to keep the structure the same as the development environment...
Does anyone knows how can I do that simply with T-SQL commands?
Thanks!
http://vyaskn.tripod.com/administration_faq.htm#q11
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:3BC02B7C-2B8A-4A3A-9D3C-ABC7134A9F8D@.microsoft.com...
When I use the ALTER TABLE statement to Add new fields to a table, is there
a
way to specify it's position on the table? I don't want it to be in the end
(last position) of the fields...
In Enterprise Manager, it does this by making a copy of the table with new
structure, droping the old and renaming the new... This is no god when I add
fields to a table with 1 million regs, in production environment... But I
also want to keep the structure the same as the development environment...
Does anyone knows how can I do that simply with T-SQL commands?
Thanks!
|||"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:3BC02B7C-2B8A-4A3A-9D3C-ABC7134A9F8D@.microsoft.com...
> When I use the ALTER TABLE statement to Add new fields to a table, is
> there a
> way to specify it's position on the table? I don't want it to be in the
> end
> (last position) of the fields...
> In Enterprise Manager, it does this by making a copy of the table with new
> structure, droping the old and renaming the new... This is no god when I
> add
> fields to a table with 1 million regs, in production environment... But I
> also want to keep the structure the same as the development environment...
> Does anyone knows how can I do that simply with T-SQL commands?
> Thanks!
Is there any particular reason why the column *needs* to be in a different
location? SQL Server internally will move the data around in it's storage
scheme depending on what datatype the column has.
You can use EM to do this, but it basically copies the data to a temp table,
drops the original table, recreates the original table and then copies the
data back in.
Rick Sawtell
MCT, MCSD, MCDBA

Re-ordering Table Fields

When I use the ALTER TABLE statement to Add new fields to a table, is there
a
way to specify it's position on the table? I don't want it to be in the end
(last position) of the fields...
In Enterprise Manager, it does this by making a copy of the table with new
structure, droping the old and renaming the new... This is no god when I add
fields to a table with 1 million regs, in production environment... But I
also want to keep the structure the same as the development environment...
Does anyone knows how can I do that simply with T-SQL commands?
Thanks!http://vyaskn.tripod.com/administration_faq.htm#q11
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:3BC02B7C-2B8A-4A3A-9D3C-ABC7134A9F8D@.microsoft.com...
When I use the ALTER TABLE statement to Add new fields to a table, is there
a
way to specify it's position on the table? I don't want it to be in the end
(last position) of the fields...
In Enterprise Manager, it does this by making a copy of the table with new
structure, droping the old and renaming the new... This is no god when I add
fields to a table with 1 million regs, in production environment... But I
also want to keep the structure the same as the development environment...
Does anyone knows how can I do that simply with T-SQL commands?
Thanks!|||"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:3BC02B7C-2B8A-4A3A-9D3C-ABC7134A9F8D@.microsoft.com...
> When I use the ALTER TABLE statement to Add new fields to a table, is
> there a
> way to specify it's position on the table? I don't want it to be in the
> end
> (last position) of the fields...
> In Enterprise Manager, it does this by making a copy of the table with new
> structure, droping the old and renaming the new... This is no god when I
> add
> fields to a table with 1 million regs, in production environment... But I
> also want to keep the structure the same as the development environment...
> Does anyone knows how can I do that simply with T-SQL commands?
> Thanks!
Is there any particular reason why the column *needs* to be in a different
location? SQL Server internally will move the data around in it's storage
scheme depending on what datatype the column has.
You can use EM to do this, but it basically copies the data to a temp table,
drops the original table, recreates the original table and then copies the
data back in.
Rick Sawtell
MCT, MCSD, MCDBA

Reolication Filters and linked tables

Hello
I have a table lets say Accounts with 2 fields Account_id and
account_name
and another table called AccountAddress with again 2 fields Account_Id
and Address_Id.
As you can imagine the Account_Id of table Accounts is linked with
Account_Id of table AccountAddress.
Now if a replicate my database and i put a filter on table accounts to
bring lets say accounts with account_name="test" ... shall i put a
filter to table AccountAddress too or this table will bring data for
those rows that exists in table account during synchronisation?
(I use merge replication in sql 2005 in order to replicate the
database on sqlce database)
Thanks a lot
Savvas
As long as there is a defined merge join filter the merge agent will take
into account the static filter.
Rgds,
Paul Ibison

Renumber column....

Hi everyone.. kindly help me out.. im a newbie by the way.. ihave this fields in my table... uid, linenum, name... my problem is everytime i wnt to delete a record i want the "line: field to be renumbered.. for exmple...
record 1:
uid=1, line=1, name=Bob
record 2:
uid=2, line=2, name=Greg
record 3:
uid=3, line=3, name=Don...

now, i deleted record 2... whatt should happen is line must be renumbered..
record 1:
uid=1, line=1, name=Bob
record 2:
uid=3, line=2, name=Don

im thingking that i should create a trigger... but how will be my approach in SQL script.. help.. pls...

Try this. It will subtract one from the current line for each record in the deleted table with a line number less than it. So, if the table starts with lines 1-10 and we delete lines 3,5, and 7. Lines 10, 9, and 8 should be decreased by 3; 6 by 2; and 4 by 1.

Drop Table BensTable go Create Table BensTable( uid int not null, linenum int not null, name varchar(100) null ) insert BensTable values (1, 1, 'Fred' ) insert BensTable values (2, 2, 'Barney' ) insert BensTable values (3, 3, 'Jane' ) insert BensTable values (4, 4, 'Wilma' ) insert BensTable values (5, 5, 'George' ) insert BensTable values (6, 6, 'Betty' ) insert BensTable values (7, 7, 'Astro' ) insert BensTable values (8, 8, 'Pebbles' ) insert BensTable values (9, 9, 'BamBam' ) insert BensTable values (10, 10, 'Dino' ) go Create Trigger Renumber on BensTable for Delete AS Begin Update B set linenum = linenum - ( Select Count(*) From deleted as d Where d.linenum < B.linenum ) From BensTable as b End go delete From BensTable where linenum in ( 3, 5, 7 ) select * from BensTable order by linenum

Triggers have 2 pseudo-tables available within them, called inserted and deleted. These can be used, like other tables, in queries to perform checks or other operations.

|||thank you very much...!!!!|||another question what if this is the scenario...

i have a table named tbl1 fields are... uid, name.. and another table named tbl2 fields are tbl1uid,hobbies, line
where tbl1uid foreign key from tbl1...

im going to delete a record.. on the tbl2 table... but the records that must be renumbered are records that has the uid of tbl1...

thanks for the help..|||

It is really bad idea to keep the line-number in the table itself. You can get the line-number while fetching the record. if you use SQL Server 2005 then you can utilize the ROW_NUMBER feature.

Which version of SQL Server are you using?

|||im using SQL server 2005 express edition...|||

If you use SQL Server 2005, you need not to keep a separate column. It is unnessary, it will cause additional overhead on every insert / update / delete and it will consume reasonable memory also,

Use the following logic to number your row,

Code Snippet

Create Table #data (

[uid] Varchar(100) ,

[name] Varchar(100)

);

Insert Into #data Values('1','Bob');

Insert Into #data Values('2','Greg');

Insert Into #data Values('3','Don');

Select uid,name,row_number() Over(order By uid) from #data

delete from #data Where uid=2

Select uid,name,row_number() Over(order By uid) from #data

|||

If your intention is keep the line number on the table itself then the following query help you.

Code Snippet

Create Table data (

[uid] Varchar(100) ,

[name] Varchar(100) ,

[line] int

);

Go

Create trigger data_renumber

on data for insert, delete

as

begin

;WithCTE

as

(

Select *, row_number() Over(order By uid) newline from data

)

Update CTE Set line = newline;

end

Go

Insert Into data(uid,name) Values('1','Bob');

Insert Into data(uid,name) Values('2','Greg');

Insert Into data(uid,name) Values('3','Don');

Go

Select *from data

delete from data Where uid=2

Select * from data

|||um.. sorry... but what if i'll implment the renumbering using a stored procedure? how will it be.. thanks..

Rendering report fields

Hi,

I am designing a report that creates a letter to send to a named individual. To accomodate different address lengths, additional fields have been added to the db. Is there a method by which if an address filed is empty, it does not display in the redered report and the fields below it are moved up to close the gap?

Any one any ideas?

Thanks

Yes, you can use just one textbox with line feed and carriage return characters appended in between conditionally something like this:

Fields!Address1.Value &

IIf(Fields!Address2.Value <> "", Chr(10) & Chr(13) & Fields!Address1.Value, "") &

IIf(Fields!City.Value <> "", Chr(10) & Chr(13) & Fields!City.Value, "") &

IIf(Fields!State.Value <> "", Chr(10) & Chr(13) & Fields!State.Value, "")

.....

Shyam

|||

Hi Shyam,

Thank you for your response. I am new to this and am not sure how the coding fits in. Below is what I have currently:

=First(Fields!Name.Value)
=First(Fields!Address1.Value)
=First(Fields!Address2.Value)
=First(Fields!Address3.Value)
=First(Fields!Town.Value)
=First(Fields!County.Value)
=First(Fields!Post_Code.Value)

A further example of where your code fits in would be a great help.

Thanks

|||

I guess you have 7 textboxes one below another. Now, delete the bottom 6 textboxes and put this expression in the top textbox:

=First(Fields!Name.Value) &

IIf(First(Fields!Address1.Value) <> "", Chr(10) & Chr(13) & First(Fields!Address1.Value), "") &

IIf(First(Fields!Address2.Value) <> "", Chr(10) & Chr(13) & First(Fields!Address2.Value), "") &

IIf(First(Fields!Address3.Value) <> "", Chr(10) & Chr(13) & First(Fields!Address3.Value), "") &

IIf(First(Fields!Town.Value) <> "", Chr(10) & Chr(13) & First(Fields!Town.Value), "") &

IIf(First(Fields!County.Value) <> "", Chr(10) & Chr(13) & First(Fields!County.Value), "") &

IIf(First(Fields!Post_Code.Value) <> "", Chr(10) & Chr(13) & First(Fields!Post_Code.Value), "") &

Shyam

|||

That works a treat.

Thanks for your help

|||

Hi Shyam,

That works fine in the dev studio but when rendered from the reporting server, all fields are rendered on the same line. Is there a fix for this?

Thanks