Please help me solve this problem

Hello All,
I am new to crystalreport am using crystalreport 8.5. I have to provide the link for
a column whose value is > 1000. i don't want to suppress the values other than1000.
My Problem is the link should be enabled only when the value is > 1000. Please share your experience to solve this problem.
Thanks in advance
Vasuformat the field of the column using a formula..

if(value>100, "link text" & value, value)

?|||Thanks a lot cjard. it works well. the link is enabled for value > 1000
thanks and regards
Vasu

Please Help !

Hi Experts,

I'm very new in ASP.NET 2.0. Got a simpe question. After I defined the 'SqlDataSource', how can I get the value (content) of a column to a control (whatever TextBox) ? I can Update a data row by using a TextBox conent to a column but don't know how to do it reversely.

Thanks in advance !

Stephen

Hey,

This is the syntax: http://book.itzero.com/read/microsoft/0602/Addison.Wesley.Data.Binding.with.Windows.Forms.2.0.Programming.Smart.Client.Data.Applications.with.dot.NET.Jan.2006_html/032126892X/app01lev1sec5.html

But you may need to put it in a form view or something like that.

|||

Let me check it first. Thanks !

stephen

Please help - urgent!

I have a matrix that displays data for tags per day on each page. The
matrix
has a grouped column that does the grouping by day (this column is returned
from a stored procedure) and page breaks at the end of the day.
This is what I need - before I page break, I need to also display the
statistics per day like avg, min and max values for that day for all the
tags.
Date Tag1 Tag2 Tag3 Tag4
======================================== 10/01/2003 00:00 2 4 6 7
10/01/2003 01:00 2 4 6 7
10/01/2003 02:00 2 4 6 7
10/01/2003 03:00 2 4 6 7
......
10/01/2003 21:00 2 4 6 7
10/01/2003 22:00 2 4 6 7
10/01/2003 23:00 2 4 6 7
========================================= Daily Statistics
--
Sum: 48 96 144 168
Average: 2 4 6
7
Min: 2 4 6
7
Max: 2 4 6
7
==========================================
======> the first one is the matrix (grouped by day and page breaks after
each day)
=======> The Daily Statistics part (summary) I am not able to do. Since the
matrix has a page break per
day, I am not able to get the statistics also on the same page for that day.
Any help will be highly appreciated. Thanks.Try putting your matrix inside a table. Your outer group goes in the
table -- with summaries in the table group footer -- and your inner group is
handled in the matrix.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"KMP" <KMP@.msn.com> wrote in message
news:%23dnvGhcFFHA.1936@.TK2MSFTNGP14.phx.gbl...
>I have a matrix that displays data for tags per day on each page. The
> matrix
> has a grouped column that does the grouping by day (this column is
> returned
> from a stored procedure) and page breaks at the end of the day.
> This is what I need - before I page break, I need to also display the
> statistics per day like avg, min and max values for that day for all the
> tags.
> Date Tag1 Tag2 Tag3 Tag4
> ========================================> 10/01/2003 00:00 2 4 6 7
> 10/01/2003 01:00 2 4 6 7
> 10/01/2003 02:00 2 4 6 7
> 10/01/2003 03:00 2 4 6 7
> ......
> 10/01/2003 21:00 2 4 6 7
> 10/01/2003 22:00 2 4 6 7
> 10/01/2003 23:00 2 4 6 7
> =========================================> Daily Statistics
> --
> Sum: 48 96 144
> 168
> Average: 2 4 6
> 7
> Min: 2 4 6
> 7
> Max: 2 4 6
> 7
> ==========================================> ======> the first one is the matrix (grouped by day and page breaks after
> each day)
> =======> The Daily Statistics part (summary) I am not able to do. Since
> the
> matrix has a page break per
> day, I am not able to get the statistics also on the same page for that
> day.
> Any help will be highly appreciated. Thanks.
>
>
>|||Thank you very much. It worked.
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:uyFiy2cFFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Try putting your matrix inside a table. Your outer group goes in the
> table -- with summaries in the table group footer -- and your inner group
is
> handled in the matrix.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "KMP" <KMP@.msn.com> wrote in message
> news:%23dnvGhcFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> >I have a matrix that displays data for tags per day on each page. The
> > matrix
> > has a grouped column that does the grouping by day (this column is
> > returned
> > from a stored procedure) and page breaks at the end of the day.
> >
> > This is what I need - before I page break, I need to also display the
> > statistics per day like avg, min and max values for that day for all the
> > tags.
> >
> > Date Tag1 Tag2 Tag3 Tag4
> > ========================================> > 10/01/2003 00:00 2 4 6 7
> > 10/01/2003 01:00 2 4 6 7
> > 10/01/2003 02:00 2 4 6 7
> > 10/01/2003 03:00 2 4 6 7
> > ......
> > 10/01/2003 21:00 2 4 6 7
> > 10/01/2003 22:00 2 4 6 7
> > 10/01/2003 23:00 2 4 6 7
> > =========================================> > Daily Statistics
> > --
> > Sum: 48 96 144
> > 168
> > Average: 2 4 6
> > 7
> > Min: 2 4 6
> > 7
> > Max: 2 4 6
> > 7
> > ==========================================> >
> > ======> the first one is the matrix (grouped by day and page breaks
after
> > each day)
> > =======> The Daily Statistics part (summary) I am not able to do. Since
> > the
> > matrix has a page break per
> > day, I am not able to get the statistics also on the same page for that
> > day.
> >
> > Any help will be highly appreciated. Thanks.
> >
> >
> >
> >
> >
>sql

Please help - unusual warning when creating a column

Hi everyone,
This is weird. I create a new column programatically using the "ALTER
TABLE" command (you know the one) inside SQL Query Analyzer. The
column is indeed created but the following warning is thrown:
WARNING: The table 'OrderRecords' has been created but its maximum row
size (15,420) exceeds the maximum number of bytes per row (8060).
INSERT or UPDATE of a row in this table will fail if the resulting row
length exceeds 8060 bytes
I then checked a likely column. I ran a wee query to find the maximum
row and it returned a row of maximum length of 2,647 characters.
Now I'm totally confused. Does anyone know why such a warning is being
thrown? Any suggestions/comments/ideas/queries to run - much
appreciated.
Puzzled,
Al.
The whole size of the rows exceeds 8060 bytes, thats the most possible
bytes SQl Server can handle per row, so this is just a warning (not an
error), that it might could be if you exceed the magic border, data
will be truncated.
If you are aware of that and you=B4ll be happy with it or you have
another way to handle this, this is fine, but you should keep that in
mind. For the best you should redesign your table by putting some
values in another table or thinking of changing the actual types of the
columns, if that is possible.
HTH, Jens Suessmeyer.
|||This means that the sum of all you columns can in theory be 15,420
bytes
If someone inserts data and it exceeds 8060 bytes, then the statement
will fail
look at all the field sizes in this table and make sure they don't
exceed 8060 bytes
http://sqlservercode.blogspot.com/
|||> This is weird. I create a new column programatically using the "ALTER
> TABLE" command (you know the one) inside SQL Query Analyzer. The
> column is indeed created but the following warning is thrown:
> WARNING: The table 'OrderRecords' has been created but its maximum row
> size (15,420) exceeds the maximum number of bytes per row (8060).
> INSERT or UPDATE of a row in this table will fail if the resulting row
> length exceeds 8060 bytes
It is a WARNING about the maximum possible rowsize based on the columns
defined for the table. Just guessing, but it is likely that you have a
couple (or a bunch) of large varchar columns in the table that could, if
completely filled with data, exceed the maximum possible rowsize supported
by sql server.
|||Thanks everyone - that's fine. I can live with that. Thanks for all
your feedback.
Al.
The happy one.

Please help - unusual warning when creating a column

Hi everyone,
This is weird. I create a new column programatically using the "ALTER
TABLE" command (you know the one) inside SQL Query Analyzer. The
column is indeed created but the following warning is thrown:
WARNING: The table 'OrderRecords' has been created but its maximum row
size (15,420) exceeds the maximum number of bytes per row (8060).
INSERT or UPDATE of a row in this table will fail if the resulting row
length exceeds 8060 bytes
I then checked a likely column. I ran a wee query to find the maximum
row and it returned a row of maximum length of 2,647 characters.
Now I'm totally confused. Does anyone know why such a warning is being
thrown? Any suggestions/comments/ideas/queries to run - much
appreciated.
Puzzled,
Al.The whole size of the rows exceeds 8060 bytes, thats the most possible
bytes SQl Server can handle per row, so this is just a warning (not an
error), that it might could be if you exceed the magic border, data
will be truncated.
If you are aware of that and you=B4ll be happy with it or you have
another way to handle this, this is fine, but you should keep that in
mind. For the best you should redesign your table by putting some
values in another table or thinking of changing the actual types of the
columns, if that is possible.
HTH, Jens Suessmeyer.|||This means that the sum of all you columns can in theory be 15,420
bytes
If someone inserts data and it exceeds 8060 bytes, then the statement
will fail
look at all the field sizes in this table and make sure they don't
exceed 8060 bytes
http://sqlservercode.blogspot.com/|||> This is weird. I create a new column programatically using the "ALTER
> TABLE" command (you know the one) inside SQL Query Analyzer. The
> column is indeed created but the following warning is thrown:
> WARNING: The table 'OrderRecords' has been created but its maximum row
> size (15,420) exceeds the maximum number of bytes per row (8060).
> INSERT or UPDATE of a row in this table will fail if the resulting row
> length exceeds 8060 bytes
It is a WARNING about the maximum possible rowsize based on the columns
defined for the table. Just guessing, but it is likely that you have a
couple (or a bunch) of large varchar columns in the table that could, if
completely filled with data, exceed the maximum possible rowsize supported
by sql server.|||Thanks everyone - that's fine. I can live with that. Thanks for all
your feedback.
Al.
The happy one.

Please help - unusual warning when creating a column

Hi everyone,
This is weird. I create a new column programatically using the "ALTER
TABLE" command (you know the one) inside SQL Query Analyzer. The
column is indeed created but the following warning is thrown:
WARNING: The table 'OrderRecords' has been created but its maximum row
size (15,420) exceeds the maximum number of bytes per row (8060).
INSERT or UPDATE of a row in this table will fail if the resulting row
length exceeds 8060 bytes
I then checked a likely column. I ran a wee query to find the maximum
row and it returned a row of maximum length of 2,647 characters.
Now I'm totally confused. Does anyone know why such a warning is being
thrown? Any suggestions/comments/ideas/queries to run - much
appreciated.
Puzzled,
Al.The whole size of the rows exceeds 8060 bytes, thats the most possible
bytes SQl Server can handle per row, so this is just a warning (not an
error), that it might could be if you exceed the magic border, data
will be truncated.
If you are aware of that and you=B4ll be happy with it or you have
another way to handle this, this is fine, but you should keep that in
mind. For the best you should redesign your table by putting some
values in another table or thinking of changing the actual types of the
columns, if that is possible.
HTH, Jens Suessmeyer.|||This means that the sum of all you columns can in theory be 15,420
bytes
If someone inserts data and it exceeds 8060 bytes, then the statement
will fail
look at all the field sizes in this table and make sure they don't
exceed 8060 bytes
http://sqlservercode.blogspot.com/|||> This is weird. I create a new column programatically using the "ALTER
> TABLE" command (you know the one) inside SQL Query Analyzer. The
> column is indeed created but the following warning is thrown:
> WARNING: The table 'OrderRecords' has been created but its maximum row
> size (15,420) exceeds the maximum number of bytes per row (8060).
> INSERT or UPDATE of a row in this table will fail if the resulting row
> length exceeds 8060 bytes
It is a WARNING about the maximum possible rowsize based on the columns
defined for the table. Just guessing, but it is likely that you have a
couple (or a bunch) of large varchar columns in the table that could, if
completely filled with data, exceed the maximum possible rowsize supported
by sql server.|||Thanks everyone - that's fine. I can live with that. Thanks for all
your feedback.
Al.
The happy one.

Please help

Tab1 and Tab2 has the following info.ID column in Tab1 and Tab2 has
matching values.
Tab3 should have the info as below.
Tab1
ID Val1 Val2
1 qqq sa
2 aaa fa
3 ddd we
Tab2
ID F1
1 A
1 B
1 C
1 D
2 F
2 G
3 H
Output Should be as blelow, without using the functions please advise the
sql query.
Tab3
ID Val1 Val2 F1
1 qqq sa A,B,C,D
2 aaa fa F,G
3 ddd we H
Thanks in advance.
It's not all that pretty, but the following function will do the job,
there may be more efficient approaches, but this one does work.
--============================================
CREATE FUNCTION JoinRows(@.id int)
RETURN varchar(50)
AS
BEGIN
DECLARE @.f1 char(1)
DECLARE @.str varchar(50)
SET @.f1 = ''
SET @.str = ''
WHILE 1=1
BEGIN
SELECT top 1 @.f1 = f1
,@.str = @.str + f1 + ','
FROM tab2
WHERE id = @.id
AND f1 > @.f1
IF @.@.rowcount = 0 BREAK
END
IF LEN(@.str) > 1
SET @.str = SUBSTRING(@.str,1,len(@.str)-1)
RETURN @.str
END
--============================================
-- This select query will then return Tab3
SELECT
*, dbo.JoinRows(id)
FROM tab1
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <u4oLJnfxFHA.736@.tk2msftngp13.phx.gbl>, dkrreddy@.hotmail.com
says...
> Tab1 and Tab2 has the following info.ID column in Tab1 and Tab2 has
> matching values.
> Tab3 should have the info as below.
> Tab1
> --
> ID Val1 Val2
> 1 qqq sa
> 2 aaa fa
> 3 ddd we
>
> Tab2
> --
> ID F1
> 1 A
> 1 B
> 1 C
> 1 D
> 2 F
> 2 G
> 3 H
>
> Output Should be as blelow, without using the functions please advise the
> sql query.
> Tab3
> --
> ID Val1 Val2 F1
> 1 qqq sa A,B,C,D
> 2 aaa fa F,G
> 3 ddd we H
>
> Thanks in advance.
>
>
|||The following will work as well:
declare @.f1str varchar(100)
set @.f1str = ''
select @.f1str = @.f1str + f1 + ','
from F1
select @.f1str
You'd have to run it an ID at a time in a function but at least it's
only one execution per ID as opped to F1.
Hope that helps.

Please help

Tab1 and Tab2 has the following info.ID column in Tab1 and Tab2 has
matching values.
Tab3 should have the info as below.
Tab1
--
ID Val1 Val2
1 qqq sa
2 aaa fa
3 ddd we
Tab2
--
ID F1
1 A
1 B
1 C
1 D
2 F
2 G
3 H
Output Should be as blelow, without using the functions please advise the
sql query.
Tab3
--
ID Val1 Val2 F1
1 qqq sa A,B,C,D
2 aaa fa F,G
3 ddd we H
Thanks in advance.It's not all that pretty, but the following function will do the job,
there may be more efficient approaches, but this one does work.
-- ========================================
====
CREATE FUNCTION JoinRows(@.id int)
RETURN varchar(50)
AS
BEGIN
DECLARE @.f1 char(1)
DECLARE @.str varchar(50)
SET @.f1 = ''
SET @.str = ''
WHILE 1=1
BEGIN
SELECT top 1 @.f1 = f1
,@.str = @.str + f1 + ','
FROM tab2
WHERE id = @.id
AND f1 > @.f1
IF @.@.rowcount = 0 BREAK
END
IF LEN(@.str) > 1
SET @.str = SUBSTRING(@.str,1,len(@.str)-1)
RETURN @.str
END
-- ========================================
====
-- This select query will then return Tab3
SELECT
*, dbo.JoinRows(id)
FROM tab1
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <u4oLJnfxFHA.736@.tk2msftngp13.phx.gbl>, dkrreddy@.hotmail.com
says...
> Tab1 and Tab2 has the following info.ID column in Tab1 and Tab2 has
> matching values.
> Tab3 should have the info as below.
> Tab1
> --
> ID Val1 Val2
> 1 qqq sa
> 2 aaa fa
> 3 ddd we
>
> Tab2
> --
> ID F1
> 1 A
> 1 B
> 1 C
> 1 D
> 2 F
> 2 G
> 3 H
>
> Output Should be as blelow, without using the functions please advise the
> sql query.
> Tab3
> --
> ID Val1 Val2 F1
> 1 qqq sa A,B,C,D
> 2 aaa fa F,G
> 3 ddd we H
>
> Thanks in advance.
>
>|||The following will work as well:
declare @.f1str varchar(100)
set @.f1str = ''
select @.f1str = @.f1str + f1 + ','
from F1
select @.f1str
You'd have to run it an ID at a time in a function but at least it's
only one execution per ID as opped to F1.
Hope that helps.sql

please give any example for using UpdateQueryColumns Property

please help me...

what i required is, When a table is updated i want to get the column names
that affected , I think that UpdateQueryColumns Propertey may help me..

If what i am thinking is correct,please specify how to use that propertey,

otherwise specify how i can satisfy my requirement..

Thanks in advance........

Quote:

Originally Posted by ramesh1210

please help me...

what i required is, When a table is updated i want to get the column names
that affected , I think that UpdateQueryColumns Propertey may help me..

If what i am thinking is correct,please specify how to use that propertey,

otherwise specify how i can satisfy my requirement..

Thanks in advance........

create a trigger. use the UPDATE(field) function

Please advise

Tab1 and Tab2 has the following info.ID column in Tab1 and Tab2 has
matching values.
Tab3 should have the info as below.
Tab1
--
ID Val1 Val2
1 qqq sa
2 aaa fa
3 ddd we
Tab2
--
ID F1
1 A
1 B
1 C
1 D
2 F
2 G
3 H
Output Should be as blelow, without using the functions please advise the
sql query.
Tab3
--
ID Val1 Val2 F1
1 qqq sa A,B,C,D
2 aaa fa F,G
3 ddd we H
Thanks in advance.See: http://www.aspfaq.com/show.asp?id=2529
Razvan|||See: http://www.aspfaq.com/show.asp?id=2529
Razvan

Placing Sub Total Coulmn to the left of the coulumn in Matrix report

Hi,

If I am taking a Matrix and right clicking on the column header and click on the SubTotal then it always place that column on the right of it .If I want to place that column to the left of my original column then I can't do it.

Adding manual column and then puuting the Expresstion =Sum(Fields!MyCol.Value) is not halping as it will give me the same value that is there in the column instead of giving me the column

-Thanks,

Digs

Putting the same expression in the Row Group Header would give you some sort of a SubTotal to the left of your Data Column. However, this will obviously only give you only the sum and will not follow the same formatting & properties etc. as that in the Data Column.

HTH.

-Aayush

Placing a sum in a table header column

I have been requested to add the sum of an interger field to the table header. I have the sum in the footer (which is very easy to do), but I cannot get the sum to appear in the table header.

I then set-up the stored procedure to run the sum, and place it into a dummy field. I still cannot add this field to the table header. Instead of printing the data for the dummy field (the correct total), it instead prints the actual field name on the report.

Is there anyway to place a sum in a table header on a SQL Server Report?

Let me know.

Thank you,

T.J.

You should be able to just copy the code that you're using in the sum field in your table footer into a textbox in the table header.

Jarret

|||

Thank you very much. I got busy and didn't get time to get back to this yesterday.

I tested this today, and if I just drop the field into a blank header cell, the number displays correctly.

But when I place this field in with text (such as "# of Boxes" and then add in the summed field, it displays the text of the field name, rather than the value (the number).

Very strange. When mixed with text, it displays the field name rather than the actual value.

Is there anyway to work around this?

Thank you,

T.J.

|||

Can you post your expression you have in your textbox?

You should be able to use something like this:

="# of Boxes - " & count(Fields!boxes.Value)

Jarret

|||

Yes, and thank you...

"# of Boxes - " & Sum(Fields_CountBoxes.Value)

When I preview this, it displays exactly like my formula.

Where if I just drop Sum(Fields_CountBoxes.Value) into the column header, it gives just the summed number I want to display.

|||

You are missing the '='. Try putting this into your textbox:

="# of Boxes - " & Sum(Fields_CountBoxes.Value)

If you don't have the '=' at the front, it takes whatever you have in there as literal. The '=' makes it an expression.

Jarret

|||

Oh, what an over sight.

Thank you very much, that fixed my error!

|||

Glad I could help TJ. Can you mark this one as answered so others can see this solution?

Thanks.

Jarret

Placement of Null in result set - sort order

Hi All,
This is a really crappy question that I found a responce to ages ago, but cannot find on the forum just now.

Whe I sort by a column that has null, then the NULL values are either in the top or bottom of the result set depending on whether the order by is asc or desc.

If I have a table
t with a column c which has 5 rows with values 1,2,3,4,NULL
************************************

create table #t(c int)

insert into #t values(1)
insert into #t values(2)
insert into #t values(3)
insert into #t values(4)
insert into #t values(null)

*************************
Then.....
select *
from #t order by 1 asc
results in ....
NULL
1
2
3
4

*******************
and...
select *
from #t order by 1 desc
results in.,...
4
3
2
1
NULL

I need null always at the end. so that I either get
1
2
3
4
NULL

or

4
3
2
1
NULL

Any ideas?

PeterYou have this problem only with ASC. In this case, order like this:

order by isnull(c, 2^31 - 1 ) ASC

instead of

order by 1 ASC|||Yo,
why are you using a bit operator.
can't I just use...
select *
from #t order by isnull(c, 4000 )|||Actually, it isn't a bit operator, but the Power operator. Anything is fine which is larger than your largest number to be expected of c.

placement of a new column

I was wondering if there is a way to add a new column to a table with
specific instruction as to where it should be created. i.e. after the 4th
column. Currently, a new column is added at the end of the table which I am
trying to avoid.
The solution I am looking for is via a script and not using the design table
option in the enterprise manager.
Many thanks
Shahriar> Currently, a new column is added at the end of the table which I am
> trying to avoid.
WHY'''''''? Can you please explain why
column order is important?
Enterprise Manager can kludge this for you, but PLEASE, PLEASE, PLEASE read
http://www.aspfaq.com/2528 before you try this on a table with anything more
than a handful of rows. You could certainly script something like what
Enterprise Manager does, but this is not using ALTER TABLE at all, and it is
a good 12kb worth of code. And as I already noted, you don't want to do
this on a large table, because it will make a complete copy of your existing
data before it's done. This can double (or more) your database size (albeit
temporarily). Plus the table is basically offline for that entire amount of
time, because you need to wrap the activity in a transaction...
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Nope. The only way to do that is to do it the way EM does. Build your new
table exactly how you want it, copy all data over to it, and then drop the
old table.
"Shahriar" <Shahriar@.discussions.microsoft.com> wrote in message
news:A0C8E551-5ED0-4C32-9929-904AA088FB82@.microsoft.com...
>I was wondering if there is a way to add a new column to a table with
> specific instruction as to where it should be created. i.e. after the
> 4th
> column. Currently, a new column is added at the end of the table which I
> am
> trying to avoid.
> The solution I am looking for is via a script and not using the design
> table
> option in the enterprise manager.
> Many thanks
> Shahriar|||From ELmars post:
"o Rename table with constraints and all (use sp_rename) Don't forget
referencing foreign keys.
o Create new table definition.
o INSERT data from the old table to the new table
o Create new referencing foreign keys pointing to new table.
o Drop old table
"
HTH, Jens Sü?meyer.
http://www.sqlserver2005.de
--|||"Jens S?meyer" > From ELmars post:
> "o Rename table with constraints and all (use sp_rename) Don't forget
> referencing foreign keys.
If I do it using EM, EM will take care of foreignkey, default, etc?|||Column order is not that important but if you need to really have it in a
specific
order, you'll need to dump the existing table into a temp table, re-create
the table with
the column ordering you want, and then re-insert the rows. To me, it
seems more trouble than it's worth.
"Shahriar" <Shahriar@.discussions.microsoft.com> wrote in message
news:A0C8E551-5ED0-4C32-9929-904AA088FB82@.microsoft.com...
> I was wondering if there is a way to add a new column to a table with
> specific instruction as to where it should be created. i.e. after the
4th
> column. Currently, a new column is added at the end of the table which I
am
> trying to avoid.
> The solution I am looking for is via a script and not using the design
table
> option in the enterprise manager.
> Many thanks
> Shahriar|||
"AB - MVP" wrote:

> WHY'''''''? Can you please explain w
hy
> column order is important?
>
There are few reasons that comes in my mind, consistency is one of them.
In dealing with many tables, in my case over 180 of them, the placement
order is indeed important. Here is a little example. All my tables have a
field at the end called dateCreated. Opening EM, I can quickly locate that
column.
Second, I ususaly like to place primary keys first, then index field key
nexts, and so on.. its a practice I have been accustomed to. Third, I
believe it is a practice that all DBA's have beed adapted to..All reserved
fields are usually placed at the end of the tables and not scattered around
in a table.
I hope that was a satisfactory answer to your question.
Thanks
Shahriar|||Yes it will.
"js" wrote:

> "Jens S¨1?meyer" > From ELmars post:
> If I do it using EM, EM will take care of foreignkey, default, etc?
>
>|||Shahriar,
Relational databases are not there to satisfy the whims and fancies of a
single user. The underlying framework is based on Completeness, Generality,
Formality & Simplicity, all with its foundation on set mathematics & logic.
It is this principle-based framework that makes relational databases
superior to alternatives.
Just because you like to do something with your table management interface
in the EM, does not make it a meaningful and valid reason for the DBMS to
have such a provision. Having look-a-like tables does not mean they are
"consistent" under the above mentioned framework.
Again, that is your personal choice. Some other user might prefer a
different choice.
There is no such general practice. Note that, there are certain specific
cases where SQL assigns positional significance to the order of columns in a
table, but that has nothing to do with user preferences or general
practices.
Anith|||Anith writes in regard to the relevancy of a column position in a table :
> Relational databases are not there to satisfy the whims and fancies of a
> single user. The underlying framework is based on Completeness ,
> Generality, Formality & Simplicity, all with its foundation on set
> mathematics & logic.
DO YOUR TABLES LOOK LIKE THIS'
Fname, Address4,Zip, Telephone, MI, DOB,Lname,Address1, State,
Address2,email,Address3
Shahriar
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OYMP3vkUFHA.928@.TK2MSFTNGP15.phx.gbl...
> Shahriar,
> Relational databases are not there to satisfy the whims and fancies of a
> single user. The underlying framework is based on Completeness,
> Generality, Formality & Simplicity, all with its foundation on set
> mathematics & logic. It is this principle-based framework that makes
> relational databases superior to alternatives.
>
> Just because you like to do something with your table management interface
> in the EM, does not make it a meaningful and valid reason for the DBMS to
> have such a provision. Having look-a-like tables does not mean they are
> "consistent" under the above mentioned framework.
>
> Again, that is your personal choice. Some other user might prefer a
> different choice.
>
> There is no such general practice. Note that, there are certain specific
> cases where SQL assigns positional significance to the order of columns in
> a table, but that has nothing to do with user preferences or general
> practices.
> --
> Anith
>

PL/SQL help - split an address

I have a single address column that i want to split.
For example I have an address with carriage returns like:

address
----------
Administration Tech Services

1234 Elm Avenue

West Building

I would like a SELECT query to split this address column into 3 like:
address address2 address3
---- ---- ----
Administration Tech Services 1234 Elm Avenue West BuildingYou may need to create a function like this:
Create Or Replace Function Get_Addr
(P_Line Number, P_Addr Varchar2)
Return Varchar2 Is
Type Addr_Typ Is Table Of Varchar2(1000);
V_Addr Addr_Typ;
V_Tmp Varchar2(1000);
I Pls_Integer;
J Pls_Integer;
K Pls_Integer;
L Pls_Integer;
Begin
V_Tmp:=Rtrim(P_Addr)||Chr(10);
For I In 1..P_Line Loop
V_Addr(I):='';
End Loop;
I:=0;
L:=Length(V_Tmp);
While (L > 0)
Loop
K:=Instr(V_Tmp,Chr(10));
If K = 0 Then
Exit;
End If;
J:= K-1;
If J > 0 Then
I:=I+1;
V_Addr(I):= Substr(V_Tmp,1,J);
V_Tmp:=Rtrim(Substr(V_Tmp||' ',K+1));
End If;
L:=Length(V_Tmp);
End Loop;
Return V_Addr(P_Line);
End;
/
And use it like:

UPDATE MyAddrTab
SET Address2=Get_Addr(2,Address)
, Address3=Get_Addr(3,Address);
COMMIT;
UPDATE MyAddrTab
SET Address=Get_Addr(1,Address);
COMMIT;
:eek:

PS: You will need to validate parameters!

PL/sql

I have problem in creating index on particular column with pL/SQL procedure . I dont know what command to and how to use?
how to run that PL/sql procedure. And howto check that wether index is created or not?
Please guide meHere's the example:

/* create a procedure */
CREATE OR REPLACE PROCEDURE brisime
AS
ci VARCHAR2 (255);
BEGIN
ci := 'create index i1 on tob_pool_ocit (pool_id, omm_id)';

EXECUTE IMMEDIATE ci;
END;

/* execute the procedure */
BEGIN
brisime ();
END;

/* checking whether it is created */
SELECT *
FROM user_indexes
WHERE index_name = 'I1';

PK on computed column

Maybe I am missing something very obvious, but I couldn't do it:

begin tran
go
create table foo (
f1 int not null,
f2 int not null,
f3 as (f1 + f2) not null primary key clustered)
go
rollback tran
go

This returns:
Server: Msg 8183, Level 16, State 1, Line 8
Only UNIQUE or PRIMARY KEY constraints are allowed on computed columns.Never mind, this worked:

begin tran
go
create table foo (
f1 int not null,
f2 int not null,
f3 as isnull((f1 + f2), 0) primary key clustered)
go
rollback tran
go|||My machines don't care for that either, although logically they shouldn't object.

-PatP|||OK...for the life of me....why?|||Actually it sarted with RogerWilco's post (http://www.dbforums.com/t1006214.html (http://www.dbforums.com/t1006214.html)), where I was trying to demonstrate that while having 4 fields instead of 1 does not mean that you will have to perform every join on all 4 fields. And sure enough, you also can make the computed column a PK, and have a UNIQUE constraint defined on it, needless to say create indexes. Of course, when we start talking about doing those things, Brett's belief that "one should leave all connection settings to default" would have to be shattered, because this is exactly the situation where not only you have to change them, but also understand the implications of changing each and one of them...But that may be easily transfered to a dedicated thread...I already see its name...

PK column

I need to make is to my users can not accidentally delete or replace the pK field, how do I do that anyone got any suggestions??Ummmm..huh :confused:

USE Northwind
GO

CREATE TABLE myTable99(Col1 int PRIMARY KEY)
GO

INSERT INTO myTable99(Col1) SELECT 1 UNION ALL SELECT 2
GO

SELECT * FROM myTable99
GO

DELETE FROM myTable99 WHERE Col1 = 1
GO

SELECT * FROM myTable99
GO

UPDATE myTable99 SET Col1 = 1 WHERE Col1 = 2
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||Ummmm..huh :confused:Yeah, what Brett said.

-PatP|||use pubs
go
deny update, delete on authors (au_id) to public
go|||thank you so much guys I appreciate it :). How do you donate to this website anyways??|||I explained that incorrectly what I am trying to say is I dont want my users to delete, or replace the data in the primary key field. Does that Make sense?|||Then this will suffice:

deny update on <your_table>(<your_primary_key>) to <your_users_or_public>

PIVOT with dynamic columns names created

I am trying to do a PIVOT on a query result, but the column names created by the PIVOT function are dynamic.

For example (modified from the SQL Server 2005 Books Online documentation on the PIVOT operator) :

SELECT
Division,
[2] AS CurrentPeriod,
[1] AS PreviousPeriod
FROM
(
SELECT
Period,
Division,
Sales_Amount
FROM
Sales.SalesOrderHeader
WHERE
(
Period = @.period
OR Period = @.period - 1
)
) p
PIVOT
(
SUM (Sales_Amount)
FOR Period IN ( [2], [1] )
) AS pvt

Let's assume that any value 2 is selected for the @.period parameter, and returns the sales by division for periods 2 and 1 (2 minus 1).

Division CurrentPeriod PreviousPeriod
A 400 3000
B 400 100
C 470 300
D 800 2500
E 1000 1900

What if the value @.period were to be changed, to say period 4 and it should returns the sales for periods 4 and 3 for example, is there a way I can change to code above to still perform the PIVOT while dynamically accepting the period values 4 and 3, applying it to the columns names in the first SELECT statement and the FOR ... IN clause in the PIVOT statement ?

Need a way to represent the following [2] and [1] column names dynamically depending on the value in the @.period parameter.

[2] AS CurrentPeriod,
[1] AS PreviousPeriod

FOR Period IN ( [2], [1] )

I have tried to use the @.period but it doesn't work.

Thanks in advance.

Kenny

This is a one drawback to the current Pivot feature. You will have to use dynamic sql for this.

Itzik has written a good article on this.

http://www.sqlmag.com/Article/ArticleID/94268/sql_server_94268.html

pivot t-sql adding a percentage column

in a cross table result is very common to add a percentage column in respect

of the sum of another column

for example

x z%

a 2 0,46 = 2/(2+5+6)

b 5 0,38 = 5/(2+5+6)

c 6 0,15 = 6/(2+5+6)

is there an easy way to do that in t-sql using the pivot command ?

This was going to be my first answer

"Not easily that I can think of because it requires knowledge of the other rows in the result set and that is where SQL is not very good. That is performing calculations at two levels one aggregate is across th

You need to find the total and then calculate the percentage. This is normally done using a derived table"

However in digging I looked into windowing. This is a new feature that can be used with the new ranking functions as well as the normal aggregates.

In your situation you can do

select x, 1.0*x / sum(x) over (parition by 1) percentage

from mytable

What this does is that it performs a sum of x over the partition of 1 (as this is the same for each row it does a sum across the whole resultset).