Decode Help 1

[tmcneil]

I'm trying to do a decode on the LOCATION_ID field of one of our tables. The location_id is preceeded by a character that tells us what line that station is on. For example, BAIR, is Airport on the Blue Line. RALE is Alewife on the Red Line and so on. I am trying to write a decode function to read-in the location_id, determine what the first character is of that string and then return the line value. Ie. 'R' found in RALE would return 'Red Line'. So, what is the wild card character to pull out chars but you only care what the first one is. Well, I'm trying to do something like this where the # sign is the wildcard.

DECODE(a.LOCATION_ID, 'R#', 'Red Line', 'O#', 'Orange Line', 'B#', 'Blue Line', 'G#', 'Green Line', 'S#', 'Silver Line') AS LINE

Thanks,
Todd

 

[Turkbear]

Hi, Use the Substr function to isolate the first letter:

DECODE(substr(a.LOCATION_ID,1,1,), 'R', 'Red Line', 'O', 'Orange Line', 'B', 'Blue Line', 'G', 'Green Line', 'S', 'Silver Line','Bad Code') AS LINE

I added a default 'Bad Code' if, for some reason a LOCATION_ID does not start with one of those specified letters..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[tmcneil]

Turkbear,

Absolutely brilliant!

Thanks,
Todd

 

[Turkbear]

Hi,
Not so brilliant as to avoid typos..Should be:

DECODE(substr(a.LOCATION_ID,1,1), 'R', 'Red Line', 'O', 'Orange Line', 'B', 'Blue Line', 'G', 'Green Line', 'S', 'Silver Line','Bad Code') AS LINE

I had an extra comma in the substr function..

Thanks..Glad it helped..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[tmcneil]

Well, I didn't mind the typo. I took out the 'Bad Code' and removed 'Line' from each returned value and put the code into an oracle view.

Thanks again,
Todd