| URGENT - Please help with this query !! [message #224276] |
Tue, 13 March 2007 12:39  |
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 #224295 is a reply to message #224276] |
Tue, 13 March 2007 15:00  |
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 #224301 is a reply to message #224276] |
Tue, 13 March 2007 16:07 |
michael_bialik
Messages: 621
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 |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
