Solved

Space Delimited Sql File

Posted on 2016-08-31
4
70 Views
Last Modified: 2016-09-15
A vendor has asked me to create an extract file.
In this extract file, certain fields need to be in a particular position.
For example:
Employee Position 1-8
Employee First Name Position 9-26
Employee Middle Name Position 27-27
Employee Last Name Position 28-42

I was thinking about doing this in oracle sql, anyone have any suggestions on how to do this?
0
Comment
Question by:metalteck
  • 2
  • 2
4 Comments
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 500 total points
ID: 41778345
This is quite simple in Oracle SQL.  Simply concatenate the four column values together into one result column, and use "to_char" on the first column (assuming that is a numeric column) and use either "rpad" or "substr" (or maybe both) on the first and last name columns, plus possibly an "nvl" on the middle name column in case that is null (blank).

Something like this should work (assuming the actual column names are:
employee_id
first_name
middle_name
last_name)

select to_char(employee_id,'99999999')
||rpad(substr(first_name,1,17),17,' ')
||nvl(middle_name,' ')
||rpad(substr(last_name,1,15),15,' ') "Output"
from [your_table]
where ...
order by  ...

(The "where..." and "order by ..." clauses are optional.)

I think the lengths for first_name and last_name are correct.  But, you will have to test them.

If you use SQL*Plus to run this, you will want to use these commands before running the query:
set pagesize 0;
set linesize 80;
set trimspool off;
set head off;
set feedback off;
spool C:\temp\my_file.txt
 [your SQL query here]
spool off;

The "spool" command will open an output file, so change "\temp" to a valid directory on your system if necessary, and change "my_file.txt" to whatever you want the output file to be named.
1
 

Author Comment

by:metalteck
ID: 41791585
This is working for me. What is the code I would need to use for a date?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 41791658
That depends on what format you want the "date" column value displayed in, and whether you want to include the time-of-day, or not.  By default, Oracle "date" columns include the time-of-day, as well as the date.  But, depending on the application, the time-of-day may or may not be included.  If it is not included by your application, that is the same as an actual time of exactly midnight.

For a date column, you would want to use the Oracle "to_char" operator and a format mask so Oracle puts the "date" value into characters as your vendor wants them to be displayed.

For example, if your "date" column is named "my_date" and the actual "date" value is Sept 9, 2016 1:13:45pm, you could any of these in your query, or something similar:

Query expression                                                 Result
--------------------------------------------------------------    --------------
to_char(my_date,'MM/DD/YYYY')                        09/09/2016
to_char(my_date,'YYYYMMDD')                           20160909
to_char(my_date,'Mon. Dy, YYYY')                       Sep. 9, 2016
to_char(my_date,'MM/DD/YYYY HH24:MI:SS')   09/09/2016 13:13:45

Note, there are dozens of other possible combinations or variations for format elements for "date" values in Oracle queries when you use "the "to_char" operator.  Check the Oracle documentation for "to_char" or "format mask" for a complete list.

You can concatenate this "to_char" column result just like you can with the other datatypes, something like this:
select to_char(employee_id,'99999999')
 ||rpad(substr(first_name,1,17),17,' ')
 ||nvl(middle_name,' ')
 ||rpad(substr(last_name,1,15),15,' ')
 ||to_char(my_date,'MM/DD/YYYY') "Output"
 from [your_table]
0
 

Author Comment

by:metalteck
ID: 41791738
Ok, I will try that.
And what is the syntax for blank spaces?
My extract has some fields that will be blank, and at various lengths.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now