reset password
Author Message
JesusLopez
Posts: 4
Posted 19:58 Apr 15, 2015 |

So i'm having a problem trying to answer this question 

6) Find the first name and last name of each member along with a calculated daytime phone number. The daytime phone number should be the work number if one is listed. If the work number is null, use the home phone number.  You will need to use CASE or IF. 

So far i ran this 

SELECT FirstName,LastName,WorkPhone, CASE WHEN WorkPhone > 1 THEN 'Day Phone' WHEN WorkPhone IS NULL THEN 'Home Phone' END AS 'Day Time Phone' FROM Members WHERE WorkPhone IS NOT NULL;

I get 5 names when i should be getting 23, how would i go to getting those remaining 18 based on the question? 

venny
Posts: 61
Posted 20:41 Apr 15, 2015 |

First thing first, you have a problem with the beginning part: "SELECT FirstName,LastName,WorkPhone"  This will cause WorkPhone to have column no matter what and it won't be calculated.  So it needs to be: 

"SELECT FirstName,LastName,...,"

In the last part, you have "FROM Members WHERE WorkPhone IS NOT NULL;"   Now you don't need to do the WorkPhone IS NOT NULL, because you will do this condition in the middle part where it will be calculated, also this is the reason why you're only getting 5 entries.  So the call will is this so far:

"SELECT FirstName,LastName,...FROM Members"

Now the trickier part is the condition now.  For the last column, the question is asking for if the workphone is listed, put the workphone, but if there is no workphone (AKA null) then put the homephone instead.

Here's an example from my old lecture that helped out 
SELECT TrackTitle, LengthSeconds/60,
CASE WHEN LengthSeconds/60 < 3
THEN 'Short Track'
ELSE 'Long Track'
END
FROM Tracks; 

So IF the length is more than 3 minutes, THEN list it as 'Short Track' ELSE list it as 'Long Track.'

Back to the original problem, so IF there is a workphone THEN put in the Workphone ELSE put in the HomePhone. 
This might help a bit:

CASE WHEN <boolean expression here> THEN <what you want displayed> ELSE <if the boolean case doesn't work, then put whatever is in here instead.>  END AS <title of the column>

The case is the most important part of the problem, so I don't want to just give the answer. 

Hint: if there is no entry for a column, it IS NULL else it IS NOT NULL..

 

Last edited by venny at 20:57 Apr 15, 2015.
JesusLopez
Posts: 4
Posted 18:01 Apr 16, 2015 |

Hey Venny, thank you so much I believe this is correct isn't it? (See Attachment)

Of course i will take the WorkPhone and HomePhone table out for the final result,

I won't show the Code so everyone can get a chance to do it for themselves, sorry guys. 

Attachments:
Last edited by JesusLopez at 18:02 Apr 16, 2015.
venny
Posts: 61
Posted 18:13 Apr 16, 2015 |

It looks correct to me.

dbravoru
Posts: 60
Posted 16:00 Apr 19, 2015 |

What does it mean by calculated phone number? What is a calculated phone number? lol.

venny
Posts: 61
Posted 18:01 Apr 19, 2015 |

I believe when it mean calculated here, it means it requires some logic to determine what output should be.  EX:   If and else statements. 

So in this example, it's asking what logic should be done to output the correct phone number.  For the each entry's day number, put in the work phone number.  If they don't have a work phone number, then put their house phone number instead.