Altering columns...getting complicated...

I have a need to write many scripts to alter a LOT the underlying database
structure of our database in the field. We have many fields out there with
a type of float, and I've been told to change those to numeric(19,5) -- easy
enough. Unless there is a constraint, in which case I have to drop the
constraint, alter the field, add the constraint back in. Easy enough again,
once you know what you are doing.
Now, they tell me to change all the nvarchar(XX) fields to varchar(XX) --
easy enough again, unless they have a default -- use the same scheme as
above, and it all works. UNLESS they are part of a primary key. Uh oh --
now I hit something I don't know how to solve...
What I'm thinking is that I should dump all of the indexes and primary keys
and defaults out of all tables, and then just rebuild them all from scratch.
However, this database was "created" by using the Access upsizing wizard, so
I don't know all the primary key names, constraint names, etc.
Can anyone point me in the right direction to dump all indexes and defaults
on every column in a database? I can re-create them pretty easily...
Any advice would be appreciated, or even an alternate method to do what I
need to do.
Thanks in advance.
Matt
In message <OU#WxZ8WFHA.2420@.TK2MSFTNGP12.phx.gbl>, YYZ <none@.none.com>
writes
>I have a need to write many scripts to alter a LOT the underlying database
>structure of our database in the field. We have many fields out there with
>a type of float, and I've been told to change those to numeric(19,5) -- easy
>enough. Unless there is a constraint, in which case I have to drop the
>constraint, alter the field, add the constraint back in. Easy enough again,
>once you know what you are doing.
>Now, they tell me to change all the nvarchar(XX) fields to varchar(XX) --
>easy enough again, unless they have a default -- use the same scheme as
>above, and it all works. UNLESS they are part of a primary key. Uh oh --
>now I hit something I don't know how to solve...
>What I'm thinking is that I should dump all of the indexes and primary keys
>and defaults out of all tables, and then just rebuild them all from scratch.
>However, this database was "created" by using the Access upsizing wizard, so
>I don't know all the primary key names, constraint names, etc.
>Can anyone point me in the right direction to dump all indexes and defaults
>on every column in a database? I can re-create them pretty easily...
>Any advice would be appreciated, or even an alternate method to do what I
>need to do.
>
Use a CURSOR to enumerate the SYSINDEXES system table in your database
to find all the indexes on it. Alternatively, if you tied this up with
the INFORMATION_SCHEMA.TABLES you can list the indexes on a table by
table basis.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
|||hi Matt,
YYZ wrote:
> I have a need to write many scripts to alter a LOT the underlying
> database structure of our database in the field. We have many fields
> out there with a type of float, and I've been told to change those to
> numeric(19,5) -- easy enough. Unless there is a constraint, in which
> case I have to drop the constraint, alter the field, add the
> constraint back in. Easy enough again, once you know what you are
> doing.
> Now, they tell me to change all the nvarchar(XX) fields to
> varchar(XX) -- easy enough again, unless they have a default -- use
> the same scheme as above, and it all works. UNLESS they are part of
> a primary key. Uh oh -- now I hit something I don't know how to
> solve...
> What I'm thinking is that I should dump all of the indexes and
> primary keys and defaults out of all tables, and then just rebuild
> them all from scratch. However, this database was "created" by using
> the Access upsizing wizard, so I don't know all the primary key
> names, constraint names, etc.
> Can anyone point me in the right direction to dump all indexes and
> defaults on every column in a database? I can re-create them pretty
> easily...
> Any advice would be appreciated, or even an alternate method to do
> what I need to do.
you can perhaps search www.sqlservercentral.com... there's plenty of
maintenance scripts...
ie: http://www.sqlservercentral.com/scri...utions/935.asp to drop
and recreate all indexes on a db..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Alter Table

Hello I have another problem.
I write this line of code:
alter table film modify casting personaggi varchar2(500)
but when I execute the result is
Error: ORA-01735: invalid ALTER TABLE option
what's the problem??
Thank you ElisaHello,

can I see the table structure ?

Best regards
Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com|||the structure of the table is:

CREATE TABLE Film
(IdFilm number(10) PRIMARY KEY,
Titolo VARCHAR2(20) NOT NULL,
Regista VARCHAR2(20) NOT NULL,
Casting VARCHAR2(500) NOT NULL,
Nazione VARCHAR2(20) NOT NULL,
Durata NUMBER(3) NOT NULL,
Genere VARCHAR2(10) NOT NULL,
Trama VARCHAR2(500) NOT NULL,
Novit VARCHAR2(10) NOT NULL,
Locandina VARCHAR2(20) NOT NULL,
Note VARCHAR2(100) NOT NULL,
anno number(4))
Thank you, Elisa|||If you want to rename a column and you are on Oracle 9i use:

ALTER TABLE film RENAME COLUMN casting TO personaggi;

On previous versions of Oracle you have to drop old and create new column.

Hope it helps,
Jacek

Originally posted by trilly
the structure of the table is:

CREATE TABLE Film
(IdFilm number(10) PRIMARY KEY,
Titolo VARCHAR2(20) NOT NULL,
Regista VARCHAR2(20) NOT NULL,
Casting VARCHAR2(500) NOT NULL,
Nazione VARCHAR2(20) NOT NULL,
Durata NUMBER(3) NOT NULL,
Genere VARCHAR2(10) NOT NULL,
Trama VARCHAR2(500) NOT NULL,
Novit VARCHAR2(10) NOT NULL,
Locandina VARCHAR2(20) NOT NULL,
Note VARCHAR2(100) NOT NULL,
anno number(4))
Thank you, Elisa

Allocate by assets

I need to write a stored procedure in which I allocate based on the assets
of an account.
I'd like to pass my procedure 2 variables: @.AccountType and @.Amount. @.Amount
is the amount to be allocated. @.AccountType determines among which accounts
the amount will be allocated. The amount allocated to each account will be
determined by the "assets" of the account.
I need to account for rounding. The amount allocated should always be to 2
decimal places. If there is an unallocated "remainder" it should be
allocated among the accounts at random. It's critically important that the
amount to be allocated matches the amount allocated.
See DDL below:
If I was allocating 10.00 to all Accounts of AccountType 'A' and the assets
of AccountID 1 is 90.00 and AccountID 2 had assets of 10.00, Account 1 would
be allocated 9.00 and account 2 would get 1.00.
Logically, add the assets of all AccountTypes 'A' (100.00) and determine
each accounts percentage of the total. Account 1 has 90% of total and
Account 2 has 10% of total, then allocate based on these percentages.
Sample data and expected results for AccountType 'B'; Amount to be
allocated: 100.00 AccountID, AccountType,Assets,Expected Result
3,'B',33.35,17.33
4,'B',85.01,44.19
5,'B',74.02,38.48
Thanks to anyone who might help.
DROP TABLE Accounts
CREATE TABLE [dbo].[Accounts] (
[AccountID] [int] NOT NULL ,
[AccountType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountValue] [decimal](18, 2) NULL,
[Assets] [decimal](18, 2) NULL,
) ON [PRIMARY]
GO
INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
(1,'A',NULL,90)
INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
(2,'A',NULL,10)
INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
(3,'B',NULL,33.35)
INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
(4,'B',NULL,85.01)
INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
(5,'B',NULL,74.02)here's one way, that i've used in the past:
create procedure AllocateValue
@.AccountType char(1),
@.Amount decimal(18,2)
as
set nocount on
-- Allocate the dollar amount based on asset pct
update Accounts
set AccountValue = @.Amount * (Assets/AccountTotal.AccountTypeTotal)
from Accounts
join (
select AccountType, sum(Assets) as AccountTypeTotal
from Accounts
where AccountType = @.AccountType
group by AccountType
) AccountTotal
on AccountTotal.AccountType = Accounts.AccountType
where Accounts.AccountType = @.AccountType
-- Adjust for rounding
-- adjust highest asset, highest account ID [if max asset matches]
update Accounts
set AccountValue =
AccountValue +
(@.Amount -
(select sum(AccountValue) from Accounts
where AccountType = @.AccountType))
where AccountType = @.AccountType
and AccountID = (
select Max(AccountID)
from Accounts
where AccountType = @.AccountType
and Assets = (select max(Assets)
from Accounts
where AccountType = @.AccountType
)
)
Terri wrote:
> I need to write a stored procedure in which I allocate based on the assets
> of an account.
> I'd like to pass my procedure 2 variables: @.AccountType and @.Amount. @.Amou
nt
> is the amount to be allocated. @.AccountType determines among which account
s
> the amount will be allocated. The amount allocated to each account will be
> determined by the "assets" of the account.
> I need to account for rounding. The amount allocated should always be to
2
> decimal places. If there is an unallocated "remainder" it should be
> allocated among the accounts at random. It's critically important that the
> amount to be allocated matches the amount allocated.
> See DDL below:
> If I was allocating 10.00 to all Accounts of AccountType 'A' and the asset
s
> of AccountID 1 is 90.00 and AccountID 2 had assets of 10.00, Account 1 wou
ld
> be allocated 9.00 and account 2 would get 1.00.
>
> Logically, add the assets of all AccountTypes 'A' (100.00) and determine
> each accounts percentage of the total. Account 1 has 90% of total and
> Account 2 has 10% of total, then allocate based on these percentages.
>
> Sample data and expected results for AccountType 'B'; Amount to be
> allocated: 100.00 AccountID, AccountType,Assets,Expected Result
>
> 3,'B',33.35,17.33
> 4,'B',85.01,44.19
> 5,'B',74.02,38.48
>
> Thanks to anyone who might help.
>
> DROP TABLE Accounts
>
> CREATE TABLE [dbo].[Accounts] (
> [AccountID] [int] NOT NULL ,
> [AccountType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AccountValue] [decimal](18, 2) NULL,
> [Assets] [decimal](18, 2) NULL,
> ) ON [PRIMARY]
> GO
>
> INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
> (1,'A',NULL,90)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
> (2,'A',NULL,10)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
> (3,'B',NULL,33.35)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
> (4,'B',NULL,85.01)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
> (5,'B',NULL,74.02)
>
>|||Trey Walpole (treypole@.newsgroups.nospam) writes:
> -- Adjust for rounding
> -- adjust highest asset, highest account ID [if max asset matches]
> update Accounts
> set AccountValue =
> AccountValue +
> (@.Amount -
> (select sum(AccountValue) from Accounts
> where AccountType = @.AccountType))
> where AccountType = @.AccountType
> and AccountID = (
> select Max(AccountID)
> from Accounts
> where AccountType = @.AccountType
> and Assets = (select max(Assets)
> from Accounts
> where AccountType = @.AccountType
> )
> )
Since Terri said that the rounding should be allocated to an account chosen
at random, here is a variation that does this:
update Accounts
set AccountValue =
AccountValue +
(@.Amount -
(select sum(AccountValue) from Accounts
where AccountType = @.AccountType))
where AccountType = @.AccountType
and AccountID = (
select TOP 1 AccountID
from Accounts
where AccountType = @.AccountType
ORDER BY newid()
)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||ah yes - missed that random bit
Erland Sommarskog wrote:
> Trey Walpole (treypole@.newsgroups.nospam) writes:
>
>
> Since Terri said that the rounding should be allocated to an account chose
n
> at random, here is a variation that does this:
> update Accounts
> set AccountValue =
> AccountValue +
> (@.Amount -
> (select sum(AccountValue) from Accounts
> where AccountType = @.AccountType))
> where AccountType = @.AccountType
> and AccountID = (
> select TOP 1 AccountID
> from Accounts
> where AccountType = @.AccountType
> ORDER BY newid()
> )
>

All possible combination in a where condition

Dear all,

In the dataset of a report in the Reporting Services 2000, I need to write an SQL statment with a Where condition which makes all possible combination of 10 conditions.

Could that be done by any way except by ORing and ANDing all the conditions?

For example, If I have three conditions, A, B, and C, I need all possible combinations in a WHERE condition as follows

SELECT *
FROM table
WHERE A = @.A
OR B = @.B
OR C = @.C
OR A = @.A and B = @.B
OR A = @.A and C = @.C
OR B = @.B and C = @.C
OR A = @.A and B = @.B and C = @.C

Note: @.A, @.B, @.C are Report parameters

I need to do the same with 10 conditions, I think it's too much to do it that way.
My question is, is there any other way I can do that with the parameters of a Report in Reporting Services.

Any help is greatly appreciated.

Thank you.SELECT * FROM table t1 CROSS JOIN table t2

?|||How you write it your where clause will be true if any of A = @.A, B = @.B or C = @.C evaluates to true.
SELECT *
FROM table
WHERE A = @.A OR B = @.B OR C = @.C
Will be true for all combinations like (A + @.A and B = @.B) because first statement say that if only 1 is ok it will be true.

|||If it is a stored procedure, pass in -1 or '-1' for All values.
IF @.A = -1 SET @.A = null
IF @.B...
IF @.C...
SELECT *
FROM table
WHERE COALESCE(@.A,A) = A
AND COALESCE(@.B,B) = B
AND COALESCE(@.C,C) = C
COALESCE substitutes null values with the value specified as the second parameter. So A will always = A when @.A = null.
Maybe this will help?

All from one table and all from another

I'm hoping someone could help me write the SQL code to solve this problem

I have two tables and a master one if I need it. All tables can be linked with the Master_ID. Table1 and Table2 can each have 0, one or many records for each Master_ID. The other column in the two tables is a number representing a volumn of two different fluids.

Table1
Master_id
Volume1_amount

Table2
Master_id
Volume2_amount

Master
Master_id
Master_name

How can I return all of the rows in Table1 and all of the rows in Table2 for each Master_id such that it looks like this if Table1 has 2 records and Table2 has 1 record for a given Master_id and then Table2 has 2 records and Table1 has 0 for a differnt Master_id

Master_id Volume1_amount Volume2_amount
100235 25.3 m 62.1 m
100235 22.0 m null
220000 null 85.66 m
220000 null 59.0 m

Any help would very much be appreciated.What are the primary keys of Table1 and Table2? What is it that links the 62.1m Table2 value to the 25.3m Table1 value rather than to the 22.0m value?|||The tables are actually temporary tables so there is/are no primary key(s) define but the Master_id is what links them all together. The master_id in Table1 will match the Master_id in Table2 which both match to Master_id in the master table|||Yes, but my other question was:

What is it that links the 62.1m Table2 value to the 25.3m Table1 value rather than to the 22.0m value?

You haven't answered that.|||Oh sorry - nothing except for which ever is first in the table. The two volumes don't relate to each other at all except that they both relate to the master_id. Make sense?|||OK, well the concept of "first in the table" is meaningless in a relational database without something to order by. What DBMS are you using? For Oracle I know a trick you can use. Otherwise, I would suggest you need to add an extra column to Table1 and Table2:

Table1
Master_id
Volume1_amount
Seq_no

Table2
Master_id
Volume2_amount
Seq_no

where Seq_no is 1 for the 1st record for each Master_id, 2 for the second etc.

Then your query becomes:

select coalesce(t1.master_id,t2.master_id), t1.volume1_amount, t2.volume2_amount
from t1
full outer join t2
on
(t1.master_id = t2.master_id
and t1.seq_no = t2.seq_no
);|||Excellent! Thank you very much