Sponsored Content
Top Forums Shell Programming and Scripting Fixed Length file from a SQL script Post 302909734 by ebsus on Friday 18th of July 2014 05:00:34 PM
Old 07-18-2014
Fixed Length file from a SQL script

Hi,

I have a DB2 UDB 9.7 SQL script, as follows:
I need to pass the script into Unix and generate a fixed length file from this.

Can someone kindly provide a script to achieve it?

Code:
SELECT 
CAST(COALESCE(CL_ID,'000000000') AS CHAR(9)) AS CL_ID
,STATUS
,CAST(COALESCE(PLACEMENT_CATEGORY,'') AS CHAR(3)) AS PLACEMENT_CATEGORY
,CAST(COALESCE(DOB_DT,'') AS CHAR(8)) AS DOB_DT
,CAST(COALESCE(LDSS,'') AS CHAR(3)) AS LDSS
,CAST(COALESCE(FIRST_NM,'') AS CHAR(20)) AS FIRST_NM
,CAST(COALESCE(LAST_NM,'') AS CHAR(20)) AS LAST_NM
,CAST(COALESCE(MIDDLE_INITIAL,'') AS CHAR(1)) AS MIDDLE_INITIAL
,CAST(COALESCE(STREET_NUMBER,'') AS CHAR(5)) AS STREET_NUMBER
,CAST(COALESCE(Street_nm,'') AS CHAR(20)) AS STREET_NM
,CAST(COALESCE(additional_Street_,'') AS CHAR(20)) AS additional_Street_Nm
,CAST(COALESCE(CITY_NM,'') AS CHAR(15)) AS CITY_NM
,CAST(COALESCE(STATE_CD,'') AS CHAR(2)) AS STATE_CD
,CAST(COALESCE(PA_ADR_ZIP5_NO,'') AS CHAR(5)) AS PA_ADR_ZIP5_NO
,CAST(COALESCE(SSN_NO,'000000000') AS CHAR(9)) AS SSN_NO
,CAST(COALESCE(school_name,'') AS CHAR(10)) AS school_name
FROM SL_JUN2014

 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

creating a fixed length output from a variable length input

Is there a command that sets a variable length? I have a input of a variable length field but my output for that field needs to be set to 32 char. Is there such a command? I am on a sun box running ksh Thanks (2 Replies)
Discussion started by: r1500
2 Replies

2. Shell Programming and Scripting

use SQL loader to dump a fixed length file in to DB

consider a fixed length file 12345abcd8901 12345abcd7777 12345njdu8888 12345hdku8388 i would like to dump it in to oracle DB using sql loader 12345 in to first coloumn abcd in to second coloumn 8901 in to 3rd coloumn (2 Replies)
Discussion started by: anumkoshy
2 Replies

3. UNIX for Dummies Questions & Answers

What the command to find out the record length of a fixed length file?

I want to find out the record length of a fixed length file? I forgot the command. Any body know? (9 Replies)
Discussion started by: tranq01
9 Replies

4. UNIX for Dummies Questions & Answers

Convert a tab delimited/variable length file to fixed length file

Hi, all. I need to convert a file tab delimited/variable length file in AIX to a fixed lenght file delimited by spaces. This is the input file: 10200002<tab>US$ COM<tab>16/12/2008<tab>2,3775<tab>2,3783 19300978<tab>EURO<tab>16/12/2008<tab>3,28523<tab>3,28657 And this is the expected... (2 Replies)
Discussion started by: Everton_Silveir
2 Replies

5. Shell Programming and Scripting

Need awk script to compare 2 fields in fixed length file.

Need a script that manipulates a fixed length file that will compare 2 fields in that file and if they are equal write that line to a new file. i.e. If fields 87-93 = fields 119-125, then write the entire line to a new file. Do this for every line in the file. After we get only the fields... (1 Reply)
Discussion started by: Muga801
1 Replies

6. Shell Programming and Scripting

Using cut command in a fixed length file

Hi, I have a file which have set of rows and has to create separate files based on the id. Eg: 001_AHaris020 001_ATony030 002_AChris090 002_ASmit060 003_AJhon001 Output: I want three files like 001_A.txt, 002_A.txt and 003_A.txt. 001_A.txt should have ... (4 Replies)
Discussion started by: techmoris
4 Replies

7. Shell Programming and Scripting

Concatenating fixed length lines in shell script

I have a peculiar file with record format like given below. Each line is wrapped to next lines after certain number of characters. I want to concatenate all wrapped lines into 1. Input:(wrapped after 10 columns) This is li ne1 This is li ne2 and this line is too lo ng Shortline ... (8 Replies)
Discussion started by: kmanyam
8 Replies

8. UNIX for Dummies Questions & Answers

Length of a fixed width file

I have a fixed width file of length 53. when is try to get the lengh of the record of that file i get 2 different answers. awk '{print length;exit}' <File_name> The above code gives me length 50. wc -L <File_name> The above code gives me length 53. Please clarify on... (2 Replies)
Discussion started by: Amrutha24
2 Replies

9. Shell Programming and Scripting

Replace and Increment a value in the fixed length file

Hi Everyone, I need to increment a value in the fixed length file. The file has almost a million rows. Is there any easy way to accomplish this. Ex input file ASDSD ADSD 00000 X AAASD ADSD 00000 X SDDDD ADSD 00000 X Ouput ASDSD ADSD 00001 X AAASD ADSD 00002 X SDDDD ADSD 00003 X ... (7 Replies)
Discussion started by: saratha14
7 Replies

10. Shell Programming and Scripting

Fixed length to delimited file conversion

Hi All, I need to convert a fixed length file to a delimited file with , (comma). But not all columns, some of the columns in the fixed files are used as fillers and I do not need that in the output file. test_fixed_len.txt I 0515 MR 394 I 0618 MR & MRS 942 I 0618 MR & MRS... (7 Replies)
Discussion started by: member2014
7 Replies
TR(1)									FSF								     TR(1)

NAME
tr - translate or delete characters SYNOPSIS
tr [OPTION]... SET1 [SET2] DESCRIPTION
Translate, squeeze, and/or delete characters from standard input, writing to standard output. -c, --complement first complement SET1 -d, --delete delete characters in SET1, do not translate -s, --squeeze-repeats replace each input sequence of a repeated character that is listed in SET1 with a single occurrence of that character -t, --truncate-set1 first truncate SET1 to length of SET2 --help display this help and exit --version output version information and exit SETs are specified as strings of characters. Most represent themselves. Interpreted sequences are: NNN character with octal value NNN (1 to 3 octal digits) \ backslash a audible BEL  backspace f form feed new line return horizontal tab v vertical tab CHAR1-CHAR2 all characters from CHAR1 to CHAR2 in ascending order [CHAR*] in SET2, copies of CHAR until length of SET1 [CHAR*REPEAT] REPEAT copies of CHAR, REPEAT octal if starting with 0 [:alnum:] all letters and digits [:alpha:] all letters [:blank:] all horizontal whitespace [:cntrl:] all control characters [:digit:] all digits [:graph:] all printable characters, not including space [:lower:] all lower case letters [:print:] all printable characters, including space [:punct:] all punctuation characters [:space:] all horizontal or vertical whitespace [:upper:] all upper case letters [:xdigit:] all hexadecimal digits [=CHAR=] all characters which are equivalent to CHAR Translation occurs if -d is not given and both SET1 and SET2 appear. -t may be used only when translating. SET2 is extended to length of SET1 by repeating its last character as necessary. Excess characters of SET2 are ignored. Only [:lower:] and [:upper:] are guaranteed to expand in ascending order; used in SET2 while translating, they may only be used in pairs to specify case conversion. -s uses SET1 if not translating nor deleting; else squeezing uses SET2 and occurs after translation or deletion. AUTHOR
Written by Jim Meyering. REPORTING BUGS
Report bugs to <bug-coreutils@gnu.org>. COPYRIGHT
Copyright (C) 2002 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICU- LAR PURPOSE. SEE ALSO
The full documentation for tr is maintained as a Texinfo manual. If the info and tr programs are properly installed at your site, the com- mand info tr should give you access to the complete manual. tr (coreutils) 4.5.3 February 2003 TR(1)
All times are GMT -4. The time now is 06:54 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy