Re: ANSI SQL

From: Michael Garfield Sørensen, CeDeT <mgs_at_CeDeT.dk>
Date: Wed, 21 Jun 2006 08:55:51 +0200
Message-ID: <20060621085551.vgtdcw6zwgwcgcws@farina.netsite.dk>

Dennis, what are your assumptions based on????

Nirmalya, your SQL should work without modification in ORACLE (at least in 10gR2) - test case (includes your SELECT unaltered):

SQL> set echo on
SQL> select * from v$version;

BANNER

---

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Solaris: Version 10.2.0.2.0 - Production NLSRTL Version 10.2.0.2.0 - Production

Forløbet: 00:00:00.02
SQL> drop table pt_data;

Tabel er droppet.

Forløbet: 00:00:00.01
SQL> drop table pt_data_location;

Tabel er droppet.

Forløbet: 00:00:00.00
SQL> drop table facility_data;

Tabel er droppet.

Forløbet: 00:00:00.00
SQL> drop table md_data;

Tabel er droppet.

Forløbet: 00:00:00.00
SQL>
SQL> CREATE TABLE pt_data(

   2 mpid varchar2(1),
   3 first_name varchar2(1),
   4 last_name varchar2(1),
   5 dob varchar2(1),
   6 ssn varchar2(1),
   7 admit_status_descr varchar2(1),
   8 notes varchar2(1),
   9 HOME_LOCATION_ID number
  10 );

Tabel er oprettet.

Forløbet: 00:00:00.00
SQL>
SQL> create table facility_data(

   2 facilityno number,
   3 facilityname varchar2(1),
   4 billinglocation_id varchar2(1)
   5 );

Tabel er oprettet.

Forløbet: 00:00:00.00
SQL>
SQL> create table pt_data_location(

   2 mpid varchar2(1),

   3    md_first_name varchar2(1),
   4    md_last_name varchar2(1),
   5    md_upin varchar2(1),

   6 location_id number,
   7 nephrologist_id number,
   8 facility_no number
   9 );

Tabel er oprettet.

Forløbet: 00:00:00.00
SQL>
SQL> create table md_data(

   2 license_no varchar2(1),
   3 office_phone varchar2(1),
   4 id number
   5 );

Tabel er oprettet.

Forløbet: 00:00:00.00
SQL>
SQL> select ptd.MPID, ptd.FIRST_NAME, ptd.LAST_NAME, ptd.DOB,

   2 ptd.SSN, ptd.ADMIT_STATUS_DESCR,
   3 FD.FACILITYNO, FD.FACILITYNAME,
   4 pdl.MD_FIRST_NAME, pdl.MD_LAST_NAME, pdl.MD_UPIN, mdd.LICENSE_NO,    5 mdd.OFFICE_PHONE, ptd.NOTES
   6 FROM
   7 (

   8      (
   9      PT_DATA PTD LEFT JOIN FACILITY_DATA FD  ON
  10      PTD.HOME_LOCATION_ID = TO_NUMBER(FD.BILLINGLOCATION_ID)
  11      AND FD.BILLINGLOCATION_ID IS NOT NULL
  12      )
  13      LEFT JOIN PT_DATA_LOCATION PDL ON
  14      PTD.MPID = PDL.MPID AND PTD.HOME_LOCATION_ID = PDL.LOCATION_ID

  15 )
  16 LEFT JOIN MD_DATA MDD ON PDL.NEPHROLOGIST_ID = MDD.ID   17 ORDER BY PDL.FACILITY_NO, PTD.MPID; Ingen rækker er valgt

Forløbet: 00:00:00.00
SQL> spool off;

HTH
Michael

Quoting Dennis Williams <oracledba.williams_at_gmail.com>:

> Nirmalya,
>
> Offhand, the Oracle syntax is LEFT OUTER JOIN.
> Next, in the clause after the FROM on line 6, I think you need a SELECT to
> start that clause.
> Silly me, I thought Oracle was ANSI compliant. Whose ANSI syntax is this?
>
> Dennis Williams

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 21 2006 - 01:55:51 CDT