Home » SQL & PL/SQL » SQL & PL/SQL » FORMAT DATA IN EXCELSHEET (ORACLE9I)
FORMAT DATA IN EXCELSHEET [message #295938] Wed, 23 January 2008 23:41 Go to next message
mehulmb
Messages: 25
Registered: May 2006
Location: Pune
Junior Member

HI FRIENDS,


I AM WORKING ON AR MOUDLE IN ORACLE APPS.I AM FACING ONE PROBLEM REGARDING EXCEL OUTPUT. WHENEVER I RUN MY QUERY IN TOAD THEN I WILL SAVE OUTPUT IN
EXCEL SHEET(.XLS FORMAT).iT SHOW ING DATA COMMA SEPARTED.LIKE THIS

CUST_NO CUST_NAME TRX_NUMBER TRX_DATE GL_DATE LOCAITON AMOUNT_ORIGINAL AMOUNT_BAL AMOUNT_APPLIED OPEN_BAL
------- --------- --------- -------- ------- --------------- ------------ -------------- -------------- ---------
120 XYZ 1001 12/9/07 12/9/07 LONDON 10,000 10,000 0 200000



I WANT OUTPUT LIKE THIS IN EXCEL SHEET.


CUST_NO:120

CUST_NAME:XYZ

LOCATION:LONDON

OPENING_BAL: ON 3-OCT-2007 200000



TRX_NUMBER TRX_DATE GL_DATE LOCAITON AMOUNT_ORIGINAL AMOUNT_BAL AMOUNT_APPLIED
--------- -------- ------- --------------- ------------ -------------- --------------
1001 12/9/07 12/9/07 LONDON 10,000 10,000 0


MY CODE IS LIKE THIS :
SELECT
HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
,RCT.TRX_NUMBER TRX_NUMBER
,HP.PARTY_NAME PARTY_NAME
,DECODE(RCT.STATUS_TRX,'OP','OPEN','CLOSED') STATUS
,HL.ADDRESS1 || HL.ADDRESS2 || HL.ADDRESS3|| HL.PROVINCE || HL.CITY || HL.STATE ||HL.POSTAL_CODE || HL.COUNTRY ADDRESS
,HCSU.LOCATION LOCATION
,RCT.TRX_DATE TRX_DATE
,APS.DUE_DATE DUE_DATE
,RCTG.GL_DATE GL_DATE
,APS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL
,APS.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING
,APS.AMOUNT_APPLIED AMOUNT_APPLIED
,DECODE(RCTT.TYPE,'DM','DEBIT MEMO','CM','CREDIT MEMO','DEP','DEPOSIT','GUAR','GURANTEE','INVOICE') TYPE
,RCTT.STATUS STATUS1
,RBS.NAME NAME
,RCT.CT_REFERENCE CT_REFERENCE
,F1(HCA.ACCOUNT_NUMBER,:GL1) OPENING_BALANCE
,F2(HCA.ACCOUNT_NUMBER,RCT.TRX_NUMBER,:GL1,:GL2) CURRENT_BAL
,F1(HCA.ACCOUNT_NUMBER,:GL1) + F2(HCA.ACCOUNT_NUMBER,RCT.TRX_NUMBER,:GL1,:GL2) CLOSED_BAL
,GCC.SEGMENT1 BC
FROM
RA_CUSTOMER_TRX_ALL RCT
,AR_PAYMENT_SCHEDULES_ALL APS
,RA_CUST_TRX_TYPES_ALL RCTT
,RA_CUST_TRX_LINE_GL_DIST_ALL RCTG
,RA_BATCH_SOURCES_ALL RBS
,HZ_PARTY_SITES HPS
,HZ_PARTIES HP
,HZ_CUST_ACCOUNTS HCA
,HZ_CUST_SITE_USES_ALL HCSU
,HZ_CUST_ACCT_SITES_ALL HCAS
,HZ_LOCATIONS HL
,GL_CODE_COMBINATIONS GCC
WHERE
RCT.CUSTOMER_TRX_ID=APS.CUSTOMER_TRX_ID
AND RCTT.CUST_TRX_TYPE_ID=RCT.CUST_TRX_TYPE_ID
AND RCTG.CUSTOMER_TRX_ID=RCT.CUSTOMER_TRX_ID
AND RCT.BATCH_SOURCE_ID=RBS.BATCH_SOURCE_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HP.PARTY_ID=HPS.PARTY_ID
AND HL.LOCATION_ID = HPS.LOCATION_ID
AND HCA.CUST_ACCOUNT_ID =HCAS.CUST_ACCOUNT_ID
AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
AND HPS.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
AND RCT.BILL_TO_SITE_USE_ID = HCSU.SITE_USE_ID
AND GCC.CODE_COMBINATION_ID = RCTG.CODE_COMBINATION_ID
AND HCSU.SITE_USE_CODE = 'BILL_TO'
AND RCTG.GL_DATE BETWEEN :GL1 AND :GL2
GROUP BY
HCA.ACCOUNT_NUMBER
,RCT.TRX_NUMBER
,HP.PARTY_NAME
,DECODE(RCT.STATUS_TRX,'OP','OPEN','CLOSED')
,HL.ADDRESS1 || HL.ADDRESS2 || HL.ADDRESS3|| HL.PROVINCE || HL.CITY || HL.STATE ||HL.POSTAL_CODE || HL.COUNTRY
,HCSU.LOCATION
,RCT.TRX_DATE
,APS.DUE_DATE
,RCTG.GL_DATE
,APS.AMOUNT_DUE_ORIGINAL
,APS.AMOUNT_DUE_REMAINING
,APS.AMOUNT_APPLIED
,DECODE(RCTT.TYPE,'DM','DEBIT MEMO','CM','CREDIT MEMO','DEP','DEPOSIT','GUAR','GURANTEE','INVOICE')
,RCTT.STATUS
,RBS.NAME
,RCT.CT_REFERENCE
,GCC.SEGMENT1




I HAVE ATTACHING MY QUERY.PLEASE GIVE ME SOLUTION FOR THIS.

THANKS IN ADVANCE
Re: FORMAT DATA IN EXCELSHEET [message #295939 is a reply to message #295938] Wed, 23 January 2008 23:42 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
It appears your CAP LOCK key is broken.
It needs to be fixed.
Re: FORMAT DATA IN EXCELSHEET [message #295941 is a reply to message #295939] Wed, 23 January 2008 23:45 Go to previous messageGo to next message
mehulmb
Messages: 25
Registered: May 2006
Location: Pune
Junior Member

VERY FUNNY.THX FOR THE REPLY
Re: FORMAT DATA IN EXCELSHEET [message #295947 is a reply to message #295938] Thu, 24 January 2008 00:07 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

It is not Funny ,

Writing is CAPITAL letter is assumed to be "RUDE" and "IMPOLITE" .

Thumbs Up
Rajuvan
Re: FORMAT DATA IN EXCELSHEET [message #295955 is a reply to message #295941] Thu, 24 January 2008 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, BEFORE posting you have to read OraFAQ Forum Guide and follow the rules, not only on CAPITALS but also on formatting.

Regards
Michel
Re: FORMAT DATA IN EXCELSHEET [message #295988 is a reply to message #295938] Thu, 24 January 2008 02:07 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
In addition, use Excel's transpose capabilities to perform the task that you need.
Previous Topic: POST with redirect (utl_http)
Next Topic: Rows deleted automatically
Goto Forum:
  


Current Time: Wed Dec 07 18:36:37 CST 2016

Total time taken to generate the page: 0.16108 seconds