Win a copy of Learn Spring Security (video course) this week in the Spring forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Please help with this query ..

 
Amber Vaidya
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 158
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 158
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 209
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How about this

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic