通过SQL查询生成固定宽度的平面格式输出文件 [英] Produce a fixed-width flat format output file from a SQL query
问题描述
我需要帮助生成固定宽度的文件，其中每一行的长度均相同.我知道我必须将每个字段都强制转换为固定长度的字符字段.字段的大小应为源表中字段的最大大小.但是，使用以下查询无法生成文件-
I need help producing fixed width file where every row is the same length. I know I have to cast each field as a fixed length character field. The size of the field should be the maximum size of the field in the source table.However, just could not produce the file using below query -
Select cast(x.account_id as char(10)) ||
cast(birth_month as char(2)) ||
cast(birth_year as char(4))||
TO_CHAR(LastVisittDate,'yyyymmdd') ||' '||
max(case when email_Rank = 1 then cast(email_address as char(100)) else null end) ||chr(13) as ConstitRow
from INTERS XR
inner join INTERS_REL RX on XR.account_id = RX.account_id and RX.sts <> 'D'
where
RX.account_id in (deleted - long list of account IDs)
推荐答案
您正在将列值转换为char(n)，这会将较短的字符串和数字(隐式转换为字符串)填充为 n 字符，并截断更长的值. (这比使用varchar2(n)更好，因为使用varchar2(n)会出现数字较长的错误，并且对较短的字符串没有任何影响.)
You are casting your column values to char(n), which will pad shorter strings and numbers (implicitly converted to strings) to n chars, and truncate longer values. (This is better than using varchar2(n), which would error with longer numbers and wouldn't make any difference to shorter strings).
不过，您会遇到null问题，因为cast(null as char(n))-或其他任何内容-仍然为null，而不是您期望的 n 空间.这对于您的任何列都可能是个问题，但对于您的case表达式尤其如此.
You will have a problem with nulls though, as cast(null as char(n)) - or anything else - is still null, rather than n spaces as you might expect. That may be a problem for any of your columns, but particularly for your case expressions.
如果任何列可以为空，则可以使用nvl或coalesce将其视为单个空格，然后强制转换也将其填充:
If any columns can be null you can use nvl or coalesce to treat them as a single space instead, and the cast will then pad those too:
cast(coalesce(First_name, ' ') as char(20))
除了强制转换之外，您还可以使用rpad():
Rather than casting, you could also use rpad():
rpad(coalesce(First_name, ' '), 20, ' ')
对于case表达式，您可以使else子句的值计算为单个空格而不是null，但是您还需要将强制转换应用于整个case表达式，而不是在一个when分支中使用它.所以代替这个:
For the case expressions you can make the else clause evaluate to a single space instead of null, but you also need to apply the cast to the overall case expression, not have it within one when branch; so instead of this:
max(case when email_Rank = 1 then cast(email_address as char(100)) else null end)
您会这样做:
cast(max(case when email_Rank = 1 then email_address else ' ' end) as char(100))
或者，如果您愿意:
cast(coalesce(max(case when email_Rank = 1 then email_address end), ' ') as char(100))
您的客户端可能无论如何都将整个字符串右填充到相同的长度(如果您具有set trimout off或假脱机了set trimspool off，SQL * Plus会这样做；这可能是BobC所指的)，但是，如果您真正想创建的是固定长度的 fields ，这并不会真正帮到您，这会累计为您提供固定长度的记录-如果您没有固定长度的字段无论如何都无法解释数据.
Your client may have been right-padding the overall string to the same length anyway (SQL*Plus will do that if you have set trimout off, or if spooling set trimspool off; which might be what BobC was referring to), but that doesn't really help if what you're really trying to create is fixed length fields, which cumulatively would give you a fixed length record as well - and if you didn't have fixed length fields it would impossible to interpret the data anyway.
这篇关于通过SQL查询生成固定宽度的平面格式输出文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
