Pls correct SQL - Urgent

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

.

Relevant Pages

  • Pls correct SQL. Urgent
    ... Please correct this SQL which is returning multiple records. ... The above query returns: all unique records ... CALL_STATUS VARCHAR2, ...
    (comp.databases.oracle.server)
  • Pls correct SQL - Urgent
    ... Please correct this SQL which is returning multiple records. ... The above query returns: all unique records ... CALL_STATUS VARCHAR2, ...
    (comp.databases.oracle.tools)
  • Re: ORA-01461 while moving data from sqlserver to oracle
    ... > bytes in a VarChar2 if I'm not mistaken. ... > how you can query the data. ... the auto-generated SQL isn't very efficient. ... A conversion program is not run daily. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: ORA-01461 while moving data from sqlserver to oracle
    ... bytes in a VarChar2 if I'm not mistaken. ... You can always use a ?LOB field for larger data, ... how you can query the data. ... the auto-generated SQL isn't very efficient. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Interpolation Query...
    ... I follow your interpolation math but the SQL ... variables and then my main query will do the interpolation correct? ... Why then do i get multiple records back. ... >> when I reopen the qury in the designer the brackets are back. ...
    (microsoft.public.access.queries)