wood burning stoves 2.0*
The moose likes JDBC 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 Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
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 ..
 
Similar Threads
IllegalArgumentException when using clj-record's create function
Problem with unique and unique-key
Issue in inserting into DB using Hibernate, Spring
Help required with DB pagination
problem framing subquery...