文本转换

oldknew

有这么段代码:
SELECT         SUBSTR(TO_CHAR(?),1,||                ||TO_CHAR(A.UPD_DATE,'hh24:mi:ss')        ||TO_CHAR(A.REC_UPD_DT,'YYYYMMDD')        ||TO_CHAR(A.REC_UPD_DT,'hh24miss')||(        SELECT COMPANY_CDE         AS RSLT FROM SAP_B_COMPANY_CARRIER WHERE CARRIER = A.CARRIER)        FROM SAP_AR_IAAJ A (        SELECT INV_ADJ_UUID,MAX(ZZEXTDT) AS ZZEXTDT_MAX        FROM SAP_AR_IAAJ WHERE ZZEXTDT>=? AND ZZEXTDT<=? AND COLL_OFF IN('VAN','MTR','TOR') AND ADJ_TYPE='RF' GROUP BY INV_ADJ_UUID) B WHERE A.INV_ADJ_UUID = B.INV_ADJ_UUID AND A.ZZEXTDT=B.ZZEXTDT_MAX

需要做如下处理:
1 在查询子句SELECT FROM中将AS RSLT去掉
2 仅需要在文本中出现的第一个FROM之前添加关键字AS RSLT(不包含括号中出现的FROM)

修改效果如下:
SELECT         SUBSTR(TO_CHAR(?),1,||                ||TO_CHAR(A.UPD_DATE,'hh24:mi:ss')        ||TO_CHAR(A.REC_UPD_DT,'YYYYMMDD')        ||TO_CHAR(A.REC_UPD_DT,'hh24miss')||(        SELECT COMPANY_CDE          FROM SAP_B_COMPANY_CARRIER WHERE CARRIER = A.CARRIER)        AS RSLT FROM SAP_AR_IAAJ A (        SELECT INV_ADJ_UUID,MAX(ZZEXTDT) AS ZZEXTDT_MAX        FROM SAP_AR_IAAJ WHERE ZZEXTDT>=? AND ZZEXTDT<=? AND COLL_OFF IN('VAN','MTR','TOR') AND ADJ_TYPE='RF' GROUP BY INV_ADJ_UUID) B WHERE A.INV_ADJ_UUID = B.INV_ADJ_UUID AND A.ZZEXTDT=B.ZZEXTDT_MAX

wh7211

回复 1# oldknew

1. awk '{sub("AS RSLT FROM","FROM");$0=gensub("(\\)[^()]+)(FROM[^()]+\\()","\\1 AS RSLT \\2","1")}1' file

复制代码

oldknew

真太牛逼了