Re: This one for an SQL guru!!!

In article <8fv33u$4b9$1_at_nnrp1.deja.com>,   pol15_at_my-deja.com wrote:
> Thomas,
>
> Thank you very much for your help, it is greatly appreciated.
>
> I've studied your query, and am I right in saying that this will not
> work if the Product has at least one "correct" price change that is
 NOT
> the LAST price change.
>

No, the query I generated returns a set of products such that

o there exists at least one row in the table where there is not a row in the table with a EFF_FROM that is one more then the EFF_TO. (thats the where NOT exists)

o and this row is not the "last" entry for that product. (thats the and eff_to < ( select max(....)

Basically, if I had to write the psuedo procedural code for this query it would be:

   loop X over every product

      look for a row with the same product code = x.product_code and
         eff_from = x.eff_to+1

      if we CANNOT FIND such a row
          then, check to see if this is the "last" entry for that
          product.  The "last" entry will not have a row with
          eff_from = x.eff_to+1 since it is the last row!

          if this is NOT the last entry - KEEP this product code
      end if;

   query up all of the records for any product we KEPT in the above    loop

If you believe the query to be in error, it should be very easy to synthesize a dataset with 3 or 4 rows that shows it getting the "wrong" result and what you believe the correct result for that data would be.

Then we can "fix" the query given the new constraints.

> It works for your data because the "correct" price change is
> the "latest" one for the Product, and you exclude this row with the
> line ...
>
> "and eff_to < ( select max(eff_to)"
>
> I may be missing something, if so please correct me.
>
> Thanks again,
>
> Pol.
>
> In article <8fut5j$t8i$1_at_nnrp1.deja.com>,
> Thomas J. Kyte <tkyte_at_us.oracle.com> wrote:
> > In article <8fufgi$cot$1_at_nnrp1.deja.com>,
> > pol15_at_my-deja.com wrote:
> > > I have a Product Prices table as follows .....
> > >
> > > PRODUCT_CODE NUMBER
> > > PRICE NUMBER
> > > EFFECTIVE_FROM_DATE DATE
> > > EFFECTIVE_TO_DATE DATE
> > >
> > > I am trying to write a query that will return product info. where
 there
> > > is a gap between the "Effective To" date of one price change and
 the
> > > "Effective From" date of the next price change.
> > >
> > > For example ...
> > >
> > > Product Price Eff. From Eff. To
> > > 12345 12.99 15-JUL-99 29-AUG-99
> > > 12345 15.99 14-SEP-99 01-NOV-99
> > >
> > > In the above example, there is a period of time where the product
 does
> > > not have a price, ie. between 30-AUG-99 and 13-SEP-99 inclusive.
 This
> > > is a problem, and therefore it is rows like these I am interested
 in.
> > >
> > > Points to bear in mind .....
> > >
> > > 1. If a Product has the above problem, then I want ALL rows for
 the
> > > Product returned (not just the row that triggered the problem).
> > >
> > > 2. The number of rows (price changes) for each Product is variable
 and
> > > there is no maximum.
> > >
> > > 3. The results should be ordered by Product, Eff. From, Eff. To
> > >
> > > Good Luck to anyone attempting this one!!!!!
> > >
> > > Thanks in advance for any help.
> > >
> > > Pol.
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > >
> >
> > ops$tkyte_at_8i> create table p ( product_code int, price number,
 eff_from
> > date, eff_to date );
> > Table created.
> >
> > ops$tkyte_at_8i> alter session set nls_date_format = 'DD-MON-RR';
> >
> > ops$tkyte_at_8i> insert into p values ( 12345, 12.99 ,'15-JUL-
> > 99','29-AUG-99' );
> >
> > ops$tkyte_at_8i> insert into p values ( 12345, 15.99 ,'14-SEP-
> > 99','01-NOV-99' );
> >
> > ops$tkyte_at_8i> insert into p values ( 12345, 15.99 ,'02-NOV-
> > 99','01-JAN-00' );
> >
> > ops$tkyte_at_8i> insert into p values ( 11111, 15.99 ,'30-AUG-
> > 99','01-NOV-99' );
> >
> > ops$tkyte_at_8i> insert into p values ( 11111, 12.99 ,'02-NOV-
> > 99','29-AUG-00' );
> >
> > ops$tkyte_at_8i> select *
> > 2 from p
> > 3 where product_code in
> > 4 ( select product_code
> > 5 from p
> > 6 where not exists ( select null
> > 7 from p p2
> > 8 where p2.eff_from = p.eff_to+1
> > 9 and p2.product_code =
 p.product_code )
> > 10 and eff_to < ( select max(eff_to)
> > 11 from p p3
> > 12 where p3.product_code = p.product_code )
> > 13 )
> > 14 order by product_code, eff_from, eff_to
> > 15 /
> >
> > PRODUCT_CODE PRICE EFF_FROM EFF_TO
> > ------------ ---------- --------- ---------
> > 12345 12.99 15-JUL-99 29-AUG-99
> > 12345 15.99 14-SEP-99 01-NOV-99
> > 12345 15.99 02-NOV-99 01-JAN-00
> >
> > --
> > Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
> > Howtos and such: http://osi.oracle.com/~tkyte/index.html
> > Oracle Magazine: http://www.oracle.com/oramag
> > Opinions are mine and do not necessarily reflect those of Oracle
 Corp
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.