Oracle DB query formatting

Question

I have a problem with formatting the data when doing an query to an Oracle database.

What I want to do is to export some data into the formatbelow into a textfile;

    1IN20071001      40005601054910101200            1   65

• First number (1 above) = Company number (position 1-5, blanks infront)
• IN or UT = IN for clockin and UT for clockout (position 6-7)
• 20071001 = Date(business date) in the format YYYYMMDD (pos 8-15)
• 400056010549 = EmployeeID (pos 16-33, right alignment, blanks infront)
• 101012 = Time in format TTMMSS (pos 34-39)
• 00 = FT, always 00 (pos 40-41)
• Blanks = Always 8 empty spaces (pos 42-49)
• 1 = Not sure what this is used for, but it should always be 1 (pos 50, right alignment, blanks infront)
• 65 = “Kostnadsställe”, ENT_HR_EMPLOYEE.USERALPHA6 (pos 51-55, right alignment, blanks infront)

Currently I'm using the query below, but this is where my SQL knowledge ends...

COLUMN one FORMAT a5 HEADER  JUSTIFY RIGHT 
COLUMN two FORMAT a8 HEADER two 
COLUMN three FORMAT a18 HEADER three JUSTIFY RIGHT
COLUMN four FORMAT a5 HEADER three JUSTIFY RIGHT

SELECT h.fkod AS one, 'IN',
    SUBSTR(t.clockindatetime,0,4) ||
    SUBSTR(t.clockindatetime,6,2) ||
    SUBSTR(t.clockindatetime,9,2) AS two,
    i.employeeid AS three
    SUBSTR(t.clockindatetime,11,6) || '00        1',  
    h.fkod AS four
FROM ent_time_card_detail t,
    max_employeeid_history i,
    ent_hr_employee h
WHERE h.enthremployeeid = t.enthremployeeid
AND h.payrollid = i.userid
AND t.clockindatetime >= i.from_date
AND (t.clockindatetime < i.to_date OR i.to_date IS NULL);

Any SQL-pro's out there that can help me finish the formatting?

Answer 1

Also you can use rpad and lpad to both pad your output and truncate extra chars