OraFAQ Forum: SQL & PL/SQL » Pick One : A Query for Multiple Submission of form but to take only first one.

Home » SQL & PL/SQL » SQL & PL/SQL » Pick One : A Query for Multiple Submission of form but to take only first one. (Oracle 11g R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Pick One : A Query for Multiple Submission of form but to take only first one. [message #653141]

Tue, 28 June 2016 07:15

javed.khan
Messages: 340
Registered: November 2006
Location: Banglore

Senior Member

Guru's,

I have one scenario where i am stuck with.
I have a table which is associated with a form , form have number of status of tickets . User can either change the status and submit or update just comment and submit on same status. Here how the table looks.

CREATE TABLE "TICKETS" 
   (    "TICKET_ID" VARCHAR2(4000 CHAR), 
    "CUSTOMER_ID" NUMBER, 
    "CREATION_DATE" DATE, 
    "STATUS_ID" NUMBER, 
    "PROCESS_INSTANCE_ID" NUMBER, 
    "CLOSE_STATUS_ID" NUMBER
   ) ;

The data of this form looks like this.

SET DEFINE OFF;
INSERT INTO tickets
            (ticket_id, customer_id,
             creation_date, status_id,
             process_instance_id, close_status_id
            )
     VALUES ('IS2016060046', 237,
             TO_DATE ('02-JUN-16 02.16.57 AM', 'DD-MON-RR HH.MI.SS AM'), 1,
             124970, 8
            );
INSERT INTO tickets
            (ticket_id, customer_id,
             creation_date, status_id,
             process_instance_id, close_status_id
            )
     VALUES ('IS2016060046', 237,
             TO_DATE ('02-JUN-16 02.20.28 AM', 'DD-MON-RR HH.MI.SS AM'), 1,
             124970, 8
            );
INSERT INTO tickets
            (ticket_id, customer_id,
             creation_date, status_id,
             process_instance_id, close_status_id
            )
     VALUES ('IS2016060046', 237,
             TO_DATE ('02-JUN-16 02.20.28 AM', 'DD-MON-RR HH.MI.SS AM'), 2,
             124970, 8
            );
INSERT INTO tickets
            (ticket_id, customer_id,
             creation_date, status_id,
             process_instance_id, close_status_id
            )
     VALUES ('IS2016060046', 237,
             TO_DATE ('02-JUN-16 02.22.35 AM', 'DD-MON-RR HH.MI.SS AM'), 3,
             124970, 8
            );
INSERT INTO tickets
            (ticket_id, customer_id,
             creation_date, status_id,
             process_instance_id, close_status_id
            )
     VALUES ('IS2016060046', 237,
             TO_DATE ('02-JUN-16 02.23.34 AM', 'DD-MON-RR HH.MI.SS AM'), 4,
             124970, 8
            );
INSERT INTO tickets
            (ticket_id, customer_id,
             creation_date, status_id,
             process_instance_id, close_status_id
            )
     VALUES ('IS2016060046', 237,
             TO_DATE ('02-JUN-16 02.24.47 AM', 'DD-MON-RR HH.MI.SS AM'), 5,
             124970, 8
            );
INSERT INTO tickets
            (ticket_id, customer_id,
             creation_date, status_id,
             process_instance_id, close_status_id
            )
     VALUES ('IS2016060046', 237,
             TO_DATE ('02-JUN-16 02.25.40 AM', 'DD-MON-RR HH.MI.SS AM'), 5,
             124970, 8
            );
INSERT INTO tickets
            (ticket_id, customer_id,
             creation_date, status_id,
             process_instance_id, close_status_id
            )
     VALUES ('IS2016060046', 237,
             TO_DATE ('02-JUN-16 02.26.39 AM', 'DD-MON-RR HH.MI.SS AM'), 6,
             124970, 8
            );
INSERT INTO tickets
            (ticket_id, customer_id,
             creation_date, status_id,
             process_instance_id, close_status_id
            )
     VALUES ('IS2016060046', 237,
             TO_DATE ('02-JUN-16 02.27.21 AM', 'DD-MON-RR HH.MI.SS AM'), 7,
             124970, 8
            );
INSERT INTO tickets
            (ticket_id, customer_id,
             creation_date, status_id,
             process_instance_id, close_status_id
            )
     VALUES ('IS2016060046', 237,
             TO_DATE ('02-JUN-16 02.28.56 AM', 'DD-MON-RR HH.MI.SS AM'), 8,
             124970, 8
            );

Now i query

select * from tickets;

and getting data like this


TICKET_ID    CUSTOMER_ID    CREATION_DATE    STATUS_ID    PROCESS_INSTANCE_ID    CLOSE_STATUS_ID
------------------------------------------------------------------------------------------------
IS2016060046    237    6/2/2016 2:16:57 AM    1    124970    8
IS2016060046    237    6/2/2016 2:20:28 AM    1    124970    8
IS2016060046    237    6/2/2016 2:20:28 AM    2    124970    8
IS2016060046    237    6/2/2016 2:22:35 AM    3    124970    8
IS2016060046    237    6/2/2016 2:23:34 AM    4    124970    8
IS2016060046    237    6/2/2016 2:24:47 AM    5    124970    8
IS2016060046    237    6/2/2016 2:25:40 AM    5    124970    8
IS2016060046    237    6/2/2016 2:26:39 AM    6    124970    8
IS2016060046    237    6/2/2016 2:27:21 AM    7    124970    8
IS2016060046    237    6/2/2016 2:28:56 AM    8    124970    8

The problem i have here is like ticket number "IS2016060046" was submitted twice for status 1 but i wont be able to calculate exact time spent on the ticket for that status because of two rows of data. I will need only first submission of every ticket number and not others. The older submission.

I am working on that and will post my statements which i am trying in plsql.

Thanks

Javed

[Updated on: Tue, 28 June 2016 07:17]

Report message to a moderator

Re: Pick One : A Query for Multiple Submission of form but to take only first one. [message #653144 is a reply to message #653141]

Tue, 28 June 2016 07:22

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Do you need to define a unique constraint on TICKET_ID and STATUS to prevent the situation from arising?

Report message to a moderator

Re: Pick One : A Query for Multiple Submission of form but to take only first one. [message #653150 is a reply to message #653141]

Tue, 28 June 2016 08:00

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

How does one eliminate duplicates rows from a table?

Report message to a moderator

Re: Pick One : A Query for Multiple Submission of form but to take only first one. [message #653151 is a reply to message #653141]

Tue, 28 June 2016 08:02

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Is the form/block based on the TICKETS table? If so, then you are not using forms for their intended purpose. Using INSERT, UPDATE, DELETE against the table the block is based on is incorrect.

Report message to a moderator

Re: Pick One : A Query for Multiple Submission of form but to take only first one. [message #653161 is a reply to message #653151]

Tue, 28 June 2016 08:52

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Like john said. If you do the following

create unique index tickets_u1 on tickets(TICKET_ID,STATUS);

The ticket table will NEVER be able to have the same ticket_id with the same status.

Report message to a moderator

Re: Pick One : A Query for Multiple Submission of form but to take only first one. [message #653169 is a reply to message #653161]

Tue, 28 June 2016 09:33

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

I always like to create a non-unique index, and then declare the constraint as DEFERABLE INITIALLY IMMEDIATE. It is a bit controversial and I'm not saying that is is any sort of "best practice", but I find it gives me the most flexibility later.

Report message to a moderator

Re: Pick One : A Query for Multiple Submission of form but to take only first one. [message #653191 is a reply to message #653169]

Tue, 28 June 2016 12:48

javed.khan
Messages: 340
Registered: November 2006
Location: Banglore

Senior Member

Why i don't want to use unique constraint is because a ticket can be updated either for comments or status. If user putting comments then status will be same but will log another entry with different time. if its status change then again another entry.

Report message to a moderator

Re: Pick One : A Query for Multiple Submission of form but to take only first one. [message #653192 is a reply to message #653191]

Tue, 28 June 2016 13:02

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Your table doesn't have a column COMMENTS.

It looks to me as though your relational analysis is incorrect. You have denormalized two entities (perhaps TICKETS and TICKET_STATES) into one, which is going to mess you up badly.

[Updated on: Tue, 28 June 2016 13:02]

Report message to a moderator

Re: Pick One : A Query for Multiple Submission of form but to take only first one. [message #653193 is a reply to message #653191]

Tue, 28 June 2016 13:27

javed.khan
Messages: 340
Registered: November 2006
Location: Banglore

Senior Member

I guess i am going to use LEAD.

Report message to a moderator

Re: Pick One : A Query for Multiple Submission of form but to take only first one. [message #653194 is a reply to message #653193]

Tue, 28 June 2016 13:30

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Wrong pick, try ROW_NUMBER, RANK or DENSE_RANK.

Report message to a moderator

Re: Pick One : A Query for Multiple Submission of form but to take only first one. [message #653400 is a reply to message #653194]

Mon, 04 July 2016 13:06

javed.khan
Messages: 340
Registered: November 2006
Location: Banglore

Senior Member

I did this.


SELECT TICKET_ID, ROUND( SUM(MINUTES),2) AS TOTAL_TIME_MIN, STATUS_ID FROM (SELECT TICKET_ID,STATUS_ID,CREATION_DATE,
  LEAD (CREATION_DATE) OVER (ORDER BY CREATION_DATE) LEAD_TIME,
(LEAD (CREATION_DATE) OVER (ORDER BY CREATION_DATE) - CREATION_DATE)*24*60 MINUTES
  FROM (  SELECT * FROM MS_DW_TICKETS_LC_DETAIL WHERE TICKET_ID='Some_Id'
        ORDER BY CREATION_DATE,CUSTOMER_ID, status_id) A) GROUP BY STATUS_ID, TICKET_ID;

Report message to a moderator

Re: Pick One : A Query for Multiple Submission of form but to take only first one. [message #653401 is a reply to message #653400]

Mon, 04 July 2016 13:13

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And does this give the result?

Report message to a moderator

Previous Topic:	Constraints in External Table.
Next Topic:	String Manipulations

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 11:35:10 CDT 2024