Re: Stupid SQL question

James Lorenzen wrote:
>
> 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'
>

It should be:
select city || DECODE(state,'NEBRASKA','-NE','IOWA','-IA',<default>) from blah where city = 'OMAHA' and state in ('NEBRASKA','IOWA')

> 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
>

That's the way it should be!

> HTH
> James
> --
> lorenzen_at_tiny.net | Life is complex; it has
> | real and imaginary parts

--

Stanley Fung                 | Software Analyst
Mobile Data Solutions Inc.   | Direct Line: (604)207-6290
Suite 135                    | Tel:         (604)270-9939
10551 Shellbridge Way,       | Fax:         (604)270-1310
Richmond, B.C.               | E-Mail:      sfung_at_mdsi.bc.ca
Canada V6X-2W9               | http://www.mdsi-advantex.com

------------------------------------------------------------