Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    148

    Unanswered: Query Question - Get the first value...

    Hi all. I have a query below. The problem is there aren't any relationships besides the one I'm using to join the tables. There are two tables:
    ARA.ITS_TICKETS AND ITS_AUDIT.
    I am joining these tables by the TXT_REQUEST_ID column. The problem is, in the ITS_AUDIT table, if there is more than one phone number assigned to a REQUEST_ID, they duplicate the REQUEST_ID row and add a phone number. This will make more sense if you scroll down to the output/query. So basically, Im looking for a way to get only the first phone number instead of all of the phone numbers per TXT_REQUEST_ID. Any help would be fantastic.

    Code:
    SQL> SELECT DISTINCT TICKET.TXT_CUST_REQUEST_ID, A.TXT_CUST_REQUEST_ID,A.TXT_CONTACT_NBR
      2  FROM ARA.ITS_TICKETS TICKET, ITS_AUDIT A 
      3  WHERE TICKET.TXT_ASSIGNED_TO = 'NETWORK' 
      4  AND TICKET.START_TIME BETWEEN ('1093600000' ) AND ('1093621262')  
      5  AND TICKET.TXT_REQUEST_ID = A.TXT_CUST_REQUEST_ID
      6  AND A.TXT_CONTACT_NBR IS NOT NULL;
    
    TXT_REQUEST_ID  TXT_REQUEST_ID  TXT_CONTACT_
    --------------- --------------- ------------
    000000000101907 000000000101907 XXX.712.804X
    000000000101907 000000000101907 XXX.980.486X
    000000000102015 000000000102015 XXX.343.850X
    000000000129721 000000000129721 XXX.365.789X
    000000000129721 000000000129721 XXX.656.740X
    000000000160447 000000000160447 XXX.262.996X
    000000000160615 000000000160615 XXX.578.470X
    000000000160615 000000000160615 XXX.730.850X
    000000000160615 000000000160615 XXX.859.584X
    000000000162032 000000000162032 XXX.563.328X
    000000000162032 000000000162032 XXX.656.730X
    
    TXT_REQUEST_ID  TXT_REQUEST_ID  TXT_CONTACT_
    --------------- --------------- ------------
    000000000168737 000000000168737 XXX.366.912X
    000000000168737 000000000168737 XXX.351.800X
    000000000168737 000000000168737 XXX.921.078X
    000000000168737 000000000168737 XXX.921.807X
    000000000191341 000000000191341 XXX.730.850X
    000000000204106 000000000204106 XXX.217.605X
    000000000214594 000000000214594 XXX.309.112X
    000000000214594 000000000214594 XXX.479.558X
    000000000214594 000000000214594 XXX.785.174X
    000000000234395 000000000234395 XXX.937.296X
    
    21 rows selected.
    
    SQL>

  2. #2
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    You could try first value analytic:

    SELECT DISTINCT TICKET.TXT_CUST_REQUEST_ID, A.TXT_CUST_REQUEST_ID,first_value(A.TXT_CONTACT_NB R)
    over (partition A.TXT_CUST_REQUEST_ID)
    FROM ARA.ITS_TICKETS TICKET, ITS_AUDIT A
    WHERE TICKET.TXT_ASSIGNED_TO = 'NETWORK'
    AND TICKET.START_TIME BETWEEN ('1093600000' ) AND ('1093621262')
    AND TICKET.TXT_REQUEST_ID = A.TXT_CUST_REQUEST_ID
    AND A.TXT_CONTACT_NBR IS NOT NULL;
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  3. #3
    Join Date
    Dec 2003
    Posts
    148

    hmm..

    For some reason running that query with the first_of function totally locks my sql plus window up...Am I missing some part of that?

  4. #4
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    Probably, I haven't used, it so the syntax is most likely off...

    Here's the example:

    http://download-west.oracle.com/docs...tion.htm#83214
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Not sure how you determine what is the "first phone #".
    Is it the first ENTERED? The LOWEST number? The HOME number?
    The PREFERRED contact number?

    nevertheless, just use analytics:

    PHP Code:
    select 
        TXT_CUST_REQUEST_ID

         
    TXT_CUST_REQUEST_ID,
         
    TXT_CONTACT_NBR 
    from 
    (
            
    SELECT DISTINCT 
                TICKET
    .TXT_CUST_REQUEST_ID
                
    A.TXT_CUST_REQUEST_ID,
                
    A.TXT_CONTACT_NBR,
                
    count(*) over (partition by TICKET.TXT_CUST_REQUEST_IDA.TXT_CUST_REQUEST_ID
                                    order by TICKET
    .TXT_CUST_REQUEST_IDA.TXT_CUST_REQUEST_IDA.TXT_CONTACT_NBRthe_order
            FROM ARA
    .ITS_TICKETS TICKETITS_AUDIT A 
                 WHERE TICKET
    .TXT_ASSIGNED_TO 'NETWORK' 
                 
    AND TICKET.START_TIME BETWEEN ('1093600000' ) AND ('1093621262')  
                 AND 
    TICKET.TXT_REQUEST_ID A.TXT_CUST_REQUEST_ID
                 
    AND A.TXT_CONTACT_NBR IS NOT NULL)
    where the_order 1
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •