Login or Register to Ask a Question and Join Our Community


Programming

Carriage return or line feed issues

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming Carriage return or line feed issues
# 1  
Old 10-30-2009
Carriage return or line feed issues
I keep running into the same problem with the following script. Every time it prints the carrage (line feed) char when I test. I believe that the issue is in the group by but I do not see it. The code is as follows.

Code:
SET FEED OFF
SET ECHO OFF
SET HEADING OFF
SET LINESIZE 1000
SET PAGESIZE 0
SET VERIFY OFF
SET TERM OFF
SET TRIMSPOOL ON
WHENEVER SQLERROR EXIT SQL.SQLCODE
SPOOL &&3;
column period_hierarchy_id_start noprint new_value rptPeriodHierarchyStart
column period_hierarchy_id_end noprint new_value rptPeriodHierarchyEnd
column report_id noprint new_value rptId
-- Get Period Hierarchy Start / End
--SELECT period_hierarchy_id_start,
-- period_hierarchy_id_end,
-- report_id
--FROM EMR_REPORTS
--WHERE report_id = &&1;
 
-- Get the year we want to run for
select
(select period_hierarchy_id
from period_hierarchy a
where a.level_name ='Year'
and a.year=(selectyearfrom period_hierarchy
where period_hierarchy_id = z.period_hierarchy_id_start)) PERIOD_HIERARCHY_ID_START,
(select period_hierarchy_id
from period_hierarchy a
where a.level_name ='Year'
and a.year=(selectyearfrom period_hierarchy
where period_hierarchy_id = z.period_hierarchy_id_start)) PERIOD_HIERARCHY_ID_END,
z.report_id
from emr_reports z
where z.report_id =&&1;
-- Now Get Month Level Period Hierarchy Start
column period_hierarchy_id noprint new_value rptMthPeriodHierarchyStart
SELECT period_hierarchy_id
FROM period_hierarchy
WHERE hierarchy_date =(SELECT DECODE(level_name,'Month',hierarchy_date,'Quarter',ADD_MONTHS(hierarchy_date,-2),'Year',ADD_MONTHS(hierarchy_date,-11))
FROM period_hierarchy
WHERE period_hierarchy_id =&&rptPeriodHierarchyStart)
AND level_name ='Month';
-- Now Get Month Level Period Hierarchy End
column period_hierarchy_id noprint new_value rptMthPeriodHierarchyEnd
SELECT period_hierarchy_id
FROM period_hierarchy
WHERE hierarchy_date =(SELECT hierarchy_date
FROM period_hierarchy
WHERE period_hierarchy_id =&&rptPeriodHierarchyEnd)
AND level_name ='Month';
SPOOL OFF;
SPOOL &&2;
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET LINESIZE 1000
SET PAGESIZE 0
SETSPACE 0
SET NEWPAGE 0
SET TRIMSPOOL ON
SELECT'Federal Tax ID Number'||chr(9)||
'Merchant Name'||chr(9)||
'Street Address'||chr(9)||
'City'||chr(9)||
'State'||chr(9)||
'Zip'||chr(9)||
'Incorporation Status'||chr(9)||
'MCC Code'||chr(9)||
'MCC Desc'||chr(9)||
'1099able?'||chr(9)||
'Merchant Market Hierarchy ID'||chr(9)||
'Level Number'||chr(9)||
'Proprietor Name'||chr(9)||
'NAICS Code'||chr(9)||
'NAICS Description'||chr(9)||
'Quarter 1 Count'||chr(9)||
'Quarter 1 Amount'||chr(9)||
'Quarter 2 Count'||chr(9)||
'Quarter 2 Amount'||chr(9)||
'Quarter 3 Count'||chr(9)||
'Quarter 3 Amount'||chr(9)||
'Quarter 4 Count'||chr(9)||
'Quarter 4 Amount'||chr(9)||
'Total Transaction Count'||chr(9)||
'Total Transaction Amount'||chr(9)||
'Merchant Legal Name'||chr(9)||
'Phone Number'||chr(9)||
'DUNS Number'
FROM DUAL;
SELECT/*+ ordered full(summ) use_hash(summ,emr,mch) */
NVL(emr.cleansed_merchant_tax_id,NVL(emr.mc_tax_id,' ')) TAXID,
chr(9),
NVL(emr.cleansed_merchant_name,emr.mc_merchant_dba_name) MERNAME,
chr(9),
NVL(emr.cleansed_merchant_street_addr,emr.mc_address)||chr(9)||
NVL(emr.cleansed_city_name,emr.mc_city)||chr(9)||
SUBSTR(NVL(emr.cleansed_state_province_code,emr.mc_state),1,2)||chr(9)||
NVL(emr.cleansed_merchant_postal_code,emr.mc_zipcode)||chr(9)||
NVL(emr.mc_busstat,emr.db_busstat)STATUS,
chr(9),
mch.merchant_category_code MCCCODE,
chr(9),
mch.merchant_category_name MCCDESC,
chr(9),
mch.flag_1099 FLAG,
chr(9),
MAX(emr.new_loc_id) HIERARCHYID,
chr(9),
MAX(mc_level_num) LEVELNUM,
chr(9),
/*DECODE(MAX(emr.at_lastnam_fstnam), ', ', ' ',MAX(emr.at_lastnam_fstnam))|| chr(9)||*/
DECODE(MAX(emr.db_solename),', ',' ',MAX(emr.db_solename))|| chr(9)||
/*MAX(emr.at_naicscode1)|| chr(9)||*/
MAX(emr.db_naicscode1)|| chr(9)||
/*MAX(emr.at_naicsdesc1) STRING2,*/
MAX(emr.db_naicsdesc1) STRING2,
chr(9),
SUM(CASEWHEN summ.period_hierarchy_id BETWEEN&rptMthPeriodHierarchyStart + 0 AND&rptMthPeriodHierarchyStart + 2 THEN
ROUND(NVL(summ.net_purch_disb_count,0),0)ELSE 0 END) Q1C,
chr(9),
SUM(CASEWHEN summ.period_hierarchy_id BETWEEN&rptMthPeriodHierarchyStart + 0 AND&rptMthPeriodHierarchyStart + 2 THEN
ROUND(NVL(summ.net_purch_disb_amount,0),2)ELSE 0 END) Q1A,
chr(9),
SUM(CASEWHEN summ.period_hierarchy_id BETWEEN&rptMthPeriodHierarchyStart + 3 AND&rptMthPeriodHierarchyStart + 5 THEN
ROUND(NVL(summ.net_purch_disb_count,0),0)ELSE 0 END) Q2C,
chr(9),
SUM(CASEWHEN summ.period_hierarchy_id BETWEEN&rptMthPeriodHierarchyStart + 3 AND&rptMthPeriodHierarchyStart + 5 THEN
ROUND(NVL(summ.net_purch_disb_amount,0),2)ELSE 0 END) Q2A,
chr(9),
SUM(CASEWHEN summ.period_hierarchy_id BETWEEN&rptMthPeriodHierarchyStart + 6 AND&rptMthPeriodHierarchyStart + 8 THEN
ROUND(NVL(summ.net_purch_disb_count,0),0)ELSE 0 END) Q3C,
chr(9),
SUM(CASEWHEN summ.period_hierarchy_id BETWEEN&rptMthPeriodHierarchyStart + 6 AND&rptMthPeriodHierarchyStart + 8 THEN
ROUND(NVL(summ.net_purch_disb_amount,0),2)ELSE 0 END) Q3A,
chr(9),
SUM(CASEWHEN summ.period_hierarchy_id BETWEEN&rptMthPeriodHierarchyStart + 9 AND&rptMthPeriodHierarchyStart + 11 THEN
ROUND(NVL(summ.net_purch_disb_count,0),0)ELSE 0 END) Q4C,
chr(9),
SUM(CASEWHEN summ.period_hierarchy_id BETWEEN&rptMthPeriodHierarchyStart + 9 AND&rptMthPeriodHierarchyStart + 11 THEN
ROUND(NVL(summ.net_purch_disb_amount,0),2)ELSE 0 END) Q4A,
chr(9),
SUM(summ.net_purch_disb_count) TOTAL_CNT,
chr(9),
SUM(summ.net_purch_disb_amount) TOTAL_AMT,
chr(9),
MAX(NVL(emr.cleansed_legal_corporate_name,emr.legal_name)) LEGALNAME,
chr(9),
emr_common_utilities.emr_format_phone(MAX(NVL(emr.cleansed_country_code,emr.mc_country_code)),MAX(NVL(emr.cleansed_merchant_tel_nbr,emr.mc_phone))) PHONE,
chr(9),
MAX(NVL(emr.cleansed_dun_bradstreet_nbr,emr.db_duns_number)) DUNSNUM
FROM emr_report_org_xref rox,
emr_15_5_20_10 summ,
emr_location emr,
merchant_category_hierarchy mch
WHERE rox.report_id =&&rptId
AND rox.report_request_id > 1
AND summ.organization_id = rox.organization_id
AND summ.period_hierarchy_id BETWEEN&rptMthPeriodHierarchyStart AND&rptMthPeriodHierarchyEnd
AND summ.merchant_market_hierarchy_id = emr.old_loc_id
AND mch.merchant_category_hierarchy_id = summ.merchant_category_hierarchy_id
GROUPBY NVL(emr.cleansed_merchant_tax_id,NVL(emr.mc_tax_id,' ')),
NVL(emr.cleansed_merchant_name,emr.mc_merchant_dba_name),
NVL(emr.cleansed_merchant_street_addr,emr.mc_address),
NVL(emr.cleansed_city_name,emr.mc_city),
SUBSTR(NVL(emr.cleansed_state_province_code,emr.mc_state),1,2),
NVL(emr.cleansed_merchant_postal_code,emr.mc_zipcode),
NVL(emr.mc_busstat,emr.db_busstat),
mch.merchant_category_code,
mch.merchant_category_name,
mch.flag_1099,
NVL(emr.cleansed_legal_corporate_name,emr.legal_name),
NVL(emr.cleansed_dun_bradstreet_nbr,emr.db_duns_number)
ORDERBY 1,3;
SPOOL OFF;
EXIT;


# 2  
Old 10-30-2009
Hi.

It prints a carriage return where? Can you define your problem more specifically, and say why you think it happens in the group by clause?

Otherwise, plan B...

You've posted a lot of code and it would help if you could now post the DDL of the tables you've used:

i.e, the result of:
Code:
SELECT DBMS_METADATA.GET_DDL('TABLE', tabs.TABLE_NAME)
FROM USER_TABLES tabs
WHERE tabs.TABLE_NAME in ('period_hierarchy_id', '...', '...' );

so that I at least don't have to spend my weekend creating all this stuff! (ideally uploading an export (with or without data) would be perfect!)

Can you also say what values I should give to the && prompts that you are using?

And also anything else you think may be relevant!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

awk Command to add Carriage Return and Line Feed

Hello, Can someone please share a Simple AWK command to append Carriage Return & Line Feed to the end of the file, If the Carriage Return & Line Feed does not exist ! Thanks (16 Replies)
Discussion started by: rosebud123
16 Replies

2. Shell Programming and Scripting

Remove carriage return and append the next line

Hi All, My requirement is to remove the carriage return in from the lines which i am reading if the length is lesser than 1330 and append the next line with it. Below is the realistic example of file structure. Input file: Blah blah blah blah Blah blah blah blah Blah blah blah blah Blah... (16 Replies)
Discussion started by: mad man
16 Replies

3. Shell Programming and Scripting

Need Help to delete carriage return and new line in csv file

Hi All, I have a problem loading the data from a csv file As you see below in the Input ,For the Data starting with " there are 2 lines, which i want to make them into single without changing the format of that data. You can see the desired output below: While i try to open the csv file and... (4 Replies)
Discussion started by: mlavanya
4 Replies

4. Shell Programming and Scripting

line carriage return characters

Hi, I would like to insert the line carriage retrun characters on each line. (2 Replies)
Discussion started by: koti_rama
2 Replies

5. Shell Programming and Scripting

Bash - multiple line carriage return

Hello! I have one strange question - let's say I have a long, multiple-line string displayed on the terminal using echo, and I would like to make a carriage return to the beginning of this string, no to the beginning of the last line - is something like that possible? I would like to be able to... (1 Reply)
Discussion started by: xqwzts
1 Replies

6. Shell Programming and Scripting

Need a carriage return at end of each line

Hi All, I am reading two files and writing out the file name and count of lines in each file to an output file. My script looks like this: echo "input_file1.out;`wc -l < input_file1.out | sed 's/^]*\(.*\)]*$/\1/'` " > comp_file1.out echo "input_file2.out;`wc -l < input_file2.out | sed... (2 Replies)
Discussion started by: Hangman2
2 Replies

7. UNIX for Dummies Questions & Answers

To remove carriage return between the line

Hi, I have a situation where I need to remove the carriage return between the lines. For.eg. The input file: 1,ad,"adc sdfd",edf 2,asd,"def fde",asd The output file should be 1,ad,adc sdfd,edf 2,asd,def fde,asd Thanks Shash (5 Replies)
Discussion started by: shash
5 Replies

8. Shell Programming and Scripting

How to insert carriage return before line feed?

I am doing some edi where translations had to be run on unix. Generally when I run the translations on windows, the output file has both carriage returns and line feed where as when ran on unix will have only line feed. I need to insert carriage return before the line feed. Is there some tool... (2 Replies)
Discussion started by: huey ing
2 Replies

9. Shell Programming and Scripting

Removing Carriage Return and or line feed from a file

Hello I'm trying to write a shell script which can remove a carriage return and/or line feed from a file, so the resulting file all ends up on one line. So, I begin with a file like this text in file!<CR> line two!<CR> line three!<CR> END!<CR> And I want to end up with a file... (1 Reply)
Discussion started by: tbone231
1 Replies

10. Shell Programming and Scripting

carriage return/line feeds

Hello, I have a file that has got carriage returns in it and I want to take them out. Anyone know how I can do this in a ksh? thanks (4 Replies)
Discussion started by: pitstop
4 Replies
Login or Register to Ask a Question