Re: HELP!! SQL query

This query makes MANY critical presumptions of your data quality, for example, assuming that there is always exactly 1 row of 'Y' and exactly 1 row or 'N' for each outlet.

Overall this query/view is not a sound foundation for an application because it does not appear that the table is designed properly either. Hopefully the query will give you some ideas, nonetheless.

CREATE VIEW AS (
SELECT mt1.outlet, mt1.name, mt1.units1 av1, mt2.units1 cp1   FROM my_table mt1, my_table mt2

 WHERE mt1.outlet = mt2.outlet
   AND mt1.competitorcode = 'Y'
   AND mt2.competitorcode = 'N' )

--
Michael O'Neill
mjoneill_at_email.com



"Dan White" <dwhite_at_icimail.com> wrote in message
news:90m05b0tmu_at_drn.newsguy.com...


> 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

>