Re: Field has two values- want to display both in SQL

"Prince Of Thieves" <thief_NOSPAM_at_hotmail.com> wrote in news:Lplqb.205$373.3901_at_nnrp1.ozemail.com.au:

> My SQL is:
>
> select s.stylecode "Style", s.styleidx, p2.price "Price RRP", p2.spidx
> from sprice_style p1, sprice_clrsku p2, style s, refcode divn
> where p1.styleidx=s.styleidx
> and (p2.spidx = 70 or p2.spidx=2588)
> and divn.name='Ck Underwear';
>
> P2.SpIdx can be both of two values. I want to change the SQL above so
> that it shows something like:
>
> select s.stylecode "Style", s.styleidx, p2.price_if_=70 "Price RRP",
> p2.price_if_=2588 "Price WHL", p2.spidx
> from sprice_style p1, sprice_clrsku p2, style s, refcode divn
> where p1.styleidx=s.styleidx
> and (p2.spidx = 70 or p2.spidx=2588)
> and divn.name='Ck Underwear';
>
> Is this possible?

select

    s.stylecode "Style",
    s.styleidx,
    decode(p2.spidx,70,p2.price,null) "Price RRP",     decode(p2.spidx,2588,p2.price,null) "Price WHL",     p2.spidx
  from sprice_stype p1,
    sprice_clrsku p2,
    style s,
    refcode divn
  where p1.styleidx=s.styleidx
    and (p2.spidx = 70 or p2.spidx=2588)     and divn.name='Ck Underwear';

There are other problems with this query though. You're not joining sprice_clrsku to the other tables, so for styleidx you're goint to repeat the same values for price RRP and price WHL which may not be related in any way to that styleidx. You're also not using the refcode table anywhere so however many rows there are in refcode with name= 'Ck Underwear' the whole mess will repeat for each one.

-- 
Ken Denny
http://www.kendenny.com/