Subquery used as an expression?

From: Travis Ormond <travis_ormond_at_hotmail.com>
Date: Wed, 09 Jun 1999 23:58:15 GMT
Message-ID: <rcD73.682$1h3.31174@dfiatx1-snr1.gtei.net>

Transact-SQL in SQL Server allows you to use a subquery (only returns 1 row) in place of an expression in the column list of a Select statement.

Is there a way to make this work in Oracle? I've tried this in SQL*Plus & have not been able to get it to run successfully.

Ex.)
SELECT A.SETID, A.VENDOR_ID, B.VNDR_LOC, TEMP1=(SELECT C.VENDOR_TYPE FROM PS_VENDOR_TYPE C WHERE C.SETID = B.SETID AND C.VENDOR_ID = B.VENDOR_ID AND C.EFFDT = B.EFFDT AND C.VENDOR_TYPE = 'TRV'), TEMP2=(SELECT C.VENDOR_TYPE FROM PS_VENDOR_TYPE C WHERE C.SETID = B.SETID AND C.VENDOR_ID = B.VENDOR_ID AND C.EFFDT = B.EFFDT AND C.VENDOR_TYPE = 'CNS'), TEMP3=(SELECT C.VENDOR_TYPE FROM PS_VENDOR_TYPE C WHERE C.SETID = B.SETID AND C.VENDOR_ID = B.VENDOR_ID AND C.EFFDT = B.EFFDT AND C.VENDOR_TYPE = 'CNT') FROM PS_VENDOR A, PS_VENDOR_TYPE B
WHERE A.SETID = B.SETID AND
A.VENDOR_ID = B.VENDOR_ID AND
B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM
PS_VENDOR_TYPE B_ED WHERE B.SETID = B_ED.SETID AND B.VENDOR_ID = B_ED.VENDOR_ID AND
B.VNDR_LOC = B_ED.VNDR_LOC AND
B_ED.EFFDT <= getdate()) Received on Wed Jun 09 1999 - 18:58:15 CDT