Hi Friends,
I facing issue in generating same unique id for odd and even records.
CREATE TABLE SAMPLE1
(
TXNAMT NUMBER(22,2),
TRANSDATETIME DATE,
ACTIVITYDATE DATE NOT NULL ENABLE,
DESCRIPTION VARCHAR2(128 BYTE) NOT NULL ENABLE,
DOCNBR NUMBER(22,0),
LASTDATEMAINT DATE NOT NULL ENABLE,
DESC_NUMBER NUMBER(22,0) NOT NULL ENABLE
);
Insert into SAMPLE1 (TXNAMT,TRANSDATETIME,ACTIVITYDATE,DESCRIPTION,DOCNBR,LASTDATEMAINT,DESC_NUMBER) values (40,to_timestamp('01302009 14:15:49','mmddyyyy hh24:mi:ss'),to_timestamp('01302009 00:00:00','mmddyyyy hh24:mi:ss'),'SAMPLETEST',2348053,to_timestamp('01302009 14:13:47','mmddyyyy hh24:mi:ss'),1234567);
Insert into SAMPLE1 (TXNAMT,TRANSDATETIME,ACTIVITYDATE,DESCRIPTION,DOCNBR,LASTDATEMAINT,DESC_NUMBER) values (0,to_timestamp('01302009 14:15:12','mmddyyyy hh24:mi:ss'),to_timestamp('01302009 00:00:00','mmddyyyy hh24:mi:ss'),'SAMPLETEST',2348053,to_timestamp('01302009 14:13:11','mmddyyyy hh24:mi:ss'),1234567);
Insert into SAMPLE1 (TXNAMT,TRANSDATETIME,ACTIVITYDATE,DESCRIPTION,DOCNBR,LASTDATEMAINT,DESC_NUMBER) values (0,to_timestamp('02062009 19:50:46','mmddyyyy hh24:mi:ss'),to_timestamp('02062009 00:00:00','mmddyyyy hh24:mi:ss'),'SAMPLETEST',2348053,to_timestamp('02062009 19:47:00','mmddyyyy hh24:mi:ss'),1234567);
Insert into SAMPLE1 (TXNAMT,TRANSDATETIME,ACTIVITYDATE,DESCRIPTION,DOCNBR,LASTDATEMAINT,DESC_NUMBER) values (500,to_timestamp('02062009 19:50:22','mmddyyyy hh24:mi:ss'),to_timestamp('02062009 00:00:00','mmddyyyy hh24:mi:ss'),'SAMPLETEST',2348053,to_timestamp('02062009 19:46:36','mmddyyyy hh24:mi:ss'),1234567);
Insert into SAMPLE1 (TXNAMT,TRANSDATETIME,ACTIVITYDATE,DESCRIPTION,DOCNBR,LASTDATEMAINT,DESC_NUMBER) values (500,to_timestamp('02062009 19:51:02','mmddyyyy hh24:mi:ss'),to_timestamp('02062009 00:00:00','mmddyyyy hh24:mi:ss'),'SAMPLETEST',2348053,to_timestamp('02062009 19:47:16','mmddyyyy hh24:mi:ss'),1234567);
Insert into SAMPLE1 (TXNAMT,TRANSDATETIME,ACTIVITYDATE,DESCRIPTION,DOCNBR,LASTDATEMAINT,DESC_NUMBER) values (480,to_timestamp('05302009 06:11:42','mmddyyyy hh24:mi:ss'),to_timestamp('05302009 00:00:00','mmddyyyy hh24:mi:ss'),'SAMPLETEST',2348053,to_timestamp('05302009 06:12:21','mmddyyyy hh24:mi:ss'),1234567);
Insert into SAMPLE1 (TXNAMT,TRANSDATETIME,ACTIVITYDATE,DESCRIPTION,DOCNBR,LASTDATEMAINT,DESC_NUMBER) values (60,to_timestamp('09082009 13:19:17','mmddyyyy hh24:mi:ss'),to_timestamp('09082009 00:00:00','mmddyyyy hh24:mi:ss'),'SAMPLETEST',2348053,to_timestamp('09082009 13:13:09','mmddyyyy hh24:mi:ss'),1234567);
Insert into SAMPLE1 (TXNAMT,TRANSDATETIME,ACTIVITYDATE,DESCRIPTION,DOCNBR,LASTDATEMAINT,DESC_NUMBER) values (400,to_timestamp('09192009 03:31:04','mmddyyyy hh24:mi:ss'),to_timestamp('09192009 00:00:00','mmddyyyy hh24:mi:ss'),'SAMPLETEST',2348053,to_timestamp('09192009 03:30:02','mmddyyyy hh24:mi:ss'),1234567);
Insert into SAMPLE1 (TXNAMT,TRANSDATETIME,ACTIVITYDATE,DESCRIPTION,DOCNBR,LASTDATEMAINT,DESC_NUMBER) values (0,to_timestamp('09192009 03:33:11','mmddyyyy hh24:mi:ss'),to_timestamp('09192009 00:00:00','mmddyyyy hh24:mi:ss'),'SAMPLETEST',2348053,to_timestamp('09192009 03:32:09','mmddyyyy hh24:mi:ss'),1234567);
Insert into SAMPLE1 (TXNAMT,TRANSDATETIME,ACTIVITYDATE,DESCRIPTION,DOCNBR,LASTDATEMAINT,DESC_NUMBER) values (10,to_timestamp('10122009 19:57:23','mmddyyyy hh24:mi:ss'),to_timestamp('10122009 00:00:00','mmddyyyy hh24:mi:ss'),'SAMPLETEST',2348053,to_timestamp('10122009 19:55:26','mmddyyyy hh24:mi:ss'),1234567);
Insert into SAMPLE1 (TXNAMT,TRANSDATETIME,ACTIVITYDATE,DESCRIPTION,DOCNBR,LASTDATEMAINT,DESC_NUMBER) values (20,to_timestamp('10192009 07:43:11','mmddyyyy hh24:mi:ss'),to_timestamp('10192009 00:00:00','mmddyyyy hh24:mi:ss'),'SAMPLETEST',2348053,to_timestamp('10192009 07:39:39','mmddyyyy hh24:mi:ss'),1234567);
commit;
SELECT * FROM SAMPLE1;
SELECT
TXNAMT,TRANSDATETIME,ACTIVITYDATE,DESCRIPTION,DOCNBR,LASTDATEMAINT,DESC_NUMBER,
RANK() OVER (PARTITION BY DESC_NUMBER,activitydate ORDER BY DOCNBR,LASTDATEMAINT) AS R1
FROM
SAMPLE1;
Problem to generate the sequence for odd and even records
SELECT
TXNAMT,TRANSDATETIME,ACTIVITYDATE,DESCRIPTION,DOCNBR,LASTDATEMAINT,DESC_NUMBER,
R1 RANK_FLAG,
(CASE MOD(R1,2) WHEN 1 THEN HEXTORAW('A'||SUBSTR(RAWTOHEX(NS_CONSTRAINTS.ALLOCATEOBJECTID),2)) END) AS EVT_ID
FROM
(
SELECT
TXNAMT,TRANSDATETIME,ACTIVITYDATE,DESCRIPTION,DOCNBR,LASTDATEMAINT,DESC_NUMBER,
RANK() OVER (PARTITION BY DESC_NUMBER,ACTIVITYDATE ORDER BY DOCNBR,LASTDATEMAINT) AS R1
FROM
SAMPLE1);
how to generate the sequence for even records and that to need to take the odd sequence and add it in even record.
Expected result:
||TXNAMT | TRANSDATETIME | ACTIVITYDATE| DESCRIPTION | DOCNBR | LASTDATEMAINT | DESC_NUMBER| RANK_FLAG | EVT_ID||
|0 | 30-Jan-09 | 30-Jan-09 | SAMPLETEST| 2348053 | 30-Jan-09 | 1234567 | 1 | ACE82873A9FB3F6CE04015AC526020890000|
|40 | 30-Jan-09 | 30-Jan-09 | SAMPLETEST | 2348053 | 30-Jan-09 | 1234567 | 2 | BCE82873A9FB3F6CE04015AC526020890000|
|500 | 6-Feb-09 | 6-Feb-09 | SAMPLETEST | 2348053 | 6-Feb-09 | 1234567 | 1 | ACE82873A9FC3F6CE04015AC526020890000|
|0 | 6-Feb-09 | 6-Feb-09 | SAMPLETEST | 2348053 | 6-Feb-09 | 1234567 | 2 | BCE82873A9FC3F6CE04015AC526020890000|
|500| 6-Feb-09 | 6-Feb-09 | SAMPLETEST | 2348053 | 6-Feb-09 | 1234567 | 3 | ACE82873A9FD3F6CE04015AC526020890000|
|480 | 30-May-09 | 30-May-09| SAMPLETEST | 2348053| 30-May-09 | 1234567 | 1 | ACE82873A9FE3F6CE04015AC526020890000
|
|60 | 8-Sep-09 | 8-Sep-09 | SAMPLETEST | 2348053 | 8-Sep-09| 1234567 | 1 | ACE82873A9FF3F6CE04015AC526020890000|
|400 | 19-Sep-09 | 19-Sep-09 | SAMPLETEST | 2348053 | 19-Sep-09 | 1234567 | 1 | ACE82873AA003F6CE04015AC526020890000
|0 | 19-Sep-09 | 19-Sep-09 | SAMPLETEST | 2348053 | 19-Sep-09 | 1234567 | 2 | BCE82873AA003F6CE04015AC526020890000
|
|10 | 12-Oct-09 | 12-Oct-09 | SAMPLETEST | 2348053 | 12-Oct-09 | 1234567 | 1 | ACE82873AA013F6CE04015AC526020890000
|20 | 19-Oct-09 | 19-Oct-09 | SAMPLETEST | 2348053 | 19-Oct-09 | 1234567 | 1 | ACE82873AA023F6CE04015AC526020890000|
|