Solved

Space Delimited Sql File

Posted on 2016-08-31
4
99 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 35

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 35

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

734 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