Alias a column that has 3 values into 3 columns

I want to alias a column that has 3 values into 3 columns, one for each
value. As an example, let’s say Pet_type.CategoryID contains three possib
le
values: Cat, Dog and Hamster. The following SQL works with MySQL. What is
the correct SQL Server syntax for this? I looked in the Transact SQL Guide
but came up empty.
Thank you.
Alan Slutsky
SELECT DISTINCT c1.Name AS Dog, c2.Name AS Cat, c3.Name AS Hamster,
d.title, d.identifier
FROM Canonical c1
LEFT JOIN Entity e1 USING (CanonicalID)
LEFT JOIN Document d USING(DocumentID)
LEFT JOIN Entity e2 USING (DocumentID)
LEFT JOIN Canonical c2 USING (CanonicalID)
LEFT JOIN Canonical c3 USING (CanonicalID)
WHERE c1.CategoryID=”Dog” AND c2.CategoryID=”Cat” and c3.CategoryID=
”Hamster”"aslutsky" <aslutsky@.discussions.microsoft.com> wrote in message
news:BE0AEB86-B15F-4E95-A13B-FBD7FFD748B4@.microsoft.com...
>I want to alias a column that has 3 values into 3 columns, one for each
> value. As an example, let's say Pet_type.CategoryID contains three
> possible
> values: Cat, Dog and Hamster. The following SQL works with MySQL. What
> is
> the correct SQL Server syntax for this? I looked in the Transact SQL
> Guide
> but came up empty.
> Thank you.
> Alan Slutsky
> SELECT DISTINCT c1.Name AS Dog, c2.Name AS Cat, c3.Name AS Hamster,
> d.title, d.identifier
> FROM Canonical c1
> LEFT JOIN Entity e1 USING (CanonicalID)
> LEFT JOIN Document d USING(DocumentID)
> LEFT JOIN Entity e2 USING (DocumentID)
> LEFT JOIN Canonical c2 USING (CanonicalID)
> LEFT JOIN Canonical c3 USING (CanonicalID)
> WHERE c1.CategoryID="Dog" AND c2.CategoryID="Cat" and c3.CategoryID="Hamst
er"
>
Here's my best guess. Notice that I've removed the references to C2 and C3
from the WHERE clause, otherwise the outer join effectively becomes and
inner one - at least it does in standard SQL but I can't verify that for
MySQL.
SELECT DISTINCT c1.name AS dog, c2.name AS cat, c3.name AS hamster,
d.title, d.identifier
FROM Canonical c1
LEFT JOIN Entity e1
ON c1.canonicalid = e1.canonicalid
LEFT JOIN Document d
ON c1.canonicalid = d.canonicalid
LEFT JOIN Entity e2
ON c1.canonicalid = e2.canonicalid
LEFT JOIN Canonical c2
ON c1.canonicalid = c2.canonicalid
AND c2.CategoryID='Cat'
LEFT JOIN Canonical c3
ON c1.canonicalid = c3.canonicalid
AND c3.CategoryID='Hamster'
WHERE c1.CategoryID='Dog' ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> I want to alias a column that has 3 values into 3 columns, one for each v
alue. <<
That makes ABSOLUTELY NO SENSE in RDBMS. A column has a single value
by definition?
No such thing can exist! Do you really only have one PetType as
implied by the singlular name? What kidn of crap is catergory_id? An
attribute can be a category OF SOME KIND or an identifier of SOME
ENTITY, but it cannot be both. This is foundations, not rocket
science.
Alan, please get a book on data modeling and SQL! Oh, in MySQL current
version or a Standared SQL you might use " pet_type IN ('Cat', 'Dog',
'Hamster')|||David,
I had to modify the query because d.canonicalid does not exist and c can not
join d. When I joined table d to table e, I get rows returned, but the
Company "column" is the only one that is populated. Person and Product
contain only nulls.
Do you have any other suggestions, and thank you for the help.
Alan
"David Portas" wrote:

> "aslutsky" <aslutsky@.discussions.microsoft.com> wrote in message
> news:BE0AEB86-B15F-4E95-A13B-FBD7FFD748B4@.microsoft.com...
> Here's my best guess. Notice that I've removed the references to C2 and C3
> from the WHERE clause, otherwise the outer join effectively becomes and
> inner one - at least it does in standard SQL but I can't verify that for
> MySQL.
> SELECT DISTINCT c1.name AS dog, c2.name AS cat, c3.name AS hamster,
> d.title, d.identifier
> FROM Canonical c1
> LEFT JOIN Entity e1
> ON c1.canonicalid = e1.canonicalid
> LEFT JOIN Document d
> ON c1.canonicalid = d.canonicalid
> LEFT JOIN Entity e2
> ON c1.canonicalid = e2.canonicalid
> LEFT JOIN Canonical c2
> ON c1.canonicalid = c2.canonicalid
> AND c2.CategoryID='Cat'
> LEFT JOIN Canonical c3
> ON c1.canonicalid = c3.canonicalid
> AND c3.CategoryID='Hamster'
> WHERE c1.CategoryID='Dog' ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>

Algorithmic question

I have a table with a field that contains a currency quote as a float.
In another table I have a rule field as a varchar.
We have an application that uses both to gennerate a price.
I need to do the same thing but in SQL.
An example could be:
Price = 1.05
rule = '*100'
Another example:
price = 1.05
rule = '*0+95'
The rules can also include division and minus.
How do I get the value of the field with the rule applied in SQL
Any idears ?
Best regards
Mikaelwhen you select a query do you use a single rule for all the prices or a
different rule based on condition.
can you post the ddl please|||I've done something similar but on a very large scale for a client, they
have about 10 calculations each calculation has a couple of hundred
different variables with different formula's, the best approach I found was
dynamic SQL and parameter substition...
Point 1 though, you shouldn't use float because its an approximate datatype,
use decimal instead.
declare @.formula_sql nvarchar(1000)
declare @.rule nvarchar(50)
set @.rule = '* 0.95'
set @.formula_sql = 'set @.answer = @.price ' + @.rule
declare @.answer decimal( 28, 3 )
sp_executesql @.forumla_sql,
N'@.price decimal( 28, 3 ), @.answer decimal( 28, 3 )
output',
@.price, @.answer OUTPUT
print @.answer
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:817AE7F0-660E-4174-BAA3-6220184E6F60@.microsoft.com...
>I have a table with a field that contains a currency quote as a float.
> In another table I have a rule field as a varchar.
> We have an application that uses both to gennerate a price.
> I need to do the same thing but in SQL.
> An example could be:
> Price = 1.05
> rule = '*100'
> Another example:
> price = 1.05
> rule = '*0+95'
> The rules can also include division and minus.
> How do I get the value of the field with the rule applied in SQL
> Any idears ?
>
> --
> Best regards
> Mikael|||Mikael
First of all avoid using FLOAT datatype for currency quote you may get
incorrect result particular in calculation
http://www.aspfaq.com/show.asp?id=2477
For the problem , please visit at
http://www.sommarskog.se/dynamic_sql.html
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:817AE7F0-660E-4174-BAA3-6220184E6F60@.microsoft.com...
>I have a table with a field that contains a currency quote as a float.
> In another table I have a rule field as a varchar.
> We have an application that uses both to gennerate a price.
> I need to do the same thing but in SQL.
> An example could be:
> Price = 1.05
> rule = '*100'
> Another example:
> price = 1.05
> rule = '*0+95'
> The rules can also include division and minus.
> How do I get the value of the field with the rule applied in SQL
> Any idears ?
>
> --
> Best regards
> Mikael|||Tony solved my problem, but here is the DDL anyways:
CREATE TABLE #curr
(
[id] int not null identity(1,1) primary key,
[rule] varchar(100) not null
)
CREATE TABLE #Price
(
FK_CurrId int not null primary key,
[price] decimal(28,10) not null-- float
)
insert into #curr ([rule])
VALUES ('*0+102')
INSERT INTO #Price (FK_CurrId,[price])
VALUES (@.@.identity,100)
SELECT *
FROM #curr c
INNER JOiN #Price p ON c.id = p.FK_CurrId
Best regards
Mikael
"Omnibuzz" wrote:

> when you select a query do you use a single rule for all the prices or a
> different rule based on condition.
> can you post the ddl please|||>> I have a table with a field [sic] that contains a currency quote as a float [bad id
ea!]. In another table I have a rule field [sic] as a varchar. <<
Columns and fields are totally different concepts. Get a book on basic
RDBMS before you do any more programming. FLOAT is never used in
currency calculations. The EU publishes some rules that you have to
follow when computing with Euros. The US has the GAAP rules. Your
design will not pass an audit.
You are vague about these rules, but it looks like they are arithmetic
functions of the form (price * A + B) where A and B can be positive or
negative, A can be greater or less than zero, and B can be any number
within a range.
CREATE TABLE Rules
(rule_name CHAR(7) NOT NULL PRIMARY KEY,
multiplier DECIMAL (8,4) DEFAULT 1.0 NOT NULL,
adder DECIMAL (8,4) DEFAULT 0.0 NOT NULL);
SQL is a compiled language and you are trying to use it as if you were
writing BASIC on the fly. The kludge is to use dynamic SQL, to mimic
interpreted BASIC.
I saw a similiar solution which stored formulas in a string for dynamic
SQL. A missing decimal point converted the formula to integer math and
destroyed the data integrity. It is really hard to see that 2 and 2.0
are not the same when you have a table with hundreds of such strings.sql

Alert on data deletion

We have an employee table that contains bank details and are experiencing
problems with account numbers being erased and lost. In order to track down
why this is happening (either due to our application code or SQL
replication) we'd like to be able to prevent certain columns from being
deleted if they already contain some data.

Is it possible to setup a check constraint to prevent our ee_acct_no columns
from being set to NULL or blank strings if it contains an account number
(i.e a 9 digit number)? We have setup the column to allow NULL's as we don't
always know employees bank details until later, so we do need to put them on
our database without bank details initially.

Also, if possible, can someone suggest a stored procedure or trigger i could
create that would fire a user-defined error message that would email an
operator if a bank account number changed?

Many thanks

Dan Williams.On Thu, 10 Mar 2005 14:35:55 +0000 (UTC), Dan Williams wrote:

> We have an employee table that contains bank details and are experiencing
> problems with account numbers being erased and lost. In order to track down
> why this is happening (either due to our application code or SQL
> replication) we'd like to be able to prevent certain columns from being
> deleted if they already contain some data.
> Is it possible to setup a check constraint to prevent our ee_acct_no columns
> from being set to NULL or blank strings if it contains an account number
> (i.e a 9 digit number)? We have setup the column to allow NULL's as we don't
> always know employees bank details until later, so we do need to put them on
> our database without bank details initially.

If ee_acct_no starts out null and later becomes non-null, then a check
constraint can't do the trick. You need a trigger ... which answers the
next question

> Also, if possible, can someone suggest a stored procedure or trigger i could
> create that would fire a user-defined error message that would email an
> operator if a bank account number changed?

CREATE TRIGGER trig_ee_acct
ON ee_acct
FOR UPDATE
AS
IF UPDATE(ee_acct_no)
BEGIN
declare @.msg varchar(400)
RAISERROR ('The ee_acct_no column must never be changed', 16, 1)
ROLLBACK TRANSACTION
END
GO

