SELECT C.ENTID,C.DATES,C.ONTIME,C.BILLNO,C.BILLSN
,'Z'||SUBSTR('0000000000',1,10 - LENGTH(ROW_NUMBER()OVER(ORDER BY C.ENTID,C.DATES,C.ONTIME,C.BILLNO,C.BILLSN)))||ROW_NUMBER()OVER(ORDER BY C.ENTID,C.DATES,C.ONTIME,C.BILLNO,C.BILLSN) AS ID
FROM (
SELECT A.ENTID,A.DATES,A.ONTIME,A.BILLNO,B.BILLSN
FROM PURINMT A
INNER JOIN PURINDT B ON A.ENTID = B.ENTID AND A.BILLNO = B.BILLNO
UNION ALL
SELECT A.ENTID,A.DATES,A.ONTIME,A.BILLNO,B.BILLSN
FROM SALEOUTMT A
INNER JOIN SALEOUTDT B ON A.ENTID = B.ENTID AND A.BILLNO = B.BILLNO
UNION ALL
SELECT A.ENTID,A.DATES,A.ONTIME,A.BILLNO,B.BILLSN
FROM ALEXECMT A
INNER JOIN ALEXECDT B ON A.ENTID = B.ENTID AND A.BILLNO = B.BILLNO
) C
;
由上语句生成一个View,需要时,根据BILLNO和BILLSN去取