July 10, 2009 at 10:05 am
My data looks a bit like this:
surname | firstname | extension_number | external_number
Smith | John |1234 | no_data_here
Jones |Mary | no_data_here | 07788332972
I want to end up with a query that results in something like this:
surname |firstname |dialing_from_inside | _dialing_from_outside
Smith |John | 1234 | 01865 21234
Jones |Mary | 9 07788332972 | 07788332972
There are lots of rules that I need to perform on both extension_number and external_number - e.g. if the extension_number starts with 3, then the prefix is 5, if the external_number starts with 5, strip that off and put a 9.
I can do a CASE to create one column, how do I do two, without returning all the records twice?
I've tried to do two queries by puttin a select in before the case, (to basically deal with the records with an extension_number, and then to deal with the ones without, but it won't let me.
Any ideas?
July 10, 2009 at 10:11 am
This should do what you need, though of course, the condition statements will probably be more complex 😛
Keep in mind that you can have nested CASE statements, and you can use subqueries as part of CASE statements as long as they return only one parameter.
SELECT
surname,
firstname,
(
CASE
WHEN extension_number = 'SOME CONDITION' THEN 'SHOW SOMETHING'
ELSE 'SOMETHING ELSE'
END
) AS dialing_from_inside,
(
CASE
WHEN external_number = 'SOME CONDITION' THEN 'SHOW SOMETHING'
ELSE 'SOMETHING ELSE'
END
) AS dialing_from_outside
FROM SOME_TABLE
July 10, 2009 at 10:24 am
I see someone has already shown a method, but I still want to post my code (as attachment to avoid frustration on my part).
Please pay close attention to the setup for my code. This is what you should do when posting questions asking for help. You can learn more about this by reading (and following) the guidelines in the first article I reference below in my signature block regarding asking for assistance. Please take the time to read it, it will help alot.
July 13, 2009 at 9:50 am
Thanks loads for your incredibly swift answers, I've used your help to get my query working and I'm getting exactly the results I wanted and more.
Thanks again for all your help.
July 20, 2009 at 9:49 pm
Was on a staycation for the week and didn't visit the forums often. Thanks for the feedback. Glad we could be of assistance.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply