Home » SQL & PL/SQL » SQL & PL/SQL » customer data retrieval (oracle 11.5.9)
customer data retrieval [message #292765] Wed, 09 January 2008 16:24 Go to next message
raj_chmi
Messages: 7
Registered: August 2007
Location: HYDERABAD
Junior Member

HI ALL,

I HAVE WRITTEN QUERY FOR CUSTOMER RETRIEVAL FROM ORACLE DATABASE OF 11.0.3 VERSION. I AM WRITING THIS SELECT STATEMENT FOR THE PURPOSE OF CUSTOMER CONVERSION.THE PROBLEM IS THAT IT RETRIEVE MORE THAN 3000 DATA , BUT I WANT ONLY <3000. WHATEVER THE JOINS CONDITIONS GIVEN BY ME, MAY BE SOME HAVE ERROR.

MY REQUEST IS , IF ANYONE HAVE DONE THAT , PLEASE SUGGEST ME OR IF ANYONE HAVE THE QUERY STATEMENT ,PLEASE FORWARD ME.
MY MAIL ID IS :RAJ_VIC5@REDIFFMAIL.COM

THE SELECT STATEMENT IS:

SELECT
R_CUST.CUSTOMER_NAME ,
R_CUST.STATUS,
R_CUST.CUSTOMER_TYPE,
R_CUST.CUSTOMER_PROSPECT_CODE,
R_CUST.CUSTOMER_CLASS_CODE,
R_CUST.CUSTOMER_CATEGORY_CODE,
R_CUST.ORIG_SYSTEM_REFERENCE,
R_ADD.ORIG_SYSTEM_REFERENCE,
R_ADD.COUNTRY,R_ADD.ADDRESS1,
R_ADD.ADDRESS2,R_ADD.ADDRESS3,
R_ADD.ADDRESS4,R_ADD.CITY,
R_ADD.POSTAL_CODE,R_ADD.STATE,
R_ADD.PROVINCE,
R_ADD.COUNTY,
R_ADD.ATTRIBUTE_CATEGORY,
R_ADD.ATTRIBUTE1,
R_ADD.ATTRIBUTE2,
R_ADD.ATTRIBUTE3,
R_ADD.ATTRIBUTE4,
R_ADD.ATTRIBUTE5,
R_ADD.ATTRIBUTE6,
R_ADD.ATTRIBUTE7,
R_ADD.ATTRIBUTE8,
R_ADD.ATTRIBUTE15,
R_CONTACT.CONTACT_KEY,
R_CONTACT.ORIG_SYSTEM_REFERENCE,
R_PHONES.ORIG_SYSTEM_REFERENCE,
R_PHONES.PHONE_TYPE,
R_PHONES.PHONE_NUMBER,
R_PHONES.AREA_CODE,
R_SITES.SITE_USE_CODE,
R_SITES.LOCATION,
R_PROFILES.PROFILE_CLASS_NAME,
R_PROFILES.COLLECTOR_NAME,
R_PROFILES.CREDIT_HOLD,
R_PROFILES.TAX_PRINTING_OPTION,
R_PROFILES.TOLERANCE,
R_PROFILES.ACCOUNT_STATUS,
R_PROFILES.RISK_CODE,
R_PROFILES.GROUPING_RULE_NAME,
R_PROFILES.STATEMENT_CYCLE_NAME,
R_PROFILES.DUNNING_LETTER_SET_NAME,
B_ACC_USES.START_DATE,
B_ACCOUNT.BANK_ACCOUNT_NAME,
B_ACCOUNT.CURRENCY_CODE,
B_ACCOUNT.BANK_ACCOUNT_NUM,
B_ACCOUNT.CURRENCY_CODE,
B_ACCOUNT.CONTACT_LAST_NAME,
B_ACCOUNT.CONTACT_FIRST_NAME,
B_BRANCH.BANK_NAME,
B_BRANCH.BANK_BRANCH_NAME
FROM
RA_CUSTOMERS R_CUST,
RA_ADDRESSES_ALL R_ADD,
RA_CONTACTS R_CONTACT,
RA_PHONES R_PHONES,
RA_SITE_USES_ALL R_SITES,
AR_CUSTOMER_PROFILES_V R_PROFILES,
AP_BANK_ACCOUNT_USES_ALL B_ACC_USES,
AP_BANK_ACCOUNTS_ALL B_ACCOUNT,
AP_BANK_BRANCHES B_BRANCH,
RA_CUSTOMER_TRX_ALL CUST_TRX
WHERE
R_CUST.CUSTOMER_ID=R_ADD.CUSTOMER_ID
AND R_ADD.CUSTOMER_ID=R_CONTACT.CUSTOMER_ID(+)
AND R_CUST.CUSTOMER_ID=R_CONTACT.CUSTOMER_ID
AND R_ADD.ADDRESS_ID =R_PHONES.ADDRESS_ID
AND R_ADD.ADDRESS_ID=R_SITES.ADDRESS_ID(+)
AND R_PROFILES.CUSTOMER_ID=R_ADD.CUSTOMER_ID
AND R_PROFILES.SITE_USE_ID=R_SITES.SITE_USE_ID
AND B_ACC_USES.CUSTOMER_ID(+)=R_CUST.CUSTOMER_ID
AND B_ACCOUNT.BANK_ACCOUNT_ID(+)=B_ACC_USES.BANK_ACCOUNT_USES_ID
AND B_BRANCH.BANK_BRANCH_ID(+)=B_ACCOUNT.BANK_BRANCH_ID
AND CUST_TRX.BILL_TO_CUSTOMER_ID=R_CUST.CUSTOMER_ID
AND R_CUST.STATUS='A'

Re: customer data retrieval [message #292769 is a reply to message #292765] Wed, 09 January 2008 16:50 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Maybe it's just me, but I have no idea what you are asking. Do you simply want the first 3000 rows of the result, or is your query returning more rows than it should?
Re: customer data retrieval [message #292853 is a reply to message #292765] Thu, 10 January 2008 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want less than 3000 rows add "and rownum < 3000".

Don't post in UPPER cases.
Also please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button to verify.

Regards
Michel
Re: customer data retrieval [message #292884 is a reply to message #292853] Thu, 10 January 2008 02:06 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Whatever the reason for the 3000 rows limit you want, I strongly recommend to start learning the datamodel of EBS (you can find that on Metalink).
No offense meant, but there are so many mistakes in your join conditions that I don't have the time to go trough all of them. Believe me: learn the datamodel or hire an EBS consultant to write this code for you.
Re: customer data retrieval [message #293018 is a reply to message #292765] Thu, 10 January 2008 10:51 Go to previous message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
I couldn't agree with skooman more. Oracle EBS is not a simple animal, especially with the lack of use of Foreign Keys.

You need to take time to understand the data model and how it is used within Oracle Apps.

Previous Topic: Array Need to catch if out of range
Next Topic: BULK COLLECT
Goto Forum:
  


Current Time: Thu Dec 08 12:44:40 CST 2016

Total time taken to generate the page: 0.14016 seconds