how to select data in a table using case statements:

[salex]

Hi,

I don't know if I'm posting this question in the right forum.

I'm new to SQL, and I'm hoping to get some help in selecting a value based on a hierachy.

What I'm trying to achieve is get the campus phone number in the selected values, and if this is not available, then go down the hierarchy:
if campus phone is null then get business_phone
if business_phone is null then get permanent_phone

columns in the table are defined as follows:
emp_id, tele_code, tele_phone
1234 CA (campus) NULL
1234 PR(permanent) xxx-xxxx
1234 BU(business) xxx-xxxx

I'm thinking I have to use a case statement, but if there are any suggestion, I would appreciate the input.

Thank you in advance for all the help.
salex

[r035198x]

Hi,

I don't know if I'm posting this question in the right forum.

I'm new to SQL, and I'm hoping to get some help in selecting a value based on a hierachy.

What I'm trying to achieve is get the campus phone number in the selected values, and if this is not available, then go down the hierarchy:
if campus phone is null then get business_phone
if business_phone is null then get permanent_phone

columns in the table are defined as follows:
emp_id, tele_code, tele_phone
1234 CA (campus) NULL
1234 PR(permanent) xxx-xxxx
1234 BU(business) xxx-xxxx

I'm thinking I have to use a case statement, but if there are any suggestion, I would appreciate the input.

Thank you in advance for all the help.
salex

no this is not the correct forum.
no do not use case.
Use if else statements

if campus phone is not null get it
else if business_phone is not null get it
else if permanent_phone is not null get it

[salex]

Can you use an if statement inside a sql select statement?
Is it decode() function?

Thank you,
salex

[ronverdonk]

This is a programming question and does not, strictly speaking, belong in this forum.
Anyway: in MySQL's case: DECODE() is a MySql extension to standard SQL.

It would be better to use your code to determine what telephone no to use. Since we cannot see what programming language and what type of database you are using, I would advise you to post your code here, so we can have a look.

Example: if you use PHP/MySQL something like the following could be used:
[PHP]$res=mysql_query("your_select_statement");
while ($row = mysql_fetch_assoc($res)) {
if ($row['business_phone'] == NULL) $phone = $row['business_phone']);
if ($row['campus_phone'] == NULL) $phone = $row['campus_phone']);
if ($row['permanent_phone'] == NULL) $phone = $row['permanent_phone']);
// other assignments
}[/PHP]

Ronald :cool:

[ronverdonk]

That previous reply was a BIG BLOOPER!!
[php]
$res=mysql_query("your_select_statement");
while ($row = mysql_fetch_assoc($res)) {
if ($row['campus_phone'] != NULL) $phone = $row['campus_phone']);
else
if ($row['business_phone'] != NULL) $phone = $row['business_phone']);
else
if ($row['permanent_phone'] != NULL) $phone = $row['permanent_phone']
else $phone = 'no phone';
// other assignments
}
[/php]

Ronald :cool:

[salex]

Thank you guys for your help, and I apologize for the wrong forum!