Home » SQL & PL/SQL » SQL & PL/SQL » Sql Query (Oracle 11g Unix)
Sql Query [message #600374] Tue, 05 November 2013 11:24 Go to next message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
Hi

Could someone advise how to get achieve this output?

Table Script:
CREATE TABLE TEST
(
  VALUE  VARCHAR2(20 BYTE)                          NULL,
  DDATE  DATE                                       NULL
)


Source Data Scripts:
INSERT INTO TEST (VALUE, DDATE) VALUES ('TRAN1'  ,to_date('05-Nov-2013 12:10:43','dd-Mon-yyyy hh24:mi:ss') );
INSERT INTO TEST (VALUE, DDATE) VALUES ('TRAN1'  ,to_date('05-Nov-2013 12:13:23','dd-Mon-yyyy hh24:mi:ss') );
INSERT INTO TEST (VALUE, DDATE) VALUES ('TRAN1'  ,to_date('05-Nov-2013 12:20:10','dd-Mon-yyyy hh24:mi:ss') );
INSERT INTO TEST (VALUE, DDATE) VALUES ('TRAN1'  ,to_date('05-Nov-2013 12:20:17','dd-Mon-yyyy hh24:mi:ss') );
INSERT INTO TEST (VALUE, DDATE) VALUES ('TRAN2'  ,to_date('05-Nov-2013 12:20:55','dd-Mon-yyyy hh24:mi:ss') );
INSERT INTO TEST (VALUE, DDATE) VALUES ('TRAN2'  ,to_date('05-Nov-2013 12:29:58','dd-Mon-yyyy hh24:mi:ss') );
INSERT INTO TEST (VALUE, DDATE) VALUES ('TRAN2'  ,to_date('05-Nov-2013 12:50:27','dd-Mon-yyyy hh24:mi:ss') );
INSERT INTO TEST (VALUE, DDATE) VALUES ('TRAN1'  ,to_date('05-Nov-2013 12:10:43','dd-Mon-yyyy hh24:mi:ss') );


Output:

TRAN1,TRAN2, 05/11/2013 12:10:43, 05/11/2013 12:20:55

TRAN1,TRAN2, 05/11/2013 12:13:23, 05/11/2013 12:29:58
 


We call above each source data record as one transaction. There are two types of transactions 1. TRAN1 2. TRAN2

We need combined transactions above above output.

when the TRAN1 is raised there may be corresponding TRAN2 transaction in next 30 mins. we need to combine those two transaction and write as single transaction.

Condition is

1. If there are more than one TRAN2 transaction for TRAN1 transaction you need to capture the earliest one.
2. You should not use already used TRAN2 transactions For Eg: You may find multiple TRAN2 transactions within the 30 mins duration but earliest dated transaction has been taken by TRAN1 transaction already. that TRAN2transaction should be skipped and you will have to take the next unused transaction
3. You can skip TRAN1 transaction, If you don't find the corresponding TRAN2 transaction for TRAN1 transactions

[Updated on: Tue, 05 November 2013 11:27]

Report message to a moderator

Re: Sql Query [message #600376 is a reply to message #600374] Tue, 05 November 2013 12:07 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1956
Registered: January 2010
Senior Member
Not clear what you need, but you could use ROW_NUMBER() OVER(PARTITION BY VALUE ORDER BY DDATE).

SY.

[Updated on: Tue, 05 November 2013 12:10]

Report message to a moderator

Re: Sql Query [message #600381 is a reply to message #600376] Tue, 05 November 2013 16:57 Go to previous messageGo to next message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
What i wanted is I want to combine the transactions Tran1 and Tran2 by satisfying the conditions
For every Tran1 there will be one Tran2 record within next 30 mins some time there may be no record some times there may be more than one record. We will have to combine Tran1 with earliest Tran2 record. when you process next Tran2 record there may be same Tran2 record(which is processed already) along with other Tran2 records in 30mins. In this case skip the processed Tran2 record and combine with next Tran2 record.

I didn't get you where i can use Row_number().

Could you please extend some more.

Thanks
Re: Sql Query [message #600408 is a reply to message #600381] Wed, 06 November 2013 02:20 Go to previous messageGo to next message
pablolee
Messages: 2612
Registered: May 2007
Location: Scotland
Senior Member
Hi, it might help visualise your rules if you post sample data and outputs that represent each of the rules that you have listed.
Re: Sql Query [message #600410 is a reply to message #600408] Wed, 06 November 2013 02:30 Go to previous messageGo to next message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
Hi Pablolee,

I have posted sample data scripts and table scripts as well in my first post.

SAMPLE DATA:
VALUE,DDATE
TRAN1,05-Nov-2013 12:10:43
TRAN1,05-Nov-2013 12:13:23
TRAN1,05-Nov-2013 12:20:10
TRAN1,05-Nov-2013 12:20:17
TRAN2,05-Nov-2013 12:20:55
TRAN2,05-Nov-2013 12:29:58
TRAN2,05-Nov-2013 12:50:27
TRAN1,05-Nov-2013 12:10:43

Re: Sql Query [message #600412 is a reply to message #600410] Wed, 06 November 2013 03:06 Go to previous messageGo to next message
pablolee
Messages: 2612
Registered: May 2007
Location: Scotland
Senior Member
Yes, I saw that. are you saying that that sample data covers every scenario of your requirements? Scratch that, it appears that it does.
OK, have you made any attempts?
It looks to me like you need to, in the initial stage, effectively split this into 2 data sets, one for tran 1 and on for tran 2. You then need to join tran 1 to tran 2 where tran time is between tra1 time and tran1 time + 30 minutes.
Give that a try, it won't be the final solution, but it'll be a good start.
After that, you'll want to be using some analytics (rank or rownum) to identify the most recent/earliest tran2.
THe next bit will require some thought...
Give it a bash, post back with what you achieve.
Re: Sql Query [message #600423 is a reply to message #600381] Wed, 06 November 2013 06:22 Go to previous messageGo to next message
ejdrba
Messages: 27
Registered: May 2005
Location: Kolkata
Junior Member

Hi,

Try below -


select 
TRIM((
SELECT LISTAGG(val, ',') WITHIN GROUP (ORDER BY num) AS val
FROM   (select distinct 1 num, value val from testt)
GROUP BY num
))||', '||
listagg(to_char(ddate, 'dd/mm/rrrr hh:mi:ss'), ', ') within group (order by rn) as dval
from (
select ddate, row_number() over (partition by value order by ddate) rn  
from testt
) group by rn


Re: Sql Query [message #600426 is a reply to message #600381] Wed, 06 November 2013 07:23 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
First TRAN1 is at 12:10:43. Two TRAN2 are within 30 mins, so use the first one - 12:20:55.
Second TRAN1 is at 12:13:23. Two TRAN2 are within 30 mins, first has already been used, so use 2nd - 12:29:58
Third TRAN1 is at 12:20:10. The same two TRAN2 are within 30 mins, but both have already been used, so display nothing.
Same for fourth TRAN1.
Re: Sql Query [message #600447 is a reply to message #600374] Wed, 06 November 2013 14:11 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1956
Registered: January 2010
Senior Member
Assuming you are on 11g:

WITH t AS (
           SELECT  value,
                   ddate,
                   row_number() over(partition by value order by ddate) rn
             FROM  test
          ),
     r(
       value1,
       value2,
       ddate1,
       ddate2,
       rn1,
       rn2
      ) AS (
             SELECT  'TRAN1'       value1,
                     'TRAN2'       value2,
                     t1.ddate      ddate1,
                     min(t2.ddate) ddate2,
                     t1.rn         rn1,
                     min(t2.rn)    rn2
               FROM  t t1,
                     t t2
               WHERE t1.value =  'TRAN1'
                 AND t1.rn    =  1
                 AND t2.value =  'TRAN2'
                 AND t2.ddate >= t1.ddate
                 AND t2.ddate <= t1.ddate + 1 / 48
               GROUP BY t1.ddate,
                        t1.rn
            UNION ALL
             SELECT  t1.value value1,
                     t2.value value2,
                     t1.ddate ddate1,
                     t2.ddate ddate2,
                     t1.rn    rn1,
                     t2.rn    rn2
               FROM      r
                     JOIN
                         t t1
                       ON (
                               t1.rn =  r.rn1 + 1
                           AND
                               t1.value    =  'TRAN1'
                          )
                     LEFT JOIN
                         t t2
                       ON (
                               t1.value    =  'TRAN1'
                           AND
                               t2.value    =  'TRAN2'
                           AND
                               t2.ddate >= t1.ddate
                           AND
                               t2.ddate <= t1.ddate + 1 / 48
                           AND
                               t2.rn > r.rn2
                          )
           )
select  value1,
        value2,
        ddate1,
        ddate2
  from  r
  where value2 is not null
/

VALUE1               VALUE2               DDATE1              DDATE2
-------------------- -------------------- ------------------- -------------------
TRAN1                TRAN2                11/05/2013 12:10:43 11/05/2013 12:20:55
TRAN1                TRAN2                11/05/2013 12:10:43 11/05/2013 12:29:58

SCOTT@orcl > 


Update: Just noticed source data have two TRAN1 with date 11/05/2013 12:10:43. I will assume this is a typo and there should be only one:

SCOTT@orcl > SELECT  *
  2            FROM  test
  3  /

VALUE                DDATE
-------------------- -------------------
TRAN1                11/05/2013 12:10:43
TRAN1                11/05/2013 12:13:23
TRAN1                11/05/2013 12:20:10
TRAN1                11/05/2013 12:20:17
TRAN2                11/05/2013 12:20:55
TRAN2                11/05/2013 12:29:58
TRAN2                11/05/2013 12:50:27

7 rows selected.

WITH t AS (
           SELECT  value,
                   ddate,
                   row_number() over(partition by value order by ddate) rn
             FROM  test
          ),
     r(
       value1,
       value2,
       ddate1,
       ddate2,
       rn1,
       rn2
      ) AS (
             SELECT  'TRAN1'       value1,
                     'TRAN2'       value2,
                     t1.ddate      ddate1,
                     min(t2.ddate) ddate2,
                     t1.rn         rn1,
                     min(t2.rn)    rn2
               FROM  t t1,
                     t t2
               WHERE t1.value =  'TRAN1'
                 AND t1.rn    =  1
                 AND t2.value =  'TRAN2'
                 AND t2.ddate >= t1.ddate
                 AND t2.ddate <= t1.ddate + 1 / 48
               GROUP BY t1.ddate,
                        t1.rn
            UNION ALL
             SELECT  t1.value value1,
                     t2.value value2,
                     t1.ddate ddate1,
                     t2.ddate ddate2,
                     t1.rn    rn1,
                     t2.rn    rn2
               FROM      r
                     JOIN
                         t t1
                       ON (
                               t1.rn =  r.rn1 + 1
                           AND
                               t1.value    =  'TRAN1'
                          )
                     LEFT JOIN
                         t t2
                       ON (
                               t1.value    =  'TRAN1'
                           AND
                               t2.value    =  'TRAN2'
                           AND
                               t2.ddate >= t1.ddate
                           AND
                               t2.ddate <= t1.ddate + 1 / 48
                           AND
                               t2.rn > r.rn2
                          )
           )
select  value1,
        value2,
        ddate1,
        ddate2
  from  r
  where value2 is not null
/

VALUE1               VALUE2               DDATE1              DDATE2
-------------------- -------------------- ------------------- -------------------
TRAN1                TRAN2                11/05/2013 12:10:43 11/05/2013 12:20:55
TRAN1                TRAN2                11/05/2013 12:13:23 11/05/2013 12:29:58

SCOTT@orcl > 


SY.

[Updated on: Wed, 06 November 2013 14:19]

Report message to a moderator

Re: Sql Query [message #600452 is a reply to message #600447] Wed, 06 November 2013 16:57 Go to previous messageGo to next message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
Thanks everyone. I will check and come back to you

Regards
Nagaraju
Re: Sql Query [message #600488 is a reply to message #600447] Thu, 07 November 2013 04:48 Go to previous messageGo to next message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
Thanks i have the result as expected.

Thanks to everyone.

[Updated on: Thu, 07 November 2013 04:52]

Report message to a moderator

Re: Sql Query [message #601072 is a reply to message #600488] Fri, 15 November 2013 04:35 Go to previous messageGo to next message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
Hi Solomon,

this query is working only for this data, if i have huge number of rows, it just returns the first two rows.

Can you please advise if we can consider all records init.

Thanks
Nagaraju
Re: Sql Query [message #601073 is a reply to message #601072] Fri, 15 November 2013 04:40 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
It would help if you supplied sample data that would result in more than 2 rows.
Re: Sql Query [message #601074 is a reply to message #601073] Fri, 15 November 2013 05:06 Go to previous messageGo to next message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
INSERT INTO TEST (VALUE, DDATE) VALUES ('TRAN1'  ,to_date('05-Nov-2013 12:10:43','dd-Mon-yyyy hh24:mi:ss') );
INSERT INTO TEST (VALUE, DDATE) VALUES ('TRAN1'  ,to_date('05-Nov-2013 12:13:23','dd-Mon-yyyy hh24:mi:ss') );
INSERT INTO TEST (VALUE, DDATE) VALUES ('TRAN1'  ,to_date('05-Nov-2013 12:20:10','dd-Mon-yyyy hh24:mi:ss') );
INSERT INTO TEST (VALUE, DDATE) VALUES ('TRAN1'  ,to_date('05-Nov-2013 12:20:57','dd-Mon-yyyy hh24:mi:ss') );
INSERT INTO TEST (VALUE, DDATE) VALUES ('TRAN2'  ,to_date('05-Nov-2013 12:20:55','dd-Mon-yyyy hh24:mi:ss') );
INSERT INTO TEST (VALUE, DDATE) VALUES ('TRAN2'  ,to_date('05-Nov-2013 12:29:58','dd-Mon-yyyy hh24:mi:ss') );
INSERT INTO TEST (VALUE, DDATE) VALUES ('TRAN2'  ,to_date('05-Nov-2013 12:50:27','dd-Mon-yyyy hh24:mi:ss') );



It should return the 3 rows but in real time there would be more rows to consider

[Updated on: Fri, 15 November 2013 05:07]

Report message to a moderator

Re: Sql Query [message #601086 is a reply to message #601074] Fri, 15 November 2013 07:52 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1956
Registered: January 2010
Senior Member
nagaraju.ch wrote on Fri, 15 November 2013 06:06
It should return the 3 rows but in real time there would be more rows to consider


Not based on your 30 minute rule. TRAN1 #3 has ddate of 05-Nov-2013 12:20:10. So my code is looking for a yet non-paired TRAN2 which is within 30 min range. And nearest yet non-paired TRAN2 has ddate 05-Nov-2013 12:50:27 which is 17 seconds outside 30 min interval. You need to remove t2.ddate <= t1.ddate + 1 / 48 condition if you want to pair TRAN1 with nearest yet non-paired TRAN2 regardless of ddate diff. Also, there is a bug in my code. Corrected code without 30 min check:

WITH t AS (
           SELECT  value,
                   ddate,
                   row_number() over(partition by value order by ddate) rn
             FROM  test
          ),
     r(
       value1,
       value2,
       ddate1,
       ddate2,
       rn1,
       rn2,
       lvl
      ) AS (
             SELECT  'TRAN1'       value1,
                     'TRAN2'       value2,
                     t1.ddate      ddate1,
                     min(t2.ddate) ddate2,
                     t1.rn         rn1,
                     min(t2.rn)    rn2,
                     1 lvl
               FROM  t t1,
                     t t2
               WHERE t1.value =  'TRAN1'
                 AND t1.rn    =  1
                 AND t2.value =  'TRAN2'
                 AND t2.ddate >= t1.ddate
                 AND t2.ddate <= t1.ddate + 1 / 48
               GROUP BY t1.ddate,
                        t1.rn
            UNION ALL
             SELECT  t1.value value1,
                     t2.value value2,
                     t1.ddate ddate1,
                     t2.ddate ddate2,
                     t1.rn    rn1,
                     t2.rn    rn2,
                     max(r.rn1) over() lvl
               FROM      r
                     JOIN
                         t t1
                       ON (
                               r.rn1 =  r.lvl
                           AND
                               t1.rn =  r.rn1 + 1
                           AND
                               t1.value    =  'TRAN1'
                          )
                     LEFT JOIN
                         t t2
                       ON (
                               t1.value    =  'TRAN1'
                           AND
                               t2.value    =  'TRAN2'
                           AND
                               t2.ddate >= t1.ddate
--                           AND
--                               t2.ddate <= t1.ddate + 1 / 48
                           AND
                               t2.rn > r.rn2
                          )
           )
select  value1,
        value2,
        ddate1,
        ddate2
  from  r
  where value2 is not null
/

VALUE1 VALUE2 DDATE1              DDATE2
------ ------ ------------------- -------------------
TRAN1  TRAN2  11/05/2013 12:10:43 11/05/2013 12:20:55
TRAN1  TRAN2  11/05/2013 12:13:23 11/05/2013 12:29:58
TRAN1  TRAN2  11/05/2013 12:13:23 11/05/2013 12:50:27

SCOTT@orcl > 


SY.
Re: Sql Query [message #601087 is a reply to message #601086] Fri, 15 November 2013 08:09 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
TRAN1 #3 doesn't match anything according to the rule but TRAN1 #4 does - TRAN2 #3 is 29 mins 30 seconds later.
Re: Sql Query [message #601094 is a reply to message #601087] Fri, 15 November 2013 09:32 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1956
Registered: January 2010
Senior Member
cookiemonster wrote on Fri, 15 November 2013 09:09
TRAN1 #3 doesn't match anything according to the rule but TRAN1 #4 does - TRAN2 #3 is 29 mins 30 seconds later.


Oops,
You are correct. And the issue is I need to preserve max(rn2) in outer join when no math is found. Hope I got it right this time:

WITH t AS (
           SELECT  value,
                   ddate,
                   row_number() over(partition by value order by ddate) rn
             FROM  test
          ),
     r(
       value1,
       value2,
       ddate1,
       ddate2,
       rn1,
       rn2,
       rnk
      ) AS (
             SELECT  'TRAN1'       value1,
                     'TRAN2'       value2,
                     t1.ddate      ddate1,
                     min(t2.ddate) ddate2,
                     t1.rn         rn1,
                     min(t2.rn)    rn2,
                     1 rnk
               FROM  t t1,
                     t t2
               WHERE t1.value =  'TRAN1'
                 AND t1.rn    =  1
                 AND t2.value =  'TRAN2'
                 AND t2.ddate >= t1.ddate
                 AND t2.ddate <= t1.ddate + 1 / 48
               GROUP BY t1.ddate,
                        t1.rn
            UNION ALL
             SELECT  t1.value value1,
                     t2.value value2,
                     t1.ddate ddate1,
                     t2.ddate ddate2,
                     t1.rn    rn1,
                     nvl(t2.rn,max(r.rn2) over()) rn2,
                     row_number() over(partition by r.rn1 order by t2.rn) rnk
               FROM      r
                     JOIN
                         t t1
                       ON (
                               r.rnk =  1
                           AND
                               t1.rn =  r.rn1 + 1
                           AND
                               t1.value    =  'TRAN1'
                          )
                     LEFT JOIN
                         t t2
                       ON (
                               t1.value    =  'TRAN1'
                           AND
                               t2.value    =  'TRAN2'
                           AND
                               t2.ddate >= t1.ddate
                           AND
                               t2.ddate <= t1.ddate + 1 / 48
                           AND
                               t2.rn > r.rn2
                          )
           )
select  value1,
        value2,
        ddate1,
        ddate2
  from  r
  where value2 is not null
    and rnk = 1
/

VALUE1 VALUE2 DDATE1              DDATE2
------ ------ ------------------- -------------------
TRAN1  TRAN2  11/05/2013 12:10:43 11/05/2013 12:20:55
TRAN1  TRAN2  11/05/2013 12:13:23 11/05/2013 12:29:58
TRAN1  TRAN2  11/05/2013 12:20:57 11/05/2013 12:50:27

SCOTT@orcl >  


SY.
Re: Sql Query [message #601380 is a reply to message #601094] Wed, 20 November 2013 04:56 Go to previous messageGo to next message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
Thanks SY

but this syntax is not supported by my oracle version 11.2.0.1.0. It gives me the below error when i execute

ORA-32486: unsupported operation in recursive branch of recursive WITH clause 
Re: Sql Query [message #601393 is a reply to message #601380] Wed, 20 November 2013 08:42 Go to previous message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
Hi

I wrote procedure to get this as below and working fine.

CREATE OR REPLACE PROCEDURE NEWTRAN
IS
L_DDATE DATE := NULL;
L_VALUE VARCHAR2(20);
--L_STRING VARCHAR2(2000);
	CURSOR C1 
		IS
			SELECT 
				* 
			FROM 
				TEST
			WHERE
				VALUE='TRAN1'
			ORDER BY DDATE ASC;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   DELETE FROM XX_TEST1;
   COMMIT;
	FOR I IN C1
		LOOP
			IF L_DDATE IS NOT NULL
				THEN
					INSERT 
						INTO 
							XX_TEST1
						VALUES (L_DDATE);

					COMMIT;
			END IF;

		L_DDATE      := NULL;
		L_VALUE     := NULL;
			
			SELECT 
				MIN(DDATE)
				,VALUE
			INTO
				L_DDATE,
				L_VALUE
			FROM
				TEST
			WHERE
				VALUE='TRAN2'
				AND ( (ddate - i.ddate) * 1440) < 30
				AND DDATE NOT IN (SELECT DDATE FROM XX_TEST1)
			GROUP BY VALUE;
		IF L_DDATE IS NOT NULL AND L_VALUE IS NOT NULL
			THEN
				INSERT 
					INTO 
						TEST 
					VALUES(
							'TRAN3'
							,I.DDATE
							,L_DDATE
										);
				COMMIT;
		END IF;
		END LOOP;
		
		--DBMS_OUTPUT.put_line(L_STRING);
END NEWTRAN;



But there slight change in requirement, Eg:there are three Tran2 rows available in 30mins and all three were used by previous Tran1 rows if there is another Tran1 row it should merge with first Tran2 record. Could someone guide me on this please.
I have changed the DDL for TEST table and Created one new table XX_TEST1 to store the dates.

CREATE TABLE TEST
(
  VALUE   VARCHAR2(20 BYTE)                         NULL,
  DDATE   DATE                                      NULL,
  DDATE2  DATE                                      NULL
);

CREATE TABLE XX_TEST1
(
  DDATE  DATE                                       NULL
)


Output:
TRAN3  11/05/2013 12:10:43 11/05/2013 12:20:55
TRAN3  11/05/2013 12:13:23 11/05/2013 12:29:58
TRAN3  11/05/2013 12:20:57 11/05/2013 12:50:27
TRAN3  11/05/2013 12:20:10 11/05/2013 12:20:55
Previous Topic: please help me with Travelling Salesman Problem
Next Topic: Please Suggest in this query what should i do..
Goto Forum:
  


Current Time: Sat Jul 26 14:55:18 CDT 2014

Total time taken to generate the page: 0.30705 seconds