Re: Convert MS-Access 2 joined queries to Oracle SQL?

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 10 Apr 2002 10:58:54 -0800
Message-ID: <3cb47d5e@news.victoria.tc.ca>

April (privatebenjamin_at_hushmail.com) wrote:
: How can I rewrite the following MS-Access queries for Oracle 7.3 sql?
: I don't know how to join the two queries?

: This is the query that joins the 2 saved queries, and is the end
: result that I want:

: SELECT qry_PEContact.EMPLOYEENAME AS PEContact, qry_Managers.GROUPDATA
: AS Manager
: FROM qry_Managers INNER JOIN qry_PEContact ON qry_Managers.DEPTID =
: qry_PEContact.EMPLOYEEDEPTID;

: Here is qry_PEContact:
: SELECT TBLEMPLOYEES.EMPLOYEENAME, TBLEMPLOYEES.EMPLOYEEDEPTID
: FROM TBLEMPLOYEES;

: Here is qry_Managers:
: SELECT TBLCBOCOMBO.GROUPDATA, TBLDEPARTMENTS.DEPTID
: FROM (TBLDEPARTMENTS INNER JOIN TBLEMPLOYEES ON TBLDEPARTMENTS.DEPTID
: = TBLEMPLOYEES.EMPLOYEEDEPTID) INNER JOIN TBLCBOCOMBO ON
: TBLEMPLOYEES.EMPLOYEENAME = TBLCBOCOMBO.GROUPDATA
: WHERE (((TBLCBOCOMBO.DIVISION)="NM") AND
: ((TBLEMPLOYEES.APPROVINGAUTHORITY)="True") AND
: ((TBLCBOCOMBO.GROUPTYPE)="NMContractMgr"));

: Thanks for your help,
: April

In ms access you can save a query and then use it in another query. In Oracle you create a view, and then use the view name like a table.

Instead of INNER JOIN you need to use the syntax

        from table1, table2 where table1.col = table2.col

the renameing of columns (column alias) is also a differentsyntax. Instead of col as name, in oracle you have column "name" (note double quotes).

Also in oracle, string values are quoted with single quotes ', though my example doesn't try to fix that.

So the untested, and not quite correct code for oracle would be (with your final query at the bottom)

CREATE OR REPLACE VIEW qry_PEContact AS
SELECT
    TBLEMPLOYEES.EMPLOYEENAME
  , TBLEMPLOYEES.EMPLOYEEDEPTID
FROM TBLEMPLOYEES; CREATE OR REPLACE VIEW qry_Managers AS
SELECT
    TBLCBOCOMBO.GROUPDATA
  , TBLDEPARTMENTS.DEPTID
FROM
    TBLDEPARTMENTS
  , TBLEMPLOYEES
  , TBLCBOCOMBO
where

	TBLDEPARTMENTS.DEPTID = TBLEMPLOYEES.EMPLOYEEDEPTID
and	TBLEMPLOYEES.EMPLOYEENAME = TBLCBOCOMBO.GROUPDATA
and
	TBLCBOCOMBO.DIVISION)="NM" AND
	TBLEMPLOYEES.APPROVINGAUTHORITY)="True" AND
	TBLCBOCOMBO.GROUPTYPE)="NMContractMgr"

;

SELECT

    qry_PEContact.EMPLOYEENAME 	"PEContact"
  , qry_Managers.GROUPDATA 	"Manager"

FROM
    qry_Managers
  , qry_PEContact
where

        qry_Managers.DEPTID = qry_PEContact.EMPLOYEEDEPTID; Received on Wed Apr 10 2002 - 13:58:54 CDT