Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

formats, right left justify, help!! sql plus

651180Apr 15 2009 — edited Apr 15 2009
I have this code, but the file is one position off,
for example school year should start in position one and end in position one
then
student_last_name format a16 size 1
should start in position 2 and end in position 17 size 16
and
student_first_name format a16
should start in position 18 end in 33 size 16
etc... etc...
also last name and fisrt name should be left justify
I change RPAD to lpad but it looks wear, is that right?
Please help my boss is waiting for the file 
set verify off
set echo off
----set pagesize 9999
set wrap off
set heading off
set trimspool on
set linesize 9999
column    school_year             format a1;
column    student_last_name        format a16 ;
column    student_first_name       format a16 ;
column    student_mi               format a1;
column    street1                  format a50 ;
column    street2                  format a50 ;
column    city                     format a28 ;
column    state                    format a2;
column    zipcode                  format a9;
column    telnumber                format a10;
column    dateofbirth              format a8;
column    ssn                      format a9;
column    dep_status               format a1;
column    natural_parents          format a1;
column    parent_own_business      format a1;
column    student_finaid_status    format a1;
column    federal_verification     format a1;
column    college_code             format a4;
column    foreign_addr_ind         format a1;
column    country                  format a25;
column    foreign_postal_code      format a15;
column    student_email_address    format a50 ;
column    alternate_id             format a12;
column    parent_email             format a256;
column    filler                   format a279;
column    slash                    format a1;
column    cr/lf                    format a2;
spool finaid2.lst
select
--RPAD(NVL(RZFIDOC_SCHOOL_YEAR,' '),1), 
RPAD(NVL('0',' '),1),
rpad(nvl(RZFIDOC_LAST_NAME,' '),16),
rpad(nvl(RZFIDOC_FIRST_NAME,' '),16),
rpad(nvl(RZFIDOC_MI,' '),1),
rpad(nvl(RZFIDOC_STREET_LINE1,' '),50),
rpad(NVL(RZFIDOC_STREET_LINE2,' '),50),
RPAD(nvl(RZFIDOC_CITY,' '),28),
rpad(nvl(RZFIDOC_STATE_CODE,' '),2),
rpad(nvl(RZFIDOC_ZIP,' '),9),
rpad(nvl(RZFIDOC_TELPHONE,' '),10),
rpad(nvl(to_char(RZFIDOC_BIRTH_DATE,'MMDDYYYY'),' '),8),
rpad(nvl(RZFIDOC_SSN,' '),9),
rpad(nvl(RZFIDOC_DEP_STATUS,' '),1),
rpad(nvl(RZFIDOC_NAT_PAR_MRTL_STATUS,' '),1),
rpad(nvl(RZFIDOC_OWN_BUSINESS,' '),1),
rpad(nvl(RZFIDOC_FINAID_STATUS,' '),1),
rpad(nvl(RZFIDOC_FED_VERIF,' '),2),
rpad(nvl(RZFIDOC_COLLEGE_CODE,' '),4),
rpad(nvl(RZFIDOC_FOREIGN_ADDR_IND,' '),1),
rpad(nvl(RZFIDOC_COUNTRY,' '),25),
rpad(nvl(RZFIDOC_FOR_POSTCODE,' '),15),
rpad(nvl(RZFIDOC_EMAIL,' '),50),
rpad(nvl(RZFIDOC_ID,' '),12),
rpad(nvl(RZFIDOC_PAR_EMAIL,' '),256),
rpad(nvl(RZFIDOC_PAR_EMAIL,' '),279),
'\',
CHR(13)||CHR(10)   
from 
faismgr_midd.RZFIDOC

Comments

624104
You have got to be kidding me, he's kidding right? Come on man... sample data or something I mean jeez all day with this


Ok maybe a bit harsh... but we really need to see some sample data and desired output to help you faster to get your file to your boss...

TimS

Edited by: TimS on Apr 15, 2009 3:36 PM
APC
Bear in mind that the COLUMN commands only apply to columns in the SELECT statement which match, Because you're applying format masks to the columns they have new column names and so don't match. This means you'll need to add aliases to everything in your query... 
select
    RPAD(NVL(RZFIDOC_SCHOOL_YEAR,' '),1) as school_year,
    ....
Alternatively, extend those COLUMN columns to include JUSTIFY LEFT, JUSTIFY CENTER or JUSTIFY RIGHT instead of padding.

Cheers, APC

blog: http://radiofreetooting.blogspot.com
6363
Please help my boss is waiting for the file
Seems to be a lot of urgent help needed for output from tables based on student, assignment, exam scores and lecturer data right now.

Are you sure you mean boss and not teacher?
651180
You don't have to make fun of me sr, maybe you are a genious
I am trying to create a file like this one, but I need to put the file in the right positions
like 0 start in position one and end in pos1...
if you see the preivious email you will know what I mean...
Please don't answer, but just don't make fun..ok

0Kinder John 2231 Delamere Drive Cleveland OH44106 112419863028497742 3 3526 jkinder@connelite.edu 00256696 \
APC
I don't know why you replied to my post as I'm the only person who has offered you helpful advice.

Regards, APC

blog: http://radiofreetooting.blogspot.com
624104
Heh poor APC caught in the crossfire... maybe it was to me, sorry if you took offense. I see a LOT of people come in here just throwing queries out and expecting us to know what the heck they want from it.

Either way read APC's example that is the most obvious problem, try that and show us your output and we'll go from there.

TimS
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 13 2009
Added on Apr 15 2009
6 comments
3,099 views