Re: build a query

From: Paolo Fossati <fossati_apoloNOSPAM_at_hotmail.com>
Date: Thu, 18 Aug 2011 14:13:32 +0200
Message-ID: <4e4d01eb$0$6843$5fc30a8_at_news.tiscali.it>

Il 18/08/2011 01:27, ddf ha scritto:
> 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

hi David,
thanks a lot for this demo, i'll try it and i come back to you if i can't understand something

Regards
Paolo Received on Thu Aug 18 2011 - 07:13:32 CDT