Pls correct SQL - Urgent

From: <krallabandi_at_gmail.com>
Date: 19 Jul 2005 07:12:36 -0700
Message-ID: <1121782356.049068.67870_at_g49g2000cwa.googlegroups.com>

Hi,

Please correct this SQL which is returning multiple records.

SELECT DISTINCT a.CALL_TIME, a.P_TRANSID, a.METHOD, b.CALL_STATUS, FROM DT_IPOS_CALL_LOG a, DT_IPOS_CALL_STATS b WHERE a.P_TRANSID IN (7569302) AND a.P_TRANSID = b.P_TRANSID (+) ORDER BY a.CALL_TIME

The above query returns: ( pls notice it has 2 records for each )

7/18/2005 3:15:46 PM,7569302,ValidateCustomer,failure
7/18/2005 3:15:46 PM,7569302,ValidateCustomer,success
7/18/2005 3:15:50 PM,7569302,eReg.ValidateAddress,failure
7/18/2005 3:15:50 PM,7569302,eReg.ValidateAddress,success
7/18/2005 3:16:20 PM,7569302,getEligibleOffers,failure
7/18/2005 3:16:20 PM,7569302,getEligibleOffers,success
7/18/2005 3:16:40 PM,7569302,translateOffers,failure
7/18/2005 3:16:40 PM,7569302,translateOffers,success
7/18/2005 3:16:45 PM,7569302,calculateTax,failure
7/18/2005 3:16:45 PM,7569302,calculateTax,success
7/18/2005 3:36:22 PM,7569302,createOrder,failure
7/18/2005 3:36:22 PM,7569302,createOrder,success
7/18/2005 3:36:45 PM,7569302,getAvailableDates,failure
7/18/2005 3:36:45 PM,7569302,getAvailableDates,success
7/18/2005 3:43:45 PM,7569302,ScheduleOrder,failure
7/18/2005 3:43:45 PM,7569302,ScheduleOrder,success

SELECT p_transid, call_time,method, btn, call_status FROM dt_ipos_call_stats WHERE p_transid = 7569302

The above query returns: all unique records

7569302,7/18/2005 3:15:46 PM,ValidateCustomer,8132645890,success
7569302,7/18/2005 3:15:50 PM,eReg.ValidateAddress,8132645890,failure
7569302,7/18/2005 3:16:20 PM,getEligibleOffers,8132645890,success
7569302,7/18/2005 3:16:40 PM,translateOffers,8132645890,success
7569302,7/18/2005 3:16:45 PM,calculateTax,8132645890,success
7569302,7/18/2005 3:43:45 PM,ScheduleOrder,8132645890,success
7569302,7/18/2005 3:36:22 PM,createOrder,8132645890,success
7569302,7/18/2005 3:36:45 PM,getAvailableDates,8132645890,success

SELECT p_transid, call_time,method FROM dt_ipos_call_log WHERE p_transid = 7569302

The above query returns: all unique records

7569302,7/18/2005 3:15:46 PM,ValidateCustomer
7569302,7/18/2005 3:15:50 PM,eReg.ValidateAddress
7569302,7/18/2005 3:16:20 PM,getEligibleOffers
7569302,7/18/2005 3:16:40 PM,translateOffers
7569302,7/18/2005 3:16:45 PM,calculateTax
7569302,7/18/2005 3:43:45 PM,ScheduleOrder
7569302,7/18/2005 3:36:22 PM,createOrder
7569302,7/18/2005 3:36:45 PM,getAvailableDates

Please correct the first query which is returning duplicates. I know there is no relationship between the two tables. But pls suggest me if we can do any manipulation od SQL so that it will not return multiple records. I need the final result should be

7/18/2005 3:15:46 PM,7569302,ValidateCustomer,success
7/18/2005 3:15:50 PM,7569302,eReg.ValidateAddress,failure
7/18/2005 3:16:20 PM,7569302,getEligibleOffers,success
7/18/2005 3:16:40 PM,7569302,translateOffers,success
7/18/2005 3:16:45 PM,7569302,calculateTax,success
7/18/2005 3:36:22 PM,7569302,createOrder,success
7/18/2005 3:36:45 PM,7569302,getAvailableDates,success
7/18/2005 3:43:45 PM,7569302,ScheduleOrder,success

Schema of DT_IPOS_CALL_STATS:

CREATE TABLE DT_IPOS_CALL_STATS (
  P_TRANSID NUMBER (15) NOT NULL,

  CALL_TIME    DATE          DEFAULT SYSDATE,
  METHOD       VARCHAR2 (20),
  BTN          VARCHAR2 (10),
  CALL_STATUS  VARCHAR2 (10),
  ERR_CODE     NUMBER (6),
  ERR_TEXT     VARCHAR2 (300),

  J_CALL_TIME VARCHAR2 (10))
   TABLESPACE DTV_DATA NOLOGGING
   PCTFREE 10
   PCTUSED 40
   INITRANS 1
   MAXTRANS 255
  STORAGE (
   INITIAL 20971520
   NEXT 20971520
   MINEXTENTS 1
   MAXEXTENTS 500
   FREELISTS 1 FREELIST GROUPS 1 )
   NOCACHE; CREATE INDEX IX_IPOS_STATS_PID ON
  DT_IPOS_CALL_STATS(P_TRANSID)
  TABLESPACE DTV_INDX PCTFREE 10 STORAGE(INITIAL 10485760 NEXT 10485760 PCTINCREASE 0 )
;

CREATE INDEX DT_INDX_CALL_STATS_JDATE ON   DT_IPOS_CALL_STATS(J_CALL_TIME)
  TABLESPACE DTV_INDX PCTFREE 10 STORAGE(INITIAL 5242880 NEXT 5242880 PCTINCREASE 0 )
;

Schema of DT_IPOS_CALL_DETAIL:

CREATE TABLE DT_IPOS_CALL_DETAIL (
  P_TRANSID NUMBER (15),
  METHOD_NAME VARCHAR2 (10),

  CALL_DATETIME  DATE          DEFAULT SYSDATE,
  CALL_DURATION  VARCHAR2 (10),
  CALL_STATUS    VARCHAR2 (10),

  FAILURE_MSG VARCHAR2 (100))
   TABLESPACE DTV_DATA NOLOGGING
   PCTFREE 10
   PCTUSED 40
   INITRANS 1
   MAXTRANS 255
  STORAGE (
   INITIAL 20971520
   NEXT 20971520
   MINEXTENTS 1
   MAXEXTENTS 500
   FREELISTS 1 FREELIST GROUPS 1 )
   NOCACHE; Thanks in advance.

Cheers,
KK Received on Tue Jul 19 2005 - 16:12:36 CEST