3,491
社区成员
发帖
与我相关
我的任务
分享
SELECT *
FROM (SELECT CASE
WHEN V2.FIELD1 > 0 THEN
'B'
ELSE
'R'
END VFIELD1,
CASE
WHEN V2.FIELD2 > 0 THEN
'B'
ELSE
'R'
END VFIELD2,
V2.FIELD3 || '''' || V2.FIELD4 ||
V2.FIELD5 AS CTMODEL,
(SELECT rtrim(LIST(DISTINCT FIELD6 || '/'), '/')
FROM TABLE2
WHERE ID = V2.ID) FIELD6LIST,
(SELECT rtrim(LIST(DISTINCT FIELD7 || '/'), '/')
FROM TABLE3
WHERE ID = V2.ID) FIELD7LIST,
(SELECT T4.STATE
FROM TABLE4 T4
WHERE T4.CNO = V2.CINNO) INSTATE,
(SELECT T4.STATE
FROM TABLE4 T4
WHERE T4.CNO = V2.COUTNO) OUTSTATE,
(SELECT CASE
WHEN COUNT(*) > 0 THEN
'Y'
ELSE
'N'
END
FROM TABLE5 T5
LEFT JOIN TABLE6 PARTITION(PARTCUR) T6
ON T5.MID = T6.MID
LEFT JOIN TABLE7 T7
ON T7.JCODE = T6.JCODE
WHERE T5.ID = V2.ID
AND T6.JCODE = 'C1'
and rownum = 1) ISMID,
(SELECT CASE
WHEN COUNT(*) > 0 THEN
'Y'
ELSE
'N'
END
FROM TABLE5 T5
LEFT JOIN TABLE6 PARTITION(PARTCUR) T6
ON T5.MID = T6.MID
LEFT JOIN TABLE7 T7
ON T7.JCODE = T6.JCODE
WHERE T5.ID = V2.ID
AND T6.JCODE = 'C2'
AND ROWNUM = 1) ISTOP,
(SELECT CASE
WHEN COUNT(V1.VID) > 0 THEN
'Y'
ELSE
'N'
END
FROM VIEW1 V1
WHERE V1.ID = V2.ID
AND V1.TYPE = 'T') ISPREE
FROM VIEW2 V2) TB