CREATING VIEW WITH SELECT QUERY HAVING SUB QUERIES : CREATE OR REPLACE VIEW SAMPLE_VIEW(MISSION_ID,"ESMP TRACK NO","RFPS TRACK NO","RADAR ID")
AS
SELECT ESMP.MISSION_ID,ESMP.TRACK_NO,RFPS.RFPS_TRK_NO,(SELECT RADAR_ID FROM MATCHED_TT_DETAILS TT1 WHERE TT1.MISSION_ID = ESMP.MISSION_ID AND TT1.TRACK_NO = ESMP.TRACK_NO)
FROM ESMP_DETAILS ESMP,RFPS_DETAILS RFPS WHERE ESMP.MISSION_ID = RFPS.MISSION_ID AND ESMP.TRACK_NO = RFPS.ESMP_TRACK_NO;




I have created above view and i'm getting error.


ESMP_DETAILS table
===================
MISSION_ID    TRACK_NO 
    A            4
    B            5
    C            6


RFPS_DETAILS TABLE
==================

MISSION_ID     RFPS_TRK_NO
     A             77
     B             88


MATCHED_TT_DETAILS TABLE
========================

MISSION_ID          RADAR_ID
     A                 5
     A                 6

     B                  4

I WANT OUTPUT LIKE BELOW IN VIEW
========================
MISSION_ID   TRACK_NO   RFPS_TRK_NO    RADAR_ID
A                4          77            5
A                4          77            6
B                5          88            4