As Books Online describes:

All ad hoc messages have a standard message ID of 14,000.

Therefore in enterprise manager, you can set an email alert on message ID
14,000, and operators will get an email.

> Many thanks
> Dan Williams.|||Ross Presser (rpresser@.imtek.com) writes:
> As Books Online describes:
> All ad hoc messages have a standard message ID of 14,000.
> Therefore in enterprise manager, you can set an email alert on message ID
> 14,000, and operators will get an email.

Is that SQL 7 Books Online? The number for ad hoc messages is 50000.

Dan could also use sp_addmessage to add a custom message, for instance
75321 for this error, and say:

RAISERROR(75321, 16, 1)

and the set up the alert on this code.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Dan Williams (dtwilliams@.hotmail.com) writes:
> We have an employee table that contains bank details and are
> experiencing problems with account numbers being erased and lost. In
> order to track down why this is happening (either due to our application
> code or SQL replication) we'd like to be able to prevent certain columns
> from being deleted if they already contain some data.
> Is it possible to setup a check constraint to prevent our ee_acct_no
> columns from being set to NULL or blank strings if it contains an
> account number (i.e a 9 digit number)? We have setup the column to allow
> NULL's as we don't always know employees bank details until later, so we
> do need to put them on our database without bank details initially.

That would have to be a trigger. Ross showed you the basics, but I like
to add some more details.

First IF UPDATE() a bit heavy-handed. IF UPDATE() only tells us that
the column mentioned in the SET clause, but not that the value was
actually changed.

So, you would have to compare inserted and deleted with each other
to compare these. However, joining inserted and deleted can have
costly performance effects. My standard routine is to save inserted
and deleted into table variables and then work with these.

Note that it's a good idea to keep IF UPDATE(), so that you don't
perform the check if the UPDATE is for a completely different column
only.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Thu, 10 Mar 2005 21:46:25 +0000 (UTC), Erland Sommarskog wrote:

> Ross Presser (rpresser@.imtek.com) writes:
>> As Books Online describes:
>>
>> All ad hoc messages have a standard message ID of 14,000.
>>
>> Therefore in enterprise manager, you can set an email alert on message ID
>> 14,000, and operators will get an email.
> Is that SQL 7 Books Online? The number for ad hoc messages is 50000.
> Dan could also use sp_addmessage to add a custom message, for instance
> 75321 for this error, and say:
> RAISERROR(75321, 16, 1)
> and the set up the alert on this code.

SQL 2000 BOL. And it's self-contradictory!

msg_id

Is a user-defined error message stored in the sysmessages table. Error
numbers for user-defined error messages should be greater than 50,000. Ad
hoc messages raise an error of 50,000.

msg_str

Is an ad hoc message with formatting similar to the PRINTF format style
used in C. The error message can have up to 400 characters. If the message
contains more than 400 characters, only the first 397 will be displayed and
an ellipsis will be added to indicate that the message has been cut. All ad
hoc messages have a standard message ID of 14,000.|||That's great. Thanks a lot.

I've managed to create a trigger that makes use of custom made error
messages that get emailed to me whenever an account number gets
changed. Here is my SQL code:-

CREATE TRIGGER trig_ee_acct ON employee
FOR UPDATE
AS

DECLARE @.eecode varchar(30)
SET @.eecode = (select ee_code FROM inserted)

IF UPDATE(ee_acct_no)
DECLARE @.oldAcctNo varchar(10)
DECLARE @.newAcctNo varchar(10)

SET @.oldAcctNo = (select ee_acct_no from deleted)
SET @.newAcctNo = (select ee_acct_no from inserted)

IF LEN(@.oldAcctNo) > 0 AND @.newAcctNo = ''
BEGIN
RAISERROR ('Bank Account Numbers cannot be deleted.', 16, 1)
ROLLBACK TRANSACTION
END

IF LEN(@.newAcctNo) > 0 AND LEN(@.newAcctNo) < 8
BEGIN
RAISERROR ('Bank Account Numbers must be 8 digits long.', 16,
1)
ROLLBACK TRANSACTION
END

IF LEN(@.newAcctNo) = 8 AND @.newAcctNo <> @.oldAcctNo
BEGIN
RAISERROR (50001, 10, 1, @.eecode, @.oldAcctNo, @.newAcctNo)
END

I believe my logic could probably be made more efficient, but initially
this appears to be working. However, i am experiencing issues with my
custom alert not being triggered consistently when the account numbers
are being changed. Do the insert and delete trigger tables get purged
after the trigger fires?

After updating an employees account number, i do initially receive the
email alert (with details of the old and new numbers), but if i update
it immediately after, it doesn't seem to fire the alert. However, if i
wait a couple of minutes, it appears to work ok.

That's why i'm thinking the inserted and deleted tables still contain
the previously saved information. Is there anyway i can purge the
information?

Thanks again

Dan|||dtwilliams@.hotmail.com (dan_williams@.newcross-nursing.com) writes:
> CREATE TRIGGER trig_ee_acct ON employee
> FOR UPDATE
> AS
> DECLARE @.eecode varchar(30)
> SET @.eecode = (select ee_code FROM inserted)
> IF UPDATE(ee_acct_no)
> DECLARE @.oldAcctNo varchar(10)
> DECLARE @.newAcctNo varchar(10)
> SET @.oldAcctNo = (select ee_acct_no from deleted)
> SET @.newAcctNo = (select ee_acct_no from inserted)

This is not a good trigger. A trigger fires once per statement, and
the deleted/inserted tables can contain many rows, and a good trigger
should handle this. So you will need to rewrite your trigger.

> That's why i'm thinking the inserted and deleted tables still contain
> the previously saved information. Is there anyway i can purge the
> information?

deleted/inserted are so-called virtual tables and are constructed from
the transaction log, and they cannot be accessed outside the scope of
the trigger.

By the way, if you need to make many accesses to the tables in your
trigger, it's a good idea to copy the interesting columns to table
variables and work with these instead. This can give quite some
performance improvements.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9616EB37A93C5Yazorman@.127.0.0.1...
> dtwilliams@.hotmail.com (dan_williams@.newcross-nursing.com) writes:
>> CREATE TRIGGER trig_ee_acct ON employee
>> FOR UPDATE
>> AS
>>
>> DECLARE @.eecode varchar(30)
>> SET @.eecode = (select ee_code FROM inserted)
>>
>> IF UPDATE(ee_acct_no)
>> DECLARE @.oldAcctNo varchar(10)
>> DECLARE @.newAcctNo varchar(10)
>>
>> SET @.oldAcctNo = (select ee_acct_no from deleted)
>> SET @.newAcctNo = (select ee_acct_no from inserted)
> This is not a good trigger. A trigger fires once per statement, and
> the deleted/inserted tables can contain many rows, and a good trigger
> should handle this. So you will need to rewrite your trigger.
>> That's why i'm thinking the inserted and deleted tables still contain
>> the previously saved information. Is there anyway i can purge the
>> information?
> deleted/inserted are so-called virtual tables and are constructed from
> the transaction log, and they cannot be accessed outside the scope of
> the trigger.
> By the way, if you need to make many accesses to the tables in your
> trigger, it's a good idea to copy the interesting columns to table
> variables and work with these instead. This can give quite some
> performance improvements.

OK, thanks for the advice. I've just discovered the wonders of triggers and
have previously been performing validation and integrity checks in my
application code, so i'm very much a beginner in writing SQL code.

Could you provide me with an example trigger that i can use or point me in
the direction of a good web site that i can learn from?

Many thanks

Dan|||Dan Williams (dtwilliams@.hotmail.com) writes:
> OK, thanks for the advice. I've just discovered the wonders of triggers
> and have previously been performing validation and integrity checks in
> my application code, so i'm very much a beginner in writing SQL code.
> Could you provide me with an example trigger that i can use or point me in
> the direction of a good web site that i can learn from?

Writing triggers is not fundamentaly different from writing stored
procedures, although a few things apply:

1) The "inserted" and "deleted" are visible in the trigger only, not
from stored procedures or dynamic SQL called from the trigger.
2) The tables are slow to access, so if the trigger has many references
to them, copying to a table variable is recommendable.
3) You are always in the context of the transaction defined by the statement
that fired the trigger. For this reason, one should engage in long-
running operations, as this can give contention problems.
4) Any error (save RAISERROR) terminates execution, aborts the batch and
rolls back the transaction.
5) Likewise, if the transaction count on exit differs from the trancount
when the trigger started execution, this also causes the entire batch
to be rolled back.

The trigger you posted could be rewritten to something like:

CREATE TRIGGER trig_ee_acct ON employee
FOR UPDATE
AS

DECLARE @.inserted TABLE (...)
DECLARE @.deleted TABLE (...)

INSERT @.inserted (...)
SELECT ... FROM inserted

INSERT @.deleted (...9
SELECT ... FROM deleted

IF UPDATE(ee_acct_no)
BEGIN
IF EXISTS (SELECT *
FROM @.inserted i
JOIN @.deleted d ON i.pk = d.pk
WHERE len(d.oldAcctNo) > 0
AND nullif(i.newAcctNo, '') IS NULL
BEGIN
RAISERROR ('Bank Account Numbers cannot be deleted.', 16, 1)
ROLLBACK TRANSACTION
END

IF EXISTS (SELECT *
FROM @.inserted i
WHERE len(i.oldAcctNo) <> 8)
BEGIN
RAISERROR ('Bank Account Numbers must be 8 digits long.', 16, 1)
ROLLBACK TRANSACTION
END

IF EXISTS (SELECT *
FROM @.inserted i
JOIN @.deleted d ON i.pk = d.pk
WHERE d.oldAcctNo <> i.newAcctNo
BEGIN
RAISERROR (50001, 10, 1, @.eecode, @.oldAcctNo, @.newAcctNo)
ROLLBACK TRANSACTION
END
END

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

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

Aggregation problem

Hi

We have a fact table contains 5 dimension keys and one measure.The measure is having value as 1for all records and it will always be 1.Total records in this fact table are 2316.

we processed cube successfully.

Problem : While viewing data in cube browser the measure count is aggregating.We want all the records for the measure is 1. But its showing aggregate values as 1, 2 6..

Thanks

karumuru

Try connecting from Excel or some other tools you have, to the cube.

Also, If you are using the default measure " Fact Count", it will aggregate, as it is a Count.

You can specify another measure and fill it with value 1 (this is just for testing, this is not a good practice), and specify the aggregateFunction property of the measure to DistinctCount.

(I hope you are aware of the Factless fact table concept)

Regards,

Jiju

|||

Jiju

yes, Our one measure is a factless fact and had value 1. This measure aggregation property we set to distinct count and count tried all. but its aggregating and we could not get 2316 rows

Thanks

Sridhar K

Aggregate Case Conditions

I have a table AAA which contains two columns fileid and typecd
for single fileid there can be either 1, 2 or 3 typecds home, host and
payroll
for e.g.
Fileid Typecd
100 Home
100 host
105 home
106 host
106 payroll
107 home
107 host
107 payroll
Now there is second table BBB which contains fileid(primary key) and
itemflag
Now i have to set itemflag for a fileid based on the above table.. like
if fileid contains only home then itemflag should be 1
only host then itemflag should be 2
only payroll then itemflag should be 3
home and host then itemflag should be 4
home and payroll then itemflag should be 5
host and payroll then itemflag should be 6
home host and payroll then itemflag should be 7
I tried this but this is not working giving error.
Insert into BBB
Select AAA.fileid,
(select
case sum(case AAA.typeCd when 'Home' then 1
when 'Host' then 2
when 'Payroll' then 4 end)
when 1 then 1
when 2 then 2
when 3 then 4
when 4 then 3
when 5 then 5
when 6 then 6
when 7 then 7 end
) as ItemFlg,
FROM AAA (nolock)
This is not working because white fetching first row from AAA, the
typecd can contain any of the three value,it is
not having all the three values at a single time.
Can any one help me with this.
Regards,
RajeevTry,
select a.fileid, sum(b.c1) + case when count(*) > 1 then 1 else 0 end as
ItemFlg
from t1 inner join (
select 'home' as Typecd, 1 as c1
union all
select 'host' as Typecd, 2 as c1
union all
select 'payroll' as Typecd, 3 as c1
) as t2
on a.Typecd = b.Typecd
group by a.fileid
go
AMB
"Rajeev" wrote:
> I have a table AAA which contains two columns fileid and typecd
> for single fileid there can be either 1, 2 or 3 typecds home, host and
> payroll
> for e.g.
> Fileid Typecd
> 100 Home
> 100 host
> 105 home
> 106 host
> 106 payroll
> 107 home
> 107 host
> 107 payroll
> Now there is second table BBB which contains fileid(primary key) and
> itemflag
> Now i have to set itemflag for a fileid based on the above table.. like
> if fileid contains only home then itemflag should be 1
> only host then itemflag should be 2
> only payroll then itemflag should be 3
> home and host then itemflag should be 4
> home and payroll then itemflag should be 5
> host and payroll then itemflag should be 6
> home host and payroll then itemflag should be 7
> I tried this but this is not working giving error.
> Insert into BBB
> Select AAA.fileid,
> (select
> case sum(case AAA.typeCd when 'Home' then 1
> when 'Host' then 2
> when 'Payroll' then 4 end)
> when 1 then 1
> when 2 then 2
> when 3 then 4
> when 4 then 3
> when 5 then 5
> when 6 then 6
> when 7 then 7 end
> ) as ItemFlg,
> FROM AAA (nolock)
> This is not working because white fetching first row from AAA, the
> typecd can contain any of the three value,it is
> not having all the three values at a single time.
> Can any one help me with this.
> Regards,
> Rajeev
>

Aggregate Case Conditions

I have a table AAA which contains two columns fileid and typecd
for single fileid there can be either 1, 2 or 3 typecds home, host and
payroll
for e.g.
Fileid Typecd
100 Home
100 host
105 home
106 host
106 payroll
107 home
107 host
107 payroll
Now there is second table BBB which contains fileid(primary key) and
itemflag
Now i have to set itemflag for a fileid based on the above table.. like
if fileid contains only home then itemflag should be 1
only host then itemflag should be 2
only payroll then itemflag should be 3
home and host then itemflag should be 4
home and payroll then itemflag should be 5
host and payroll then itemflag should be 6
home host and payroll then itemflag should be 7
I tried this but this is not working giving error.
Insert into BBB
Select AAA.fileid,
(select
case sum(case AAA.typeCd when 'Home' then 1
when 'Host' then 2
when 'Payroll' then 4 end)
when 1 then 1
when 2 then 2
when 3 then 4
when 4 then 3
when 5 then 5
when 6 then 6
when 7 then 7 end
) as ItemFlg,
FROM AAA (nolock)
This is not working because white fetching first row from AAA, the
typecd can contain any of the three value,it is
not having all the three values at a single time.
Can any one help me with this.
Regards,
RajeevTry,
select a.fileid, sum(b.c1) + case when count(*) > 1 then 1 else 0 end as
ItemFlg
from t1 inner join (
select 'home' as Typecd, 1 as c1
union all
select 'host' as Typecd, 2 as c1
union all
select 'payroll' as Typecd, 3 as c1
) as t2
on a.Typecd = b.Typecd
group by a.fileid
go
AMB
"Rajeev" wrote:

> I have a table AAA which contains two columns fileid and typecd
> for single fileid there can be either 1, 2 or 3 typecds home, host and
> payroll
> for e.g.
> Fileid Typecd
> 100 Home
> 100 host
> 105 home
> 106 host
> 106 payroll
> 107 home
> 107 host
> 107 payroll
> Now there is second table BBB which contains fileid(primary key) and
> itemflag
> Now i have to set itemflag for a fileid based on the above table.. like
> if fileid contains only home then itemflag should be 1
> only host then itemflag should be 2
> only payroll then itemflag should be 3
> home and host then itemflag should be 4
> home and payroll then itemflag should be 5
> host and payroll then itemflag should be 6
> home host and payroll then itemflag should be 7
> I tried this but this is not working giving error.
> Insert into BBB
> Select AAA.fileid,
> (select
> case sum(case AAA.typeCd when 'Home' then 1
> when 'Host' then 2
> when 'Payroll' then 4 end)
> when 1 then 1
> when 2 then 2
> when 3 then 4
> when 4 then 3
> when 5 then 5
> when 6 then 6
> when 7 then 7 end
> ) as ItemFlg,
> FROM AAA (nolock)
> This is not working because white fetching first row from AAA, the
> typecd can contain any of the three value,it is
> not having all the three values at a single time.
> Can any one help me with this.
> Regards,
> Rajeev
>|||Try,
select a.fileid, sum(b.c1) + case when count(*) > 1 then 1 else 0 end as
ItemFlg
from t1 inner join (
select 'home' as Typecd, 1 as c1
union all
select 'host' as Typecd, 2 as c1
union all
select 'payroll' as Typecd, 3 as c1
) as t2
on a.Typecd = b.Typecd
group by a.fileid
go
AMB
"Rajeev" wrote:

> I have a table AAA which contains two columns fileid and typecd
> for single fileid there can be either 1, 2 or 3 typecds home, host and
> payroll
> for e.g.
> Fileid Typecd
> 100 Home
> 100 host
> 105 home
> 106 host
> 106 payroll
> 107 home
> 107 host
> 107 payroll
> Now there is second table BBB which contains fileid(primary key) and
> itemflag
> Now i have to set itemflag for a fileid based on the above table.. like
> if fileid contains only home then itemflag should be 1
> only host then itemflag should be 2
> only payroll then itemflag should be 3
> home and host then itemflag should be 4
> home and payroll then itemflag should be 5
> host and payroll then itemflag should be 6
> home host and payroll then itemflag should be 7
> I tried this but this is not working giving error.
> Insert into BBB
> Select AAA.fileid,
> (select
> case sum(case AAA.typeCd when 'Home' then 1
> when 'Host' then 2
> when 'Payroll' then 4 end)
> when 1 then 1
> when 2 then 2
> when 3 then 4
> when 4 then 3
> when 5 then 5
> when 6 then 6
> when 7 then 7 end
> ) as ItemFlg,
> FROM AAA (nolock)
> This is not working because white fetching first row from AAA, the
> typecd can contain any of the three value,it is
> not having all the three values at a single time.
> Can any one help me with this.
> Regards,
> Rajeev
>

Aggregate Case Conditions

I have a table AAA which contains two columns fileid and typecd
for single fileid there can be either 1, 2 or 3 typecds home, host and
payroll
for e.g.
Fileid Typecd
100 Home
100 host
105 home
106 host
106 payroll
107 home
107 host
107 payroll
Now there is second table BBB which contains fileid(primary key) and
itemflag
Now i have to set itemflag for a fileid based on the above table.. like
if fileid contains only home then itemflag should be 1
only host then itemflag should be 2
only payroll then itemflag should be 3
home and host then itemflag should be 4
home and payroll then itemflag should be 5
host and payroll then itemflag should be 6
home host and payroll then itemflag should be 7
I tried this but this is not working giving error.
Insert into BBB
Select AAA.fileid,
(select
case sum(case AAA.typeCd when 'Home' then 1
when 'Host' then 2
when 'Payroll' then 4 end)
when 1 then 1
when 2 then 2
when 3 then 4
when 4 then 3
when 5 then 5
when 6 then 6
when 7 then 7 end
) as ItemFlg,
FROM AAA (nolock)
This is not working because white fetching first row from AAA, the
typecd can contain any of the three value,it is
not having all the three values at a single time.
Can any one help me with this.
Regards,
RajeevPlease post DDL, as you have been asked to do before.
CREATE TABLE AAA
(file_id INTEGER NOT NULL,
type_cd CHGAR(7) NOT NULL
CHECK (type_cd IN ('home', 'host', 'payroll')),
PRIMARY KEY (file_id, type_cd));
item_flag <<
This is a bad design. The "item_flag" is computed and should not be
presisted in a base table. Use a VIEW instead
CREATE VIEW BBB (file_id, item_flag)
AS
SELECT file_id,
CASE WHEN COUNT(*) = 3 THEN 7 -- all 3 type codes
WHEN MIN(type_cd) = 'home' AND MAX(type_cd) = 'home' THEN 1
WHEN MIN(type_cd) = 'host' AND MAX(type_cd) = 'host' THEN
2
WHEN MIN(type_cd) = 'payroll' AND MAX(type_cd) = 'payroll'
THEN 3
WHEN MIN(type_cd) = 'home' AND MAX(type_cd) = 'host' THEN 4
WHEN MIN(type_cd) = 'home' AND MAX(type_cd) = 'payroll'
THEN 5
WHEN MIN(type_cd) = 'host' AND MAX(type_cd) = 'payroll'
THEN 6
ELSE NULL END
) AS item_flag
FROM AAA
GROUP BY file_id;
The VIEW will always be current and does not require constant updating.|||Rajeev,
You forgot to do a GROUP BY for aggregration.
This should work for you.
CREATE TABLE AAA
( FileID int
, TypeCD varchar(10)
)
GO
INSERT INTO AAA VALUES(100,'Home')
INSERT INTO AAA VALUES(100,'Host')
INSERT INTO AAA VALUES(105,'Home')
INSERT INTO AAA VALUES(106,'Host')
INSERT INTO AAA VALUES(106,'Payroll')
INSERT INTO AAA VALUES(107,'Home')
INSERT INTO AAA VALUES(107,'Host')
INSERT INTO AAA VALUES(107,'Payroll')
CREATE TABLE BBB
( FileID int
, ItemFlag int
)
GO
INSERT INTO BBB
SELECT
AAA.FileID
, sum( CASE AAA.TypeCd
WHEN 'Home' THEN 1
WHEN 'Host' THEN 2
WHEN 'Payroll' THEN 4
END
)
FROM AAA (nolock)
GROUP BY AAA.FileID
SELECT *
FROM BBB
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Rajeev" <rajeev.rajput@.gmail.com> wrote in message news:1150388715.269695.235590@.h76g2000c
wa.googlegroups.com...
>I have a table AAA which contains two columns fileid and typecd
>
> for single fileid there can be either 1, 2 or 3 typecds home, host and
> payroll
> for e.g.
>
> Fileid Typecd
> 100 Home
> 100 host
>
> 105 home
>
> 106 host
> 106 payroll
>
> 107 home
> 107 host
>
> 107 payroll
>
> Now there is second table BBB which contains fileid(primary key) and
> itemflag
>
> Now i have to set itemflag for a fileid based on the above table.. like
> if fileid contains only home then itemflag should be 1
> only host then itemflag should be 2
> only payroll then itemflag should be 3
> home and host then itemflag should be 4
> home and payroll then itemflag should be 5
> host and payroll then itemflag should be 6
> home host and payroll then itemflag should be 7
>
> I tried this but this is not working giving error.
>
> Insert into BBB
> Select AAA.fileid,
> (select
> case sum(case AAA.typeCd when 'Home' then 1
> when 'Host' then 2
> when 'Payroll' then 4 end)
> when 1 then 1
> when 2 then 2
> when 3 then 4
> when 4 then 3
> when 5 then 5
> when 6 then 6
> when 7 then 7 end
> ) as ItemFlg,
> FROM AAA (nolock)
>
> This is not working because white fetching first row from AAA, the
> typecd can contain any of the three value,it is
> not having all the three values at a single time.
>
> Can any one help me with this.
>
> Regards,
> Rajeev
>|||Joe,
I agree, the resultset for 'BBB' would be more robust as a VIEW.
However, wouldn't the view be a 'bit' easier to maintain if it was a simple
aggregration rather than the detailed CASE statement you offered?
CREATE VIEW BBB
AS
SELECT
AAA.FileID
, sum( CASE AAA.TypeCd
WHEN 'Home' THEN 1
WHEN 'Host' THEN 2
WHEN 'Payroll' THEN 4
END
) AS 'ItemFlag'
FROM AAA (nolock)
GROUP BY AAA.FileID
And of course, it would be so-o-o-o much simpler if Rajeev were to be storin
g the enumerated values rather than 'Home', 'Post', etc. Another of his desi
gn mistakes -and I sincerely hope that the table isn't really named 'AAA'...
Regards,
-Arnie Rowland
--
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"--CELKO--" <jcelko212@.earthlink.net> wrote in message news:1150394057.944343.243490@.y41g20
00cwy.googlegroups.com...
> Please post DDL, as you have been asked to do before.
>
> CREATE TABLE AAA
> (file_id INTEGER NOT NULL,
> type_cd CHGAR(7) NOT NULL
> CHECK (type_cd IN ('home', 'host', 'payroll')),
> PRIMARY KEY (file_id, type_cd));
>
> item_flag <<
>
> This is a bad design. The "item_flag" is computed and should not be
> presisted in a base table. Use a VIEW instead
>
> CREATE VIEW BBB (file_id, item_flag)
> AS
> SELECT file_id,
> CASE WHEN COUNT(*) = 3 THEN 7 -- all 3 type codes
> WHEN MIN(type_cd) = 'home' AND MAX(type_cd) = 'home' THEN 1
> WHEN MIN(type_cd) = 'host' AND MAX(type_cd) = 'host' THEN
> 2
> WHEN MIN(type_cd) = 'payroll' AND MAX(type_cd) = 'payroll'
> THEN 3
> WHEN MIN(type_cd) = 'home' AND MAX(type_cd) = 'host' THEN 4
> WHEN MIN(type_cd) = 'home' AND MAX(type_cd) = 'payroll'
> THEN 5
> WHEN MIN(type_cd) = 'host' AND MAX(type_cd) = 'payroll'
> THEN 6
> ELSE NULL END
> ) AS item_flag
> FROM AAA
> GROUP BY file_id;
>
> The VIEW will always be current and does not require constant updating.
>|||Arnie Rowland wrote:
> Joe,
I like your answer better!
Yes! and me, too :)

After restore database, what happy with full-text catelogs?

Hi,
I have CRM database that contains full-text catalogs. Someone delete rows by
mistake. I need restore this database to same server from last night backup,
after database restore, do I need manually active full-text indexes or it
will automatically enable since I restore database at same server?
If I need do manually active full-text indexes, where and what kind steps I
need perform?
Regards,
Chen
You should run an incremental population.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:07AD58B8-128E-4726-8293-6E60198E5D3C@.microsoft.com...
> Hi,
> I have CRM database that contains full-text catalogs. Someone delete rows
> by
> mistake. I need restore this database to same server from last night
> backup,
> after database restore, do I need manually active full-text indexes or it
> will automatically enable since I restore database at same server?
> If I need do manually active full-text indexes, where and what kind steps
> I
> need perform?
> Regards,
> Chen
|||Chen,
I would recommend that you run a Full Population as the restored database,
may not be current with your existing database in addition to the rows that
were deleted.
Also, if your table does not have a timestamp column and you start an
Incremental Population, then in fact a Full Population is executed and
furthermore, an Incremental Population often takes as long to run as a Full
Population as it needs to determine if any rows are deleted and this causes
the Incremental Population to take as long as the Full Population, even if
no changes have occurred.
Finally, depending upon your SQL Server version ( SELECT @.@.version ) and if
you are using SQL Server 2000, you can use "Change Tracking" with "Update
Index in Background" and once your database is restored, you may never have
to run an Incremental Population again!
Thanks,
John
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:07AD58B8-128E-4726-8293-6E60198E5D3C@.microsoft.com...
> Hi,
> I have CRM database that contains full-text catalogs. Someone delete rows
by
> mistake. I need restore this database to same server from last night
backup,
> after database restore, do I need manually active full-text indexes or it
> will automatically enable since I restore database at same server?
> If I need do manually active full-text indexes, where and what kind steps
I
> need perform?
> Regards,
> Chen

Advice on table design which will allow me to enforce integrity

Hi,
I have two tables Table A and B, below with some dummy data...

Table A (contains specific unique settings that can be requested)

Id, SettingName
1, weight
2, length

Table B (contains the setting values, here 3 values relate to weight
and 1 to length)

Id, Brand, SettingValue
1, A, 100
1, B, 200
1, null, 300
2, null, 5.3

(There is also a list of Brands available in another table). No primary
keys / referential integrity has been setup yet.

Basically depending upon the Brand requested a different setting value
will be present. If a particular brand is not present (signified by a
null in the Brand column in table B), then a default value will be
used.
Therefore if I request the weight and pass through a Brand of A, I will
get 100
If I request the weight but do not pass through a brand (i.e. null) I
will get 300.

My question is, what kind of integrity can I apply to avoid the user
specifying duplicate Ids and Brands in table B. I cannot apply a
composite key on these two fields as a null is present. Table B will
probably contain about 50 rows and probably 10 of them will be brand
specific. The reason its done like this is in the calling client code I
want to call some function e.g.
getsetting(weight) ... result = 300
Or if it is brand specific
getsetting(weight,A) .... result = 100

Any advice on integrity or table restructuring would be greatly
appreciated. Its sql 2000 sp3.
Thanks
bradAs you have already realized, TableB isn't even in First Normal Form.
Attribute Value models like this are seldom viable and you haven't
stated any excuse not to use the standard, normalized approach:

CREATE TABLE TableB (id INTEGER NOT NULL, brand CHAR(1) DEFAULT 'Z' NOT
NULL REFERENCES Brands (brand), weight INTEGER NOT NULL, length INTEGER
NOT NULL, PRIMARY KEY (id,brand))

Either add the default ("Z" here) brand to the brands table, or if you
prefer not to do that, put the brand-specific info into a separate
table:

CREATE TABLE TableB (id INTEGER NOT NULL, default_weight INTEGER NOT
NULL, default_length INTEGER NOT NULL, PRIMARY KEY (id))

CREATE TABLE TableBBrands (id INTEGER NOT NULL REFERENCES TableB (id),
brand CHAR(1) NOT NULL REFERENCES Brands (brand), weight INTEGER NOT
NULL, length INTEGER NOT NULL, PRIMARY KEY (id,brand))

Personally, I would lean toward the former design. This meets all the
requirements that you have specified as far as I can see.

--
David Portas
SQL Server MVP
--|||i would go with two tables B1(id ,brand , value) with PK (id,brand) and
B2(id,value) PK (id)
but i think that more appropriate solution is to have separate table
for each "setting type" e.g.
WEIGHTS, LENGTHS, VOLUMES where you can have appropriate data type for
each setting

HTH, Strider|||> i think that more appropriate solution is to have separate table
> for each "setting type" e.g.
> WEIGHTS, LENGTHS, VOLUMES

Do you mean separate COLUMNS? Why would you create separate tables for
each attribute?

--
David Portas
SQL Server MVP
--|||I should have also said that Table A will grow in size over time (i.e.
60 different settings), so you can have many settings, i.e. a user
could add additional settings in here, therefore making these as
columns would not be ideal. If possible I would also prefer to not have
to have a dummy brand.

David Portas wrote:
> > i think that more appropriate solution is to have separate table
> > for each "setting type" e.g.
> > WEIGHTS, LENGTHS, VOLUMES
> Do you mean separate COLUMNS? Why would you create separate tables
for
> each attribute?
> --
> David Portas
> SQL Server MVP
> --|||>Why would you create separate tables for
>each attribute?

i don't know, perhaps transient brain disorder ;)|||Why can't you implement the necessary change control procedures to add
columns as the users require them? Allowing users the unfettered
ability to add new "attributes" to a system is a recipe for creating an
unusable mass of redundant and inconsistent data. Do you really expect
users to go through the process of identifying and eliminating
functional dependencies before they add new attributes? On the whole,
users aren't good database architects. At least if they were they would
work with proper tables and columns, constraints and keys - things
which aren't possible under your model.

Your question was how to enforce integrity but I don't see any
integrity in your design at all.

--
David Portas
SQL Server MVP
--|||Yup, I agree all valid points there David. Thanks for your input on
this one.
Brad

David Portas wrote:
> Why can't you implement the necessary change control procedures to
add
> columns as the users require them? Allowing users the unfettered
> ability to add new "attributes" to a system is a recipe for creating
an
> unusable mass of redundant and inconsistent data. Do you really
expect
> users to go through the process of identifying and eliminating
> functional dependencies before they add new attributes? On the whole,
> users aren't good database architects. At least if they were they
would
> work with proper tables and columns, constraints and keys - things
> which aren't possible under your model.
> Your question was how to enforce integrity but I don't see any
> integrity in your design at all.
> --
> David Portas
> SQL Server MVP
> --|||1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

2) Google EAV and attribute splitting" design flaws before you destroy
your database. It is virtually impossible to have any data integrity
with this "design" becuase of the need to write a complete RDBMS engine
in SQL constraints.

3) The thing falls apart in about one year of operation. That is based
on fixing these things as a consultant.

4) You need help from someone who knows even a little bit about data
modeling. You have not even gottent o the basic normal forms yet.|||[posted and mailed, please reply in news]

(obhayes@.hotmail.com) writes:
> My question is, what kind of integrity can I apply to avoid the user
> specifying duplicate Ids and Brands in table B. I cannot apply a
> composite key on these two fields as a null is present. Table B will
> probably contain about 50 rows and probably 10 of them will be brand
> specific. The reason its done like this is in the calling client code I
> want to call some function e.g.
> getsetting(weight) ... result = 300
> Or if it is brand specific
> getsetting(weight,A) .... result = 100

I would add a dummy primary key to TableB, possibly an IDENTITY column.
When I would add a UNIQUE constraint on (Id, Brand). A UNIQUE constraint
does permit for NULL values, but only one NULL.

(It's a bit amazing that a thread with so many replies did not include
this simple and straigtforward suggestion.)

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

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