convert a Boolean to either String or Text?

Hi,

Does any one know how to convert a Boolean to either String or Text?
I came across the ToText ( ) function but I can't seem to get it to work.
According to the Crystal Reports For Visual Studio .NET ( Wrox book )
the ToText ( ) the function should work to convert Booleans... but I have not IDEA HOW , since they don't provide any
example! Can any one please shed some light? or maybe provide a better solution?

thank you in advance.

C.What database are you talking about?|||Originally posted by Brett Kaiser
What database are you talking about?

SQL Server. Did I post this question on the wrong Forum?|||There is no ToText function in SQL server. That is a Crystal function. SQL Server does not even technically use "boolean" values. It uses the BIT value instead. It's possible that Crystal is misinterpreting the values it is receives from SQL server, because it is well-document that Crystal reports sucks big-time.

blindman|||Originally posted by blindman
There is no ToText function in SQL server. That is a Crystal function. SQL Server does not even technically use "boolean" values. It uses the BIT value instead. It's possible that Crystal is misinterpreting the values it is receives from SQL server, because it is well-document that Crystal reports sucks big-time.

blindman

I agree on the SUCK big time if we are talking about CR.NET. Version 8.5 seems to be pretty good to me. In any case, I guess I should have asked the question in terms of SQL Server since I want to create a VW and put it on the CR.NET as a DataSet. I have just realized that the way to convert a Boolean ( or Bit, thanks for the clarification) is as follows:

select tbl.fieldname = (case tbl_name when boolean_value then 'yourString' end) from tblName

Thank you,

P.|||The only technical equivalent to what I think you're asking is:

CASE
WHEN [MyColumn] = 0 THEN 'NO'
WHEN [MyColumn] = 1 THEN 'YES'
ELSE 'Null'
END CASE

That is assuming of course that the column is defined as:

MyColumn BIT NULL

The above CASE statement accepts that the field might be null. If that's not the case (the column allows no nulls), then you can just use two lines (omit the 'ELSE NULL').

Other than that, blindman is right. There isn't a data type called 'boolean' in SQL Server and ToText is definitely not a standard SQL function.

Good Luck,

hmscott|||Sorry for the duplicate post; you beat me by a couple of minutes. tHet's kuz i suk at tping.

hmscott|||Originally posted by hmscott
Sorry for the duplicate post; you beat me by a couple of minutes. tHet's kuz i suk at tping.

hmscott

Your query also worked. Thank you hmscott. I'll see you arround.

Contraint Or Trigger

I have been doing abit of research, and maybe I shall stumble across the answer yet. But until then I wish to present my trouble to you good people. :)

I have two tables, TAB_A, TAB_B.
When TAB_A.ID is deleted, then the Foreign_key on TAB_B is fired with CASCADE DELETE.
I have (or someone wants) two CASCADE DELETEs on TAB_B when TAB_A.ID is deleted. When I put this in SQL2000 I get the error: May cause cycles or multiple path.
Both Contraints do use different Fields, but reference the same TAB_A.ID

ex.
Alter table TAB_B TAB_A_ID1_FK FOREIGN KEY(ID1) REFERENCES TAB_A(ID) ON DELETE CASCADE

Alter table TAB_B TAB_A_ID2_FK FOREIGN KEY(ID2) REFERENCES TAB_A(ID) ON DELETE CASCADE
I can add either constraint alone and it is fine, when I add the remainder, I get the error. So, I was thinking maybe a Trigger would work? But then how do I know what ID was deleted from TAB_A?

Thanks for any help. I hope I was descriptive enough.

Davewhen your trigger is fired you will have two temp tables, inserted and deleted. The deleted table will hold all the deleted records.|||Thanks,
I have discoved the inserted and deleted tables that are created in memory when the triggers are fired.

Now I have new question I am sorting through. Based off of my earlier example:

Create Trigger TAB_B_TR
ON TAB_A
FOR DELETE
AS

if @.@.ROWCOUNT=0
return

DELETE FROM TAB_B WHERE TAB_B.ID1 = DELETED.ID

When I execute this, it says DELETED is not a recognized column name. Now, if I just type SELECT * FROM DELETED that will execute and run. I have a few theories that I am working on as to why this isn't working...barring syntax. :)

Hopefully this will be an easy post for someone and maybe save me ( the newbie) some time.

Thanks
David|||I have figured out what I needed. Simple really. I believe this will do what I want:

Create TRIGGER TABLEA_TABLEB_A_ID_TR
ON TABLE_A
FOR DELETE
AS

DELETE FROM TABLE_B WHERE TABLE_A_ID = (SELECT ID FROM DELETED)

(saying that TABLE_B has two columns 1) ID and 2) TABLE_A_ID AND TABLE_A has one Column ID )

I don't recall the earilier example I used,
so I am guessing abit on column names

Is there another way I should be doing this?
Else, I think this will work.

Thanks,
David Barnes
(trying to move outta the newbie zone)|||That will work just fine. One of the mistakes people often make with triggers is that they forget that a trigger must work for a single row as well as multiple rows.

containstable across columns

We're having some difficulty getting adequate search results. We're trying to search across 3 columns, "author", "title", and "subtitle" (no other columns are part of FT index). The problem relates to searching for an author/title phrase at the same tim
e. Say the user types "chemistry brown" into a search box, (the Author being "Brown" and the title "Chemistry"--but there's no way to make that distinction at run time). If you use CONTAINSTABLE, (...from containstable(<ourtable>,*,'"brown" near "chemis
try"') it only return rows where "brown" and "chemistry" are in the SAME column (where what we need is for it to return high rankings where "brown" and "chemistry" are in different columns but the same rows). Freetexttable is no better, since again it's
ranking based on the number of occurrences of a word in a SINGLE column, not in all the FT-indexed columns per row.
The key factor is that the type of word (author, title, subtitle) is not known at search time, which it difficult. Any suggesstions?
Platform: SQl 2K sp3, Windows Server 2000 Sp4
you could try this
select * from containstable(fulltext,*,'chemistry or brown')
or
select * from containstable(fulltext,*,'"chemistry" or "brown"')
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"John C" <John C@.discussions.microsoft.com> wrote in message
news:36E8719A-769E-45D1-A05B-FD8BC0B170E5@.microsoft.com...
> We're having some difficulty getting adequate search results. We're
trying to search across 3 columns, "author", "title", and "subtitle" (no
other columns are part of FT index). The problem relates to searching for
an author/title phrase at the same time. Say the user types "chemistry
brown" into a search box, (the Author being "Brown" and the title
"Chemistry"--but there's no way to make that distinction at run time). If
you use CONTAINSTABLE, (...from containstable(<ourtable>,*,'"brown" near
"chemistry"') it only return rows where "brown" and "chemistry" are in the
SAME column (where what we need is for it to return high rankings where
"brown" and "chemistry" are in different columns but the same rows).
Freetexttable is no better, since again it's ranking based on the number of
occurrences of a word in a SINGLE column, not in all the FT-indexed columns
per row.
> The key factor is that the type of word (author, title, subtitle) is not
known at search time, which it difficult. Any suggesstions?
> Platform: SQl 2K sp3, Windows Server 2000 Sp4
|||Hilary,
While the below containstable query examples provide a solution, it does not
meet John's requirement of 'search across 3 columns, "author", "title", and
"subtitle"' and I submit that using the following FT-enabled Northwind table
(Employees) and multiple column specific column names (vs. using "*" or
asterisk for all FT-enabled columns) does meet John's request, for example:
use Northwind
go
SELECT LastName, FirstName, Title, Notes from Employees
/* returns:
LastName FirstName Title
-- -- --
Davolio Nancy Sales Representative
Fuller Andrew Vice President, Sales
Leverling Janet Sales Representative
Peacock Margaret Sales Representative
Buchanan Steven Sales Manager
Suyama Michael Sales Representative
King Robert Sales Representative
Callahan Laura Inside Sales Coordinator
Dodsworth Anne Sales Representative
(9 row(s) affected)
*/
-- an expanded version of your example. Note, that this query will return
all 9 rows that contain Fuller (LastName column) OR Sales (Title column)
SELECT e.LastName, e.FirstName, e.Title
from Employees AS e,
containstable(Employees,*,'Fuller or Sales') as A
where
A.[KEY] = e.EmployeeID
--or this example that will also return all 9 rows that contain Fuller
(LastName column) OR Sales (Title column)
SELECT e.LastName, e.FirstName, e.Title
from Employees AS e,
containstable(Employees,*,'"Fuller" or "Sales"') as A
where
A.[KEY] = e.EmployeeID
However, John wants to 'search across 3 columns, "author", "title", and
"subtitle"' and the user types "chemistry brown" into a search box, (the
Author being "Brown" and the title "Chemistry"). Note the request is for an
AND'ing between the FT-enabled columns and not an OR between the columns.
This can be satsified via using multiple CONTAINSTABLE clauses and AND'ing
the join between Containstable predicates, for example
SELECT e.LastName, e.FirstName, e.Title
from Employees AS e,
containstable(Employees, LastName, 'Fuller') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID AND
B.[KEY] = e.EmployeeID
go
/* -- returns:
LastName FirstName Title
-- -- --
Fuller Andrew Vice President, Sales
*/
John, you can also use the workarounds that are documented for this issue in
SQL Server 7.0 KB article "286787 (Q286787) FIX: Incorrect Results From
Full-Text Search on Several Columns" at
http://support.microsoft.com/default...b;en-us;286787 as well as
SQL Server 7.0 KB article "294809 (Q294809) FIX: Full-Text Search Queries
with CONTAINS Clause Search Across Columns" at
http://support.microsoft.com/default...;en-us;294809. You should
note that while these SQL Server 7.0 specific KB articles are "fixes" for
SQL Server 7.0, this is the default behavior for SQL Server 2000 and SQL
Server 7.0 was "fixed" to comply with the default behavior of SQL Sever
2000. The workarounds in the KB articles will also work in SQL Server 2000
as well as the above solution that I provided above.
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:#Zt$PpiZEHA.2216@.TK2MSFTNGP10.phx.gbl...
> you could try this
> select * from containstable(fulltext,*,'chemistry or brown')
> or
> select * from containstable(fulltext,*,'"chemistry" or "brown"')
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "John C" <John C@.discussions.microsoft.com> wrote in message
> news:36E8719A-769E-45D1-A05B-FD8BC0B170E5@.microsoft.com...
> trying to search across 3 columns, "author", "title", and "subtitle" (no
> other columns are part of FT index). The problem relates to searching for
> an author/title phrase at the same time. Say the user types "chemistry
> brown" into a search box, (the Author being "Brown" and the title
> "Chemistry"--but there's no way to make that distinction at run time). If
> you use CONTAINSTABLE, (...from containstable(<ourtable>,*,'"brown" near
> "chemistry"') it only return rows where "brown" and "chemistry" are in the
> SAME column (where what we need is for it to return high rankings where
> "brown" and "chemistry" are in different columns but the same rows).
> Freetexttable is no better, since again it's ranking based on the number
of
> occurrences of a word in a SINGLE column, not in all the FT-indexed
columns
> per row.
> known at search time, which it difficult. Any suggesstions?
>
|||Thank you John. However, we've tried using multiple containstable and the problem is that you get, as you noted, an ANDing of your results. Returning to our previous example (user types "brown chemistry" in search box, with "brown" the implicit author a
nd "chemistry" the implicit title), we'd like to have chemistry books written by Brown as the highest ranked results, but also return other chemistry books (not by Brown) and books authored by Brown (but w/o "Chemistry") in the title. The JOINing of 2 co
ntainstable queries rules that out. When reading your reply I first thought that using a LEFT join would solve that problem, but that assumes you know which search term(s) are the most significant (i.e., which side to make the right, and which to make th
e left).
John
"John Kane" wrote:

> Hilary,
> While the below containstable query examples provide a solution, it does not
> meet John's requirement of 'search across 3 columns, "author", "title", and
> "subtitle"' and I submit that using the following FT-enabled Northwind table
> (Employees) and multiple column specific column names (vs. using "*" or
> asterisk for all FT-enabled columns) does meet John's request, for example:
> use Northwind
> go
> SELECT LastName, FirstName, Title, Notes from Employees
> /* returns:
> LastName FirstName Title
> -- -- --
> Davolio Nancy Sales Representative
> Fuller Andrew Vice President, Sales
> Leverling Janet Sales Representative
> Peacock Margaret Sales Representative
> Buchanan Steven Sales Manager
> Suyama Michael Sales Representative
> King Robert Sales Representative
> Callahan Laura Inside Sales Coordinator
> Dodsworth Anne Sales Representative
> (9 row(s) affected)
> */
> -- an expanded version of your example. Note, that this query will return
> all 9 rows that contain Fuller (LastName column) OR Sales (Title column)
> SELECT e.LastName, e.FirstName, e.Title
> from Employees AS e,
> containstable(Employees,*,'Fuller or Sales') as A
> where
> A.[KEY] = e.EmployeeID
> --or this example that will also return all 9 rows that contain Fuller
> (LastName column) OR Sales (Title column)
> SELECT e.LastName, e.FirstName, e.Title
> from Employees AS e,
> containstable(Employees,*,'"Fuller" or "Sales"') as A
> where
> A.[KEY] = e.EmployeeID
> However, John wants to 'search across 3 columns, "author", "title", and
> "subtitle"' and the user types "chemistry brown" into a search box, (the
> Author being "Brown" and the title "Chemistry"). Note the request is for an
> AND'ing between the FT-enabled columns and not an OR between the columns.
> This can be satsified via using multiple CONTAINSTABLE clauses and AND'ing
> the join between Containstable predicates, for example
> SELECT e.LastName, e.FirstName, e.Title
> from Employees AS e,
> containstable(Employees, LastName, 'Fuller') as A,
> containstable(Employees, Title, 'Sales') as B
> where
> A.[KEY] = e.EmployeeID AND
> B.[KEY] = e.EmployeeID
> go
> /* -- returns:
> LastName FirstName Title
> -- -- --
> Fuller Andrew Vice President, Sales
> */
> John, you can also use the workarounds that are documented for this issue in
> SQL Server 7.0 KB article "286787 (Q286787) FIX: Incorrect Results From
> Full-Text Search on Several Columns" at
> http://support.microsoft.com/default...b;en-us;286787 as well as
> SQL Server 7.0 KB article "294809 (Q294809) FIX: Full-Text Search Queries
> with CONTAINS Clause Search Across Columns" at
> http://support.microsoft.com/default...;en-us;294809. You should
> note that while these SQL Server 7.0 specific KB articles are "fixes" for
> SQL Server 7.0, this is the default behavior for SQL Server 2000 and SQL
> Server 7.0 was "fixed" to comply with the default behavior of SQL Sever
> 2000. The workarounds in the KB articles will also work in SQL Server 2000
> as well as the above solution that I provided above.
> Regards,
> John
>
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:#Zt$PpiZEHA.2216@.TK2MSFTNGP10.phx.gbl...
> of
> columns
>
>
|||You're welcome, John
You can also "OR" the multiple containstable clauses, for example:
-- Note, the OR and the addition of distinct e.LastName and searching on
'Inside' from the Title column.
SELECT distinct e.LastName, e.FirstName, e.Title
from Employees AS e,
containstable(Employees, LastName, 'Fuller') as A,
containstable(Employees, Title, 'Inside') as B
where
A.[KEY] = e.EmployeeID OR
B.[KEY] = e.EmployeeID
/* -- returns:
LastName FirstName Title
-- -- --
Callahan Laura Inside Sales Coordinator
Fuller Andrew Vice President, Sales
*/
However, I believe that by " highest ranked results", you are really looking
for cross-column FT Search results and for what is also know as "best bets"
or forcing you're artificial or "editorial best selection" and that you
would like this to be keyword and column specific. While SharePoint Portal
Server has such as concept as best bets, SQL Server FTS does not directly
support this without having to pay a scalability penalty. However, I've
recently worked out a single column keyword specific solution to this, for
details see my replies under the fulltext newsgroup subject thread
"Full-text SharePoint". I just recently worked out the details to a scalable
single table, single column keyword-specific KeywordRank "booster" that can
also work with the ExtendedRank value in the example provided in the
"Full-text SharePoint" thread. Please, review this thread and post back here
any questions you might have about the procedures. What would be required of
you or your users, is a keyword or phrase which can be linked to each of row
in your FT-enable table, this can also be linked back to a query log table
that your searchers are using that will record the keywords that they are
using and what pages they have clicked on. Let me know if you want to pursue
this further.
Thanks,
John
"John C" <John C@.discussions.microsoft.com> wrote in message
news:1BDCCD8F-EA03-463E-BA6C-8D62BB0AE0E8@.microsoft.com...
> Thank you John. However, we've tried using multiple containstable and the
problem is that you get, as you noted, an ANDing of your results. Returning
to our previous example (user types "brown chemistry" in search box, with
"brown" the implicit author and "chemistry" the implicit title), we'd like
to have chemistry books written by Brown as the highest ranked results, but
also return other chemistry books (not by Brown) and books authored by Brown
(but w/o "Chemistry") in the title. The JOINing of 2 containstable queries
rules that out. When reading your reply I first thought that using a LEFT
join would solve that problem, but that assumes you know which search
term(s) are the most significant (i.e., which side to make the right, and
which to make the left).[vbcol=seagreen]
> John
> "John Kane" wrote:
not[vbcol=seagreen]
and[vbcol=seagreen]
table[vbcol=seagreen]
example:[vbcol=seagreen]
return[vbcol=seagreen]
an[vbcol=seagreen]
columns.[vbcol=seagreen]
AND'ing[vbcol=seagreen]
issue in[vbcol=seagreen]
as[vbcol=seagreen]
Queries[vbcol=seagreen]
should[vbcol=seagreen]
for[vbcol=seagreen]
2000[vbcol=seagreen]
(no[vbcol=seagreen]
for[vbcol=seagreen]
"chemistry[vbcol=seagreen]
If[vbcol=seagreen]
near[vbcol=seagreen]
the[vbcol=seagreen]
where[vbcol=seagreen]
number[vbcol=seagreen]
not[vbcol=seagreen]

CONTAINS with AND across multiple Columns

How come when I am doing a CONTAINS search across multiple columns on
a table that I have full text indexed I don't get any matches when one
word is contained in one column and the other word is contained in the
other column in the same row of data? Here is a query where first
name is in one column and last name is in another column. Is the only
option to physically store this information concatenated together so
my search will behave as expected?
SELECT *
FROM dbo.Person
WHERE CONTAINS ((FIRST_NAME,LAST_NAME),'"BARRY*" AND "SMITH*"')
This is by design. In SQL 2000 a freetext search could look across columns.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Spencer" <spencer@.tabbert.net> wrote in message
news:227246c4-bc9e-40b8-a64b-1dd2c2ae8df6@.g21g2000hsh.googlegroups.com...
> How come when I am doing a CONTAINS search across multiple columns on
> a table that I have full text indexed I don't get any matches when one
> word is contained in one column and the other word is contained in the
> other column in the same row of data? Here is a query where first
> name is in one column and last name is in another column. Is the only
> option to physically store this information concatenated together so
> my search will behave as expected?
> SELECT *
> FROM dbo.Person
> WHERE CONTAINS ((FIRST_NAME,LAST_NAME),'"BARRY*" AND "SMITH*"')

constraints across normalized tables

Dear Experts,

When I use a single table I can easily use constraints to enforce my
business logic, but what do I do when I normalize a single table into
multiple tables.

For example, imagine that my initial table has the columns ID, Name,
Salary with the constraint that Salary is not NULL. Now imagine that
I break this into two tables, one with ID and Name and another with ID
and Salary. I would like to have a constraint that prevents the
creation of a row with (ID,Name) in the first table unless a
corresponding row in the second table is also created.

I can enforce this logic with triggers, but it looks ugly and is
fairly brittle. Is there a better way or is this the dark side of
normalization?

Thanks.Emin (emin.shopper@.gmail.com) writes:

Quote:

Originally Posted by

When I use a single table I can easily use constraints to enforce my
business logic, but what do I do when I normalize a single table into
multiple tables.
>
For example, imagine that my initial table has the columns ID, Name,
Salary with the constraint that Salary is not NULL. Now imagine that
I break this into two tables, one with ID and Name and another with ID
and Salary. I would like to have a constraint that prevents the
creation of a row with (ID,Name) in the first table unless a
corresponding row in the second table is also created.
>
I can enforce this logic with triggers, but it looks ugly and is
fairly brittle. Is there a better way or is this the dark side of
normalization?

I wouldn't call that particular example normalistion, but rather
vertical partitioning. :-)

If you have

CREATE TABLE leftside (id int NOT NULL PRIMARY KEY,
name varchar(34) NOT NULL)
CREATE TABLE rightside (id int NOT NULL PRIMARY KEY,
salary int NOT NULL)

You can use a foreign-key constraint to ensure that an id is not
inserted into leftside, if it's not also in rightside.

However, there is no way that you can ensure that there are rows in
both table, at least not with useful data. Not with triggers, not
with constraints, since there are not any commit-time versions of
the same.

The best you can do is to have a trigger to cascade inserts with dummy
values into the other table.

More generally, cross-table checks you have to do with triggers,
with the exception of foriegn keys. (OK, you can use user-defined
functions that you call from you CHECK constraints, but that can
be very costly performancewise.)

--
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|||>When I use a single table I use constraints to enforce my business logic, but what do I do when I normalize a single table into multip can easilyle tables. <<

The same thing -- use constraints to enforce business logic.

Quote:

Originally Posted by

Quote:

Originally Posted by

>For example, imagine that my initial table has the columns ID, Name, Salary with the constraint that Salary is not NULL. <<

Your data element names are all wrong. There is no such magical
creature as a Universal ID; this is OO or file system record numbers.
Name of what? Employee, maybe? Salary_type? Salary_amt? In fact, we
have no ideas what the name of this table is!!

Quote:

Originally Posted by

Quote:

Originally Posted by

>>Now imagine that I break this into two tables, one with ID and Name and another with ID and Salary. <<

We fire you for not having taken a data modeling course and knowing
about attribute splitting. The rule is that all the attributes of an
entity stay in one and only one table. Can I assume that this is
Personnel (emp_id, emp_name, salary_amt) and that the three are
required attributes?

Quote:

Originally Posted by

Quote:

Originally Posted by

>I would like to have a constraint that prevents the creation of a row with (ID,Name) in the first table unless a corresponding row in the second table is also created. <<

You do not need that if you keep your **already normalized** table.

I am guessing that you are really trying to ask about DRI actions, but
don't have enough background to know the concepts of normalization and
how they work together.|||--CELKO-- (jcelko212@.earthlink.net) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>For example, imagine that my initial table has the columns ID, Name,

Salary with the constraint that Salary is not NULL. <<

Quote:

Originally Posted by

>
Your data element names are all wrong. There is no such magical
creature as a Universal ID; this is OO or file system record numbers.
Name of what? Employee, maybe? Salary_type? Salary_amt? In fact, we
have no ideas what the name of this table is!!

Joe, in your endeavour to spit on as many SQL users as posssible, can't
you just be a little bit discriminant? Emin's question was apparently of
a generic nature, and his table was just an exmaple. For a table that
is just to be used in an example, and nothing else I think "id" is a
perfect name. There is no reason to call it CheeseID, AppleID, or
WhateverID; that would only distract attention from what the example
is intended to discuss.

--
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|||>Emin's question was apparently of a generic nature, and his table was just an example. <<

Because good specs are important; because there is no such thing as a
"generic table"; because we need to know if the FDs would allow that
table to be split like he was trying to do.

Quote:

Originally Posted by

Quote:

Originally Posted by

> There is no reason to call it CheeseID, AppleID, or WhateverID; that would only distract attention from what the example is intended to discuss. <<

So, how do you find the FDs and make a rational decision about this
nameless table? If he had given us real-world names, then we could
guess. What we had was (id -name; id -salary) or (id -name;
name -salary) or ((name,salary)-id) or etc.

If we keep giving these newbies kludges instead of showing them that
their questions are not properly formed, then they are going think
what they are doing is just fine and never learn RDBMS.

Quote:

Originally Posted by

>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||Emin wrote:

Quote:

Originally Posted by

>
Dear Experts,
>
When I use a single table I can easily use constraints to enforce my
business logic, but what do I do when I normalize a single table into
multiple tables.
>
For example, imagine that my initial table has the columns ID, Name,
Salary with the constraint that Salary is not NULL. Now imagine that
I break this into two tables, one with ID and Name and another with ID
and Salary. I would like to have a constraint that prevents the
creation of a row with (ID,Name) in the first table unless a
corresponding row in the second table is also created.
>
I can enforce this logic with triggers, but it looks ugly and is
fairly brittle. Is there a better way or is this the dark side of
normalization?
>
Thanks.

Your example doesn't make sense. Following the normalization rules, you
would not split this initial table, because there is no reason to split
it. Why do you want to split this table?

But even if you had an example: it is usually not necessary to go beyond
the 3rd normal form when modelling for an RDBMS. It just add a lot of
overhead and complexity to your queries.

Gert-Jan|||--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>Emin's question was apparently of a generic nature, and his table was just an example. <<

>
Because good specs are important; because there is no such thing as a
"generic table"; because we need to know if the FDs would allow that
table to be split like he was trying to do.
>

Quote:

Originally Posted by

Quote:

Originally Posted by

>> There is no reason to call it CheeseID, AppleID, or WhateverID; that would only distract attention from what the example is intended to discuss. <<

>
So, how do you find the FDs and make a rational decision about this
nameless table? If he had given us real-world names, then we could
guess. What we had was (id -name; id -salary) or (id -name;
name -salary) or ((name,salary)-id) or etc.
>
If we keep giving these newbies kludges instead of showing them that
their questions are not properly formed, then they are going think
what they are doing is just fine and never learn RDBMS.

You need a good cop and a bad cop. Except in Alpha Complex, where
the positions have been combined due to budgetary restraints. :)

Constraint question

I have 'Table1' with two fields:
- RecID
- POID
I want to allow records to be entered in Table1 where the same RecID and
POID can exist across multiple records in Table1, but I don't want to allow
a POID to exist in Table1 twice with different RecIDs.
This is OK
RecID | POID
1 | 100
1 | 100
This is not OK
RecID | POID
1 | 100
2 | 100
What's the best way to set this up?
Thanks,
CB1. add another table with constraints PK(RecID, POID ) and
UNIQUE(POID) or vice versa
2. add a FK to table1 referencing that another table (RecID, POID )
P.S. there could be a problem with your database design. you might want
to post your DDL|||On Mon, 3 Oct 2005 13:28:22 -0400, Chris Burgess wrote:
(snip)
>This is OK
>RecID | POID
>1 | 100
>1 | 100
Hi Chris,
No it's not. Unless you have more columns in the table, but omitted them
in this post.
But with this design, you've now got two exact duplicates. And SQL gives
you no way to address just one of them. Every change you wish to make to
one of these rows will be made to the other as well.
If the reality you are modeling has two entities with RecID 1 and POID 1
and you've got a way to distinguish them, then add an extra column to
store whatever distinguishes the two in your business. If you've got the
both of them, they are really indistiguishable, but you still need to
store the fact that there are two of them, add a columns to hold the
current number. That solves your constraint problem as well:
CREATE TABLE YourTable
(RecID int NOT NULL,
POID int NOT NULL,
NumberOfItems int NOT NULL DEFAULT 1,
PRIMARY KEY (POID),
CHECK (NumberOfItems >= 1)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.