Re: Subquery used as an expression?

Travis,

AFAIK Oracle 8 doesn't allow this, (however I believe that 8i does...). You can get around it in two ways that I know of:

1. Oracle does allow '(select... ) as anAlias' in the FROM clause of a select statement. You can then use anAlias.aColumnName in the select clause and in join conditions in the where clause.
2. You can code a stored function to return the desired value, I use this for getting the description of a code value for example.

Regards, David.

Travis Ormond 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())
Received on Wed Jun 09 1999 - 20:15:08 CDT