	[x] Posted via EE Mobile
	Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.

Question

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

9.1

Replacing Carriage Return with a space WHERE APPLICABLE

Asked by dabdowb in Oracle ERP, Oracle 8.x

Tags: SQLPLUS, Oracle

This is driving me completely nuts!!! BUT, I am new to all of this, so that is part of the problem. I am attempting to have the output from the following code put into a flat text file that is either pipe delimited, or comma delimited WITH a quote field qualifier. The code itself works just fine, except when someone has placed carriage returns in the field called REM_TEXT. Whenever there is no carriage return in that field, all the data for that particular record is spreadout across one row of data, which is what I need, since the text file is then being imported into a database for analysis.

Unfortunately, the REM_TEXT field in the mother application is completely open for any format, so some users have hard carriage returns in this field so that it looks "pretty" on the screen when viewing it. Why do they try to make it pretty? Just kills me.

Anyway, I read in this forum where a TRANSLATE function could be used to identify the carriage returns and convert them to a horizontal tab, but when I tried that using the following syntax, it blew up all together saying not the right amount of arguments?

translate (REM_TEXT,chr(13),chr(9)) in place of just REM_TEXT

So now I am at your mercy and praying to the coding gods for help. HELP!?!?!?!?!

Thanks

View the Solution FREE for 30 Days

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:

           
           
             set echo off
set newpage 0
set space 0
set pagesize 0
set feedback off
set trimspool on
set heading off
set linesize 12000
 
 
SPOOL C:\Remarks.txt
 
select FCST_ID||'|'||trim(substr(fcst_id,1,18))||'|'||substr(fcst_id,19,8)||'|'||substr(fcst_id,27,10)||'|'||
ltrim(PROD_GRP_ID)||'|'||LVL_NBR||'|'||REM_VER_NBR||'|'||CRTD_DATE_TIME||'|'||MOD_DATE_TIME||'|'||
MOD_USR_INDX||'|'||REM_TEXT||'|'||REM_DESC
from LGTY_REMARK_ENTRY, SCP_FCST_ROOT, LGTY_REMARK
where LGTY_REMARK_ENTRY.REM_NBR = SCP_FCST_ROOT.REM_NBR and LGTY_REMARK_ENTRY.rem_nbr = LGTY_REMARK.rem_nbr;
 
 
SPOOL OFF
 
 
exit

           
         

20091118-EE-VQP-93 - Hierarchy / EE_QW_3_20080625

Hall of Fame

1. schwertner8,800
2. mrjoltcola7,320
3. pal2ie6,000
4. sdstuber5,975
5. Milleniuma...4,000
6. dvz3,850
7. markgeer2,850
8. rolutola2,750
9. mlmcc2,500
10. Dr_Billy2,000
10. tbsgadi2,000
10. billprew2,000
10. yuching2,000
10. Gertone2,000
10. Andytw2,000
16. nabeelmoidu1,500
16. Senz791,500
18. Henka1,400
19. sonicefu1,250
20. mass26121,000
20. grzessio1,000
20. egarrison1,000
23. mark_wills500
23. franckpac...500
25. gregclough480

1. schwertner16,800
2. sdstuber7,475
3. mrjoltcola7,320
4. pal2ie6,000
5. rolutola4,850
6. Milleniuma...4,000
7. dvz3,850
8. mass26123,750
9. Twisteddk3,260
10. markgeer3,250
11. mlmcc2,500
12. Dr_Billy2,000
12. ericpete2,000
12. tbsgadi2,000
12. billprew2,000
12. Akenathon2,000
12. Fayyaz2,000
12. oleggold2,000
12. yuching2,000
12. sathyagiri2,000
12. Gertone2,000
12. EFHC2,000
12. arrkerr1...2,000
12. Andytw2,000
25. angelIII1,875

Replacing Carriage Return with a space WHERE APPLICABLE

Asked by dabdowb in Oracle ERP, Oracle 8.x

Tags: SQLPLUS, Oracle

About this solution