Oracle数据库查询格式化

Question

当我对Oracle数据库进行查询时，我遇到了数据格式的问题。

我想要将一些数据导出至文本文件，并采用以下格式：

    1IN20071001      40005601054910101200            1   65

• 第一个数字（上面的1）= 公司编号（位置1-5，前面有空格）
• IN或UT = IN表示打卡，UT表示下班打卡（位置6-7）
• 20071001 = 日期（业务日期）格式为YYYYMMDD（位置8-15）
• 400056010549 = 员工ID（位置16-33，右对齐，前面有空格）
• 101012 = 时间格式为TTMMSS（位置34-39）
• 00 = FT，始终为00（位置40-41）
• 空格 = 始终为空的8个空格（位置42-49）
• 1 = 不确定这是用于什么，但它应该始终为1（位置50，右对齐，前面有空格）
• 65 = “Kostnadsställe”，ENT_HR_EMPLOYEE.USERALPHA6（位置51-55，右对齐，前面有空格）

目前我正在使用以下查询，但这是我的SQL知识的尽头...

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);

有没有 SQL 专家可以帮助我完成格式调整？

- Petter

Answer 1

如果 t.clockindatetime 是一个 Oracle DATE 类型，那么为什么不使用：
TO_CHAR(t.clockindatetime, 'YYYYMMDD') 来获取日期部分，以及 TO_CHAR(t.clockindatetime, 'HHMISS') 来获取时间部分（如果您想要 24 小时制的小时数，请使用 TO_CHAR(t.clockindatetime, 'HH24MISS') （小时数仍然只占用 2 个字符））

- hamishmcn

Answer 2

您还可以使用rpad和lpad来填充输出并截断额外的字符。

- hamishmcn

Answer 3

太棒了，你们救了我的周末！:)

查询：

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);

结果：

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

我仍然需要弄清楚time_card_detail或hr_employee表与我用于列1的“公司编号”的关系，以及如何锁定时间卡发布以防篡改，但这可以等到周一再处理。

非常感谢！ / Petter

- Petter