Conditional SQL?

[jchastai]

I have a standard SELECT SQL statement in which one of the items in the table is a field called Owner. The owner field can either be an id number that points off to another table called users -OR- it coule be a persons name, email address.

Now what I need to return in a single query is the Owner's Name in one field and the Owner's Email address in a second.

Is there a way to do a conditional
if Owner is numeric
pull owner name from users AS OwnerName
pull owner email from users AS OwnerEmail
else
first item in Onwer AS OwnerName
second item in Owner AS OwnerEmail
end

?????

Any help would be appreciated - thanks!
-- Jeff

 

[tlbroadbent]

Try something like this.

Select
OwnerName=
Case When isnumeric(wner)
Then u.Name
Else left(wner, charindex(',', Owner)-1)
End,
OwnerEmail=
Case When isnumeric(wner)
Then u.Email
Else right(wner, len(wner)-charindex(',', wner))
End
From Owners o Left Join Users u
Oner wner=u.OwnerID Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[jchastai]

Thanks for the help. What you said almost worked.

I had to add in an =1 at the end of the isNumeric statements, but I am also getting an error anytime the query comes across a string in the owner field. It processes fine with the owner field is a number (foreign key to Users), but croaks with a string.

The error message is:
"Syntax error converting the varchar value 'John Doe,john_doe@somewhere.com' to a column of data type int."

Is this not what the isNumeric key is supposed to solve? -OR- is is occuring on the join between the two tables (the error message gives the line number as one - the select keyword)?

Again, thanks.
-- Jeff Chastain

 

[jchastai]

The problem was in the table joins and I played with the database in order to get the fields to be the same.

Thanks for the help.

 

[tlbroadbent]

Glad you got it. I see why the error occurred in the JOIN. Hope that didn't cause you to make a lot of changes. Also, I aplogize for leaving off the =1 from the isnumeric expression. I know better. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm