Computed columns

I have a table with fields called fname (First Name) and lname (Last
Name). I need the users email thai is compose from lname and fname:
LOWER(LEFT (fname,1) + lname)

Is there any difference between creatig this computed column ia a table
or in a view in SQL Server 2000?

I can do:

1. CREATE TABLE Users(
fname varchar(20),
lname varchar(20),
email as LOWER(LEFT (fname,1) + lname) )

Or

2. CREATE TABLE Users (
fname varchar(20),
lname varchar(20))

CREATE VIEW Vw_users (fname, Lname ,
email)
AS
SELECT fname, Lname ,
LOWER(LEFT (fname,1) + lname) )

Is one of them is better?

Paulo

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Paulo Andre Ortega Ribeiro" <paulo.andre.66@.terra.com.br> wrote in message
news:3fa28c1c$0$201$75868355@.news.frii.net...
> I have a table with fields called fname (First Name) and lname (Last
> Name). I need the users email thai is compose from lname and fname:
> LOWER(LEFT (fname,1) + lname)
> Is there any difference between creatig this computed column ia a table
> or in a view in SQL Server 2000?
> I can do:
> 1. CREATE TABLE Users(
> fname varchar(20),
> lname varchar(20),
> email as LOWER(LEFT (fname,1) + lname) )
> Or
> 2. CREATE TABLE Users (
> fname varchar(20),
> lname varchar(20))
> CREATE VIEW Vw_users (fname, Lname ,
> email)
> AS
> SELECT fname, Lname ,
> LOWER(LEFT (fname,1) + lname) )
>
> Is one of them is better?
> Paulo
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Adding a computed column means the value is computed on demand (unless you
index that column) when you query the table. The view is rather like storing
a query definition in the database, so the value is 'calculated' only when
you query the view (unless you index the view). In that sense, both
approaches are very similar.

However, the view is a separate object, which means you can have separate
security permissions on it. A view will also be more portable to other
database systems, if that is a consideration for you.

In any case, neither solution is a good one, assuming that this isn't a
theoretical question. You will quickly have duplicate email addresses, even
with just a few names. If you have John Smith, and then James Smith is
added, their email addresses would be the same, so the best all-round
solution is to use a normal table column for the email address.

Simon

Complicated SQL Select help needed

Hi,

My users table contains a field called researchInterestId which looks like this: 1, 5, 10

This is because users where allows to select multiple options when choosing their research interests.

I have another table which contains the names of those research interests, which looks like this:

researchInterestId researchInterestName

1 Biology

2 Cancer

My question is, when selecting my list of users, i wish to also display the names of their research interests. I know how to inner join but im not sure in this case as there are multiple values (1, 5, 10)

Hope that makes sense and that someone can point me in the right direction or let me know what this type of query is called?

Thanks

Sam

You can create a function as shown here:http://weblogs.sqlteam.com/dinakar/archive/2007/03/28/60150.aspx in the comments and use it to join with the researchinterests table to get the description.

|||

It worked perfectly, thanks so much!

Complex queries using WHERE and mix of OR and AND

How do you effectively mix OR and AND together? I have the query below for m
y
SEARCH page. I would like the users to have the option of selecting one fiel
d
to search with OR selecting pairs of fields together to search the database
with. The problem is, with the SQL statement below, OR works (select 1 field
to search with) but AND does not (if I use more than 1 field to search with,
the search returns all entries in the database.
Could someone pls point out to me what I'm doing wrong? I'd really
appreciate it.
SELECT vNXX, vLN, vMN, vDT, vYR, vAGNT, vORD, vSAVE
FROM salesdb
WHERE (vNXX LIKE 'varNXX' AND vLN LIKE 'varLINE') OR (vMN LIKE 'varMONTH'
AND vAGNT LIKE 'varAGENT'AND vYR LIKE 'varYEAR' AND vSAVE LIKE 'varSAVE') OR
(vMN LIKE 'varMONTH' AND vYR LIKE 'varYEAR' AND vSAVE LIKE 'varSAVE') OR (vM
N
LIKE 'varMONTH' AND vYR LIKE 'varYEAR') OR (vNXX LIKE 'varNXX') OR (vLN LIKE
'varLINE') OR (vMN LIKE 'varMONTH') OR (vDT LIKE 'varDATE') OR (vYR LIKE
'varYEAR') OR (vAGNT LIKE 'varAGENT') OR (vORD LIKE 'varORD') OR (vSAVE LIKE
'varSAVE')
ORDER BY vMN DESC, vDT DESC, vYR DESC> AND does not (if I use more than 1 field to search with,
> the search returns all entries in the database.
Could you post a working example of this so that we can understand what you
mean. There's no reason why you can't use as many ANDs and ORs as you need
in a WHERE clause. AND takes precedence over OR unless you use brackets to
alter the order of evaluation.
David Portas
SQL Server MVP
--|||Hi David,
First I formatted your SQL using www.sqlinform.com .
Then I have seen that some conditions are not logic, e.g. using
(
vNXX LIKE 'varNXX'
AND vLN LIKE 'varLINE'
)
together with
(
vNXX LIKE 'varNXX'
)
because this condition is true independent from the value of vLN. You
will need to code your SQL in a different way.
Regards
Guido
SELECT vNXX, vLN, vMN, vDT, vYR, vAGNT, vORD, vSAVE
FROM salesdb
WHERE
(
vNXX LIKE 'varNXX'
AND vLN LIKE 'varLINE'
)
OR
(
vMN LIKE 'varMONTH'
AND vAGNT LIKE 'varAGENT'
AND vYR LIKE 'varYEAR'
AND vSAVE LIKE 'varSAVE'
)
OR
(
vMN LIKE 'varMONTH'
AND vYR LIKE 'varYEAR'
AND vSAVE LIKE 'varSAVE'
)
OR
(
vMN LIKE 'varMONTH'
AND vYR LIKE 'varYEAR'
)
OR
(
vNXX LIKE 'varNXX'
)
OR
(
vLN LIKE 'varLINE'
)
OR
(
vMN LIKE 'varMONTH'
)
OR
(
vDT LIKE 'varDATE'
)
OR
(
vYR LIKE 'varYEAR'
)
OR
(
vAGNT LIKE 'varAGENT'
)
OR
(
vORD LIKE 'varORD'
)
OR
(
vSAVE LIKE 'varSAVE'
)
ORDER BY vMN DESC, vDT DESC, vYR DESC

Complex dimension question

I have two dimension; 1 is a 'Users' dimension which has information about users of our system, the other is a self-referencing dimension containing industry hierarchies.

The Users dimension is linked to the Industry dimension by Industry ID.

I tried adding the Industry hierarchy to the Users dimension but when I go to browse the hierarchy all I see is IDs of the DimUser table, so I think that I did something wrong.

My goal is to add the industry hierarchy to the User dimension.
Can I do this with my current architecture?

Here are some examples of the two dimension:

DimUsers
- ID
- Name
- IndustryID (FK -> DimIndustry.ID)

DimIndustry
- ID
- IndustryName
- ParentID (FK -> DimIndustry.ID)

Thanks in advance.

Preston,

You should be able to do this using the following steps:

1.) Edit the User dimension

2.) In the "Data Source View" pane of the dimension editor, and select "Show Tables..."

3.) Add the DimIndustry table to the data source view pane

4.) Drag the "IndustryID" from your DimUsers table as a new attribute.

5.) Change the "NameColumn" property to pull the "IndustryName" column from the DimIndustry table.

6.) Create your hierarchy using the attributes you want and process.

HTH,

- Steve

Compile store procedures

I'm having a excessive amount of lock timeout and I discover that every time
when users execute store procedures, these procedures are compile before
execution. Anyone has an idea about this behaviour ?
thank you for your help...Check this link:
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q243/5/86.ASP&NoWebContent=1
--
HTH,
SriSamp
Please reply to the whole group only!
"Tony" <news@.hotmail.com> wrote in message
news:%23l0Kxh8PDHA.2424@.tk2msftngp13.phx.gbl...
> I'm having a excessive amount of lock timeout and I discover that every
time
> when users execute store procedures, these procedures are compile before
> execution. Anyone has an idea about this behaviour ?
> thank you for your help...
>