Re: Subquery used as an expression?

How about:

SELECT A.SETID, A.VENDOR_ID, B.VNDR_LOC, C1.VENDOR_TYPE, C2.VENDOR_TYPE, C3.VENDOR_TYPE FROM PS_VENDOR A, PS_VENDOR_TYPE B,
     PS_VENDOR_TYPE C1, PS_VENDOR_TYPE C2, PS_VENDOR_TYPE C3 WHERE A.SETID = B.SETID AND
A.VENDOR_ID = B.VENDOR_ID AND
B.EFFDT = (SEELCT MAX(B_EC.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 <= sysdate)
AND C1.SETID = B.SETID AND C1.VENDOR_ID = B.VENCOR_ID AND C1.EFFDT = B.EFFDT AND C1.VENDOR_TYPE = 'TRV' AND C2.SETID = B.SETID AND C2.VENDOR_ID = B.VENCOR_ID AND C2.EFFDT = B.EFFDT AND C2.VENDOR_TYPE = 'CNS' AND C3.SETID = B.SETID AND C3.VENDOR_ID = B.VENCOR_ID AND C3.EFFDT = B.EFFDT AND C3.VENDOR_TYPE = 'CNT' In article <rcD73.682$1h3.31174_at_dfiatx1-snr1.gtei.net>,   "Travis Ormond" <travis_ormond_at_hotmail.com> wrote:
> 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())
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jun 10 1999 - 22:06:19 CDT