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