Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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),
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),
Cheers,
KK
Received on Tue Jul 19 2005 - 09:10:54 CDT