Re: HELP!! SQL query

From: Michael Bialik <michael_bialik_at_my-deja.com>
Date: Fri, 08 Dec 2000 20:45:42 GMT
Message-ID: <90rh9k$4kn$1@nnrp1.deja.com>

Hi.

 If I understood your problem correctly then try following:

  SELECT outlet, name,

    DECODE ( competitor_code, 'Y', units1, NULL ) av1,
    DECODE ( competitor_code, 'Y', units2, NULL ) av2,
    DECODE ( competitor_code, 'Y', units3, NULL ) av3,
    DECODE ( competitor_code, 'Y', units4, NULL ) av4,
    DECODE ( competitor_code, 'Y', units5, NULL ) av5,
     DECODE ( competitor_code, 'N', units1, NULL ) cp1,
     DECODE ( competitor_code, 'N', units2, NULL ) cp2,
     DECODE ( competitor_code, 'N', units3, NULL ) cp3,
     DECODE ( competitor_code, 'N', units4, NULL ) cp4,
     DECODE ( competitor_code, 'N', units5, NULL ) cp5

  FROM my_table;

 HTH. Michael

In article <90m05b0tmu_at_drn.newsguy.com>,   Dan White <dwhite_at_icimail.com> wrote:
> Hello I have a problem query. PL/SQL is not an option for this problem because I
> have to greate a view with this querry
>
> my table desc is as follows
>
> OUTLET VARCHAR2(8)
> NAME VARCHAR2(40)
> UNITS1 NUMBER(10,2)
> UNITS2 NUMBER(10,2)
> UNITS3 NUMBER(10,2)
> UNITS4 NUMBER(10,2)
> UNITS5 NUMBER(10,2)
> COMPETITORCODE CHAR(1)
>
> What I need to produce is
> outlet,name,units1 av1,units2 av2,units3 av3,units4 av4,units5 av5,
> units1 cp1,units2 cp2,units3 cp3, units4 cp4,units5 cp5
>
> I need to see units 1-5 where competitor code = 'Y'
> and units 1- 5 where competitorcode ='N' in the same row
> is there a way to do this???
>
> Thanks in advance
> Dan White
>
> Dan White
> programmer/analyst
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Dec 08 2000 - 14:45:42 CST