This week's book giveaway is in the Android forum.
We're giving away four copies of Head First Android and have Dawn & David Griffiths on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes Please help with this query .. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Head First Android this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Please help with this query .." Watch "Please help with this query .." New topic
Author

Please help with this query ..

Amber Vaidya
Ranch Hand

Joined: Oct 14, 2003
Posts: 42
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 ]
A knibbs
Ranch Hand

Joined: Aug 23, 2006
Posts: 158

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

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.
Amber Vaidya
Ranch Hand

Joined: Oct 14, 2003
Posts: 42
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?
A knibbs
Ranch Hand

Joined: Aug 23, 2006
Posts: 158
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.
Amber Vaidya
Ranch Hand

Joined: Oct 14, 2003
Posts: 42
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.
Justin Chu
Ranch Hand

Joined: Apr 19, 2002
Posts: 209
    
    1
How about this

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Please help with this query ..
 
It's not a secret anymore!