BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
WITH t AS (SELECT 1 id,
'A' grp,
1 account,
'I' TYPE
FROM DUAL
UNION ALL
SELECT 2,
'A',
1,
NULL
FROM DUAL
UNION ALL
SELECT 3,
'A',
2,
NULL
FROM DUAL
UNION ALL
SELECT 4,
'B',
1,
'I'
FROM DUAL
UNION ALL
SELECT 5,
'B',
1,
NULL
FROM DUAL)
IDGRP ACCOUNT TYPE
1 A 1 I
2 A 1
3 A 2
4 B 1 I
5 B 1
when the account changes per group ( like going from account 1 to account 2) I need an additional row with a type of R
I have the expected results if I use a union
SELECT ID,
grp,
account,
CASE
WHEN LAG (account) OVER (PARTITION BY grp ORDER BY id) != account
THEN
'R'
ELSE
TYPE
END
TYPE
FROM t
UNION
SELECT * FROM t;
ID,GRP,ACCOUNT,TYPE
1 A 1 I
2 A 1
3 A 2 R
3 A 2,
4 B 1 I
5 B 1
however I'd like to do it without the union
is there a solution using connect by or the model clause or somehting?
Edited by: pollywog on Oct 29, 2010 11:32 AM
Edited by: pollywog on Oct 29, 2010 11:38 AM
Edited by: pollywog on Oct 29, 2010 11:39 AM