Re: Stupid SQL question

In article <356DA711.5DF8B02D_at_decisionism.com>, "Marcy A. McKee" <marcy_at_decisionism.com> wrote:

>Hello,
>I have the following query:
>select city || '-NE' from blah where city = 'OMAHA' and state =
>'NEBRASKA'
>that works the way I want it to. My problem is that I want to include an
>additional statement like
>select city || '-IA' from blah where city = 'OMAHA' and state = 'IOWA'
>so that my end result is OMAHA-NE when the state is equal to Nebraska
>and OMAHA-IA when the state is equal to Iowa. Any ideas on how to do
>this?
>
>Thanks,
>Marcy

Marcy

One way is to use the DECODE function in sQL. I.E. select city || DECODE(state,'NEBRASKA','-NE',IOWA','-IA',<default>)  from blah where city = 'OMAHA' and state = 'NEBRASKA'

With 50 states, this could get loooong and hard to understand, it may be simpler to create a state code lookup table that has 2 columns, one for the state name and the second for the two character code. A simple join could provide the short code. I.E
select city || '-' || state_code
 from blah, state_lookup where city = 'OMAHA' and state = 'NEBRASKA'    AND state = state_name    

HTH
   James
--

lorenzen_at_tiny.net             | Life is complex; it has
                              |   real and imaginary parts