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

"Paolo Fossati" <fossati_apoloNOSPAM (AT) hotmail (DOT) com> a écrit dans le message de news: 4e4bd509$0$6840$5fc30a8 (AT) news (DOT) tiscali.it...
All are different or some are different?

Regards
Michel

Michel Cadot wrote:
select ref from (select distinct ref, object_count from location) group by ref having count(*) > 1

Il 17/08/2011 18:30, Gerard H. Pille ha scritto:
in this wan i can find all the ref that are in 2 locations with
different object_count?
i'll try tomorrow... thanks again
Paolo

Paolo Fossati schrieb am 17.08.2011 in
<4e4c29d5$0$6839$5fc30a8 (AT) news (DOT) tiscali.it>:

objects.
Should be the answer of your question, isn't it?

Andreas

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

On Aug 17, 7:49*am, Paolo Fossati <fossati_apoloNOS... (AT) hotmail (DOT) com>
wrote:
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

Il 18/08/2011 01:07, Andreas Mosmann ha scritto:

Thanks a lot
Paolo

Il 18/08/2011 01:27, ddf ha scritto:
thanks a lot for this demo, i'll try it and i come back to you if i
can't understand something

Regards
Paolo

ddf schrieb am 18.08.2011 in
<d6e122f6-d480-4ecc-a6d1-0cff4d33e974 (AT) bl1g2000vbb (DOT) googlegroups.com>:

...

but in general it is great. It makes many things easier and faster.

Do you know what kind of license you need to use these analytic
functions?
Is the enterprise license enough or is there more to be licensed?
From which Oracle version these functions exist?

Thanks in advance
Andreas

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

In article <1313671608.78 (AT) user (DOT) newsoffice.de>, mosmann@expires-31-08-
2011.news-group.org says...


<of analytics>

All editions if I am not very much mistaken.

--
jeremy
K1200S