This week's book giveaway is in the Other Languages forum.
We're giving away four copies of Rust Web Development and have Bastian Gruber on-line!
See this thread for details.
Win a copy of Rust Web Development this week in the Other Languages forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

Please help with this query ..

 
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Ranch Hand
Posts: 158
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 209
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
How about this

 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic