Oracle DB query formating problem

Question

Hi,

I have a problem with formating 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 formating?

Cheers, Petter

Answer 1

if t.clockindatetime is an oracle DATE then why not use:
TO_CHAR(t.clockindatetime, 'YYYYMMDD') for the date part and TO_CHAR(t.clockindatetime, 'HHMISS') for the time part (if you want the hours to be in 24hr format use TO_CHAR(t.clockindatetime, 'HH24MISS') (the hours will still only take up 2 characters))

Answer 2

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

Answer 3

Super sweet, you guys saved my weekend! :)

Query:

SELECT lpad('1',5) || 'IN' ||
    TO_CHAR(t.clockindatetime, 'YYYYMMDD') ||
    lpad(i.employeeid,18) ||
    TO_CHAR(t.clockindatetime, 'HH24MISS') ||
    '00        1' ||
    lpad('h.useralpha6',5)
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);

Result:

    1IN20081106          1234123412101500        1   64                                                                                     
    1IN20081106              234512385100        1   64                                                                                     
    1IN20081107              234515261900        1   64

I still have to figure out how the time_card_detail or hr_employee table is related to the "Company number" i use for column 1 and how to lock the timecardpost for tampering but that can wait til monday.

Big thanks! / Petter