Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Carriage return or line feed issues
Top Forums Programming Carriage return or line feed issues Post 302366868 by sherrod6970 on Friday 30th of October 2009 03:02:47 PM
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;


 

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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
All times are GMT -4. The time now is 02:57 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy