Home » SQL & PL/SQL » SQL & PL/SQL » URGENT - Please help with this query !!
URGENT - Please help with this query !! [message #224276] Tue, 13 March 2007 12:39 Go to next message
amber_vaidya
Messages: 4
Registered: March 2007
Junior Member
Hi,

I have an oracle table called T_CUSTOMER_DEBT as follows

T_CUSTOMER_DEBT
----------------------------

CUST_ID		VARCHAR2(10)
AMOUNT_OWED     VARCHAR2(10)
OWED_FOR	VARCHAR2(10)


This table contains close to 200,000 records for 3000 unique CUST_IDs.

I need to print a report that lists down these 3 coulmns and also checks if a bank account exists for
the customer in the system.
There is a separate function to check if the bank account exists called pkg_val.fnc_error_msgs().

The query that I wrote was as follows


SELECT A.CUST_ID,  
       A.AMOUNT_OWED,   
       A.OWED_FOR,   
       pkg_val.fnc_error_msgs(A.CUST_ID) as VAL_MSG
   FROM T_CUSTOMER_DEBT A
  WHERE A.OWED_FOR='DESKTOPS'
  


Now, the problem is that even though this query returns the correct results, it is very slow.
I assume its because it is running the function for all the rows (200,000).

Is there any way I can write a query that would return the same results as the above query but
execute the function only for unique/distinct CUST_IDs (3000) ?

Expected result is


CUST_ID			AMOUNT_OWED			OWED_FOR		VAL_MSG
---------------------------------------------------------------------------------------------------------
C_001			12457.22			DESKTOPS		
C_001			2341.21				DESKTOPS		
C_001			87347.99			DESKTOPS
C_002			78987.98			DESKTOPS		NO BANK ACCOUNT
C_002			7823.98				DESKTOPS		NO BANK ACCOUNT
C_002			878.76				DESKTOPS		NO BANK ACCOUNT
C_003			3427.87				DESKTOPS
C_004			7823.98				DESKTOPS
C_005			8792.98				DESKTOPS		NO BANK ACCOUNT



Please help !! Thanks in advance.

- Amber
Re: URGENT - Please help with this query !! [message #224293 is a reply to message #224276] Tue, 13 March 2007 14:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Is there no way you can use the logic from this (very logically named) function and merge it into your query?
Re: URGENT - Please help with this query !! [message #224295 is a reply to message #224276] Tue, 13 March 2007 15:00 Go to previous messageGo to next message
amber_vaidya
Messages: 4
Registered: March 2007
Junior Member
Nope, can't include the logic from the function into the query.
However, this is what I came up with. Let me know if you see any problems with this query


SELECT ZZ.*,
		(case when ZZ.RANK = 1 then
			pkg_val.fnc_error_msgs(A.CUST_ID)	
		else
			''
		) as VAL_MSG
from 
(SELECT A.CUST_ID,  
       	A.AMOUNT_OWED,   
       	A.OWED_FOR,
       	RANK() OVER(PARTITION BY A.CUST_ID ORDER BY A.AMOUNT_OWED DESC) RANK
 FROM T_CUSTOMER_DEBT A
 WHERE A.OWED_FOR='DESKTOPS'
) ZZ  
  
Re: URGENT - Please help with this query !! [message #224297 is a reply to message #224295] Tue, 13 March 2007 15:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This will only return the functions result for the first record per cust_id.
I like the idea though, and I think you are on the right track here. I don't know for now how to do this, but it looks like a good start.
Re: URGENT - Please help with this query !! [message #224301 is a reply to message #224276] Tue, 13 March 2007 16:07 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Why do you need to display AMOUNT_OWED and OWED_FOR columns at all? ( OWED_FOR displays same value for all rows ).
What is the meaning of it?

If you don't need that data, then try

SELECT A.CUST_ID, pkg_val.fnc_error_msgs(A.CUST_ID)
FROM ( SELECT DISTINCT CUST_ID FROM FROM T_CUSTOMER_DEBT
       WHERE OWED_FOR='DESKTOPS' ) A


HTH.
Michael
Re: URGENT - Please help with this query !! [message #224425 is a reply to message #224301] Wed, 14 March 2007 04:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could do it like this:
SQL> create table t_customer_debt (CUST_ID VARCHAR2(10),AMOUNT_OWED     VARCHAR2(10),OWED_FOR VARCHAR2(10));

Table created.

SQL> insert into t_customer_Debt (cust_id,amount_owed,owed_for) values (1,234,'DESKTOP');
SQL> insert into t_customer_Debt (cust_id,amount_owed,owed_for) values (1,434,'DESKTOP');
SQL> insert into t_customer_Debt (cust_id,amount_owed,owed_for) values (2,450,'DESKTOP');
SQL> insert into t_customer_Debt (cust_id,amount_owed,owed_for) values (2,655,'DESKTOP');
SQL> insert into t_customer_Debt (cust_id,amount_owed,owed_for) values (3,744,'DESKTOP');

SQL> create sequence cust_debt_seq;

Sequence created.

SQL> create or replace function cust_debt_func return number as
  2    v_return  pls_integer;
  3  begin
  4    select cust_debt_seq.nextval
  5    into   v_return
  6    from dual;
  7    
  8    return v_return;
  9  end;
 10  /

Function created.

SQL> select a.cust_id
  2        ,a.amount_owed
  3        ,a.owed_for
  4        ,b.val_msg
  5  from   t_customer_debt a
  6        ,(select distinct cust_id
  7                ,cust_debt_func val_msg
  8          from (select distinct cust_id
  9                from   t_customer_debt)) b
 10  where a.cust_id = b.cust_id;      

CUST_ID    AMOUNT_OWE OWED_FOR      VAL_MSG
---------- ---------- ---------- ----------
1          234        DESKTOP             1
1          434        DESKTOP             1
2          450        DESKTOP             2
2          655        DESKTOP             2
3          744        DESKTOP             3
Re: URGENT - Please help with this query !! [message #225142 is a reply to message #224425] Sun, 18 March 2007 07:03 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
This is based on the assumption that you have a master table for customer.

I have slightly modified the version of what michael_bialik suggested. I presume you are having a master table for the Customer and the customer id has been denormalised in your t_customer_debt. Here is my example.

select * from t_customer_debt;

CUST_ID    AMOUNT_OWE OWED_FOR
---------- ---------- ----------
C_001      55.67      DESKTOP
C_002      65.67      DESKTOP
C_001      45.45      DESKTOP
C_003      455.67     COMPUTER
C_004      45.67      DESKTOP

select * from customer;

CUST_ID
----------
C_001
C_002
C_003
C_004

select cust_id, case when mod(rownum,2) = 0 then 'NO BANK ACCOUNT' ELSE NULL end acc_details 
from customer;


CUST_ID    ACC_DETAILS
---------- ---------------
C_001
C_002      NO BANK ACCOUNT
C_003
C_004      NO BANK ACCOUNT

with cust
as
(select cust_id, case when mod(rownum,2) = 0 then 'NO BANK ACCOUNT' ELSE NULL end acc_details from customer)
select tcd.cust_id, cust.acc_details, tcd.amount_owed, tcd.owed_for from t_customer_debt tcd, cust
where tcd.cust_id = cust.cust_id

CUST_ID    ACC_DETAILS     AMOUNT_OWE OWED_FOR
---------- --------------- ---------- ----------
C_001                      55.67      DESKTOP
C_001                      45.45      DESKTOP
C_002      NO BANK ACCOUNT 65.67      DESKTOP
C_003                      455.67     COMPUTER
C_004      NO BANK ACCOUNT 45.67      DESKTOP


In the above i have used case statement coupled with rownum to identify whether the customer has a Bank Account. Replace the case statement with your function

HTH.

Previous Topic: procedure & function
Next Topic: NEED OPTIMAL SOLUTION FOR COMMON WILDCARD SEARCH IN ORACLE DATABASE AGAINST 2-4 MILLION RECORDS
Goto Forum:
  


Current Time: Sat Dec 03 22:04:04 CST 2016

Total time taken to generate the page: 0.08434 seconds