Re: build a query

From: ddf <oratune_at_msn.com>
Date: Wed, 17 Aug 2011 16:27:30 -0700 (PDT)
Message-ID: <d6e122f6-d480-4ecc-a6d1-0cff4d33e974_at_bl1g2000vbb.googlegroups.com>

On Aug 17, 7:49 am, Paolo Fossati <fossati_apoloNOS..._at_hotmail.com> wrote:
> hi all,
>
> i'm trying (with no success ) to build an oracle query , db is oracle
> 10, os windows 2008 64 bit
>
> in a table called "location" i have :
>
> REF     location        object_count
>
> 1000    a               22
> 1000    b               22
> 1001    a               57
> 1001    b               55
> .
> .
> .
>
>   the same object (see the 1001 example ) is existing in 2 different
> locations (a and b) but the number on objects are not the same (in one
> place are 57, in the other 55)
> As this number must be the same in all the locations  for the same ref
> (see the 1000 example), how can i spot with query all the refs thar are
> not the same?
>
> thanks a lot fot your help
> Paolo

SQL> create table location (

  2          ref     number not null,
  3          location varchar2(4) not null,
  4          object_count number not null

  5 );

Table created.

SQL>
SQL> insert all
  2 into location
  3 values(1000, 'a', 22)
  4 into location
  5 values(1000, 'b', 22)
  6 into location
  7 values(1001, 'a', 57)
  8 into location
  9 values(1001, 'b', 56)
 10 into location
 11 values(1002, 'a', 27)
 12 into location
 13 values(1002, 'b', 29)
 14 into location
 15 values(1003, 'a', 22)
 16 into location
 17 values(1003, 'b', 22)
 18 into location
 19 values(1004, 'a', 42)
 20 into location
 21 values(1004, 'b', 62)
 22 into location
 23 values(1005, 'a', 82)
 24 into location
 25 values(1005, 'b', 82)
 26 into location
 27 values(1006, 'a', 23)
 28 into location
 29 values(1006, 'b', 22)
 30 select * From dual;

14 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select * From location;

       REF LOCA OBJECT_COUNT
---------- ---- ------------

      1000 a              22
      1000 b              22
      1001 a              57
      1001 b              56
      1002 a              27
      1002 b              29
      1003 a              22
      1003 b              22
      1004 a              42
      1004 b              62
      1005 a              82

       REF LOCA OBJECT_COUNT
---------- ---- ------------
      1005 b              82
      1006 a              23
      1006 b              22

14 rows selected.

SQL>
SQL> column prev_loc format a4
SQL>
SQL> select ref, prev_loc, location, prev_loc_obj_ct, object_count

  2 from
  3 (select ref,
  4 nvl(lag(ref) over (order by ref, location), ref) prev_ref,
  5 nvl(lag(location) over (order by ref, location), ref) prev_loc,

  6          location,
  7          object_count,
  8          nvl(lag(object_count) over (order by ref, location),

object_count) prev_loc_obj_ct
  9 from location)
 10 where nvl(prev_loc_obj_ct, object_count) <> object_count  11 and prev_ref = ref
 12 /

       REF PREV LOCA PREV_LOC_OBJ_CT OBJECT_COUNT ---------- ---- ---- --------------- ------------

      1001 a    b                 57           56
      1002 a    b                 27           29
      1004 a    b                 42           62
      1006 a    b                 23           22

SQL> David Fitzjarrell Received on Wed Aug 17 2011 - 18:27:30 CDT