Please help with this query ..

Hi,

I have an oracle table called T_CUSTOMER_DEBT as follows

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

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

Please help !! Thanks in advance.

- Amber

[edited subject - was "URGENT !! Please help with this query .."]
[ March 13, 2007: Message edited by: Jeanne Boyarsky ]

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_IDAMOUNT_OWEDOWED_FORVAL_MSG ------------------------------------------------------------------- C_00112457.22DESKTOPS C_0012341.21DESKTOPS C_00187347.99DESKTOPS C_00278987.98DESKTOPSNO BANK ACCOUNT C_0027823.98DESKTOPSNO BANK ACCOUNT C_002878.76DESKTOPSNO BANK ACCOUNT C_0033427.87DESKTOPS C_0047823.98DESKTOPS C_0058792.98DESKTOPSNO BANK ACCOUNT
Please help !! Thanks in advance.

- Amber

I might be missing something but when you said you want it for distinct Id's do you mean that you only want each Id to appear once ? If that is the case your sample output is wrong or your phrasing seems wrong, as your sample has the same Id multiple times.

That's exactly where I am stuck . I need the first three columns to appear as they do in the expected result (with the same CUST_ID appearing multiple times) , but the value in the last column (VAL_MSG) can appear either once for every CUST_ID or multiple times.

The purpose is to determine if the customer has a valid bank account or not.

Is this possible with a single query?

Originally posted by Amber Vaidya:
That's exactly where I am stuck . I need the first three columns to appear as they do in the expected result (with the same CUST_ID appearing multiple times) , but the value in the last column (VAL_MSG) can appear either once for every CUST_ID or multiple times.

The purpose is to determine if the customer has a valid bank account or not.

Is this possible with a single query?

I don't understand when you say the last column can appear once or multiple times - if you want to see if a customer has a valid account should you not bedoing this:
Looking at each record for the same id until you either finish looking at the records or until you find they have a valid bank account. I don't understand how the same customer can both have a valid bank account and not have a valid bank account. the only way this may make sense is if you have another column such as order which identifies each of the rows as a unique order for a customer.
I can't say if it should be able to be done in one row until I understand what you are trying to do.

Ps. I'm no DBA but it seems like your table has more information in it than it should, but remember that's from a most likely unqualified person.

Here's what I was trying to do

Basically, run the function once per CUST_ID. Sorry if I did not explain it clearly.

Thanks anyway.

How about this