Re: SELECT within the FROM clause?

On Sat, 09 Sep 2006 20:22:51 GMT, "Dereck L. Dietz" <dietzdl_at_ameritech.net> wrote:

>I have never seen code where there is a SELECT within the FROM clause like
>below. I have always seen it in the WHERE clause. Can anybody explain what
>a SELECT in this location is doing?
>
>P.S.: This is not my code - I'm just trying to figure it out and maybe tune
>it if I can after I can understand what it's doing.
>
>FROM CLAIM.CLM_RX A, (SELECT DISTINCT
> A.AHFS_THPY_CD,
> A.TYPE_1,
> NULL AS TYPE_2,
> A.SPFC_THPY_CLASS_CD,
> B.NATL_DRUG_CD AS NDC,
> A.HMP_DISEASE_CODE
> FROM
>bcn_hmp.ref_hmp_rx_class A,
> RPT.DB2_NDC B
> WHERE
>A.AHFS_THPY_CD=B.AHFS_THPY_CD
> AND A.HMP_DISEASE_CODE
>IN('LL','ST')) B
>WHERE A.NATL_DRUG_CD = B.NDC
>AND A.PCH_DATE BETWEEN TO_DATE('&BDATE','YYYY-MM-DD') AND
>TO_DATE('&EDATE','YYYY-MM-DD')
>
>

It's called an inline view, and it serves the same purpose as a normal view, but now coded inside the complete statement itself. It's been around since Oracle 8 if my memory serves me right.

From Oracle 9 on, you can even put a SELECT in the SELECT list, like

SELECT (SELECT 1 from dual) from dual;

This is called a scalar select. It is allowed to yield only one value, so

select (select 1,2 from dual) from dual;

is illegal, as is

select (select 1 from dual union select 2 from dual) from dual;

Jaap. Received on Sat Sep 09 2006 - 15:46:04 CDT