印象 ● sql函数

with as:

例：

insert into PHA_CODESET.HCPCS(           --插入表
SID,
CD,
BASEDT,
DESCRIPTION,
DESCRIPTIONSHORT,
VERSION,
EFFECTIVEDT
)
With a as (select *, row_number() over (partition by cd order by version desc) as rn from PHA_CODESET._RAWHCPCS)            --with as: 使用a作为一个公用表达式，该表达式在使用上与表变量类似。
select row_number () over (order by a.cd) + (select coalesce(max(SId), 0) from PHA_CODESET.HCPCS)                                                   CTE后面直接跟SQL语句（select, insert, update），否则失效。
,trim(a.cd)
,TO_DATE(substring(a.version,3,4), 'YYYY')
,trim(a.LongDescription)
,trim(a.ShortDescription)
,trim(a.version)
,TO_DATE(substring(a.version,3,4), 'YYYY')
from a
where a.rn = 1
order by a.CD;

 

Right:

right('00000000'||w.Search_DOB,8):表示right字段左边补8个0.

Lpad:

 

select ipa,lpad(to_char(rn),13,'0'),'Contracted Group Name','cxcombinedclaimvw'
from (select row_number() over(order by ipa) rn from ....表示：rn 字段通过排序后，通过lpad函数加上几个0，组成13个字符型的字符串。