| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Pls correct SQL. Urgent
Comments embedded.
KK wrote:
> 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
>
One for 'success', one for 'failure'. This is a problem how?
>
> 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.
There are no 'duplicates' in your output, period. Look at it again; there is one record for 'success' and one record for 'failure' in each 'set', making each record UNIQUE.
> 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
>
You only want the failure record for eReg.ValidateAddress? That doesn't constitute removing duplicates, ir involves restricting your result set through the WHERE clause:
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 (+) AND a.method not like 'eReg%' AND b.call_status = 'success'
AND a.P_TRANSID = b.P_TRANSID (+) AND a.method like 'eReg%' AND b.call_status = 'failure'
>
> 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
David Fitzjarrell Received on Tue Jul 19 2005 - 11:30:06 CDT
 |
 |