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

From: April <privatebenjamin_at_hushmail.com>
Date: 11 Apr 2002 07:20:52 -0700
Message-ID: <21e9f79.0204110620.528aac65@posting.google.com>

I found a solution. Here it is below.

Select PEContact, Manager
FROM (Select tblEmployees.EmployeeName PEContact, tblEMployees.EmployeeDeptId From tblEmployees) d,

	 (Select y.GroupData Manager, z.DEPTID
  	 from tblemployees x, tblcboCombo y, tblDepartments z
	 where  x.EmployeeName = y.GroupData 
	 and x.EMPLOYEEDEPTID = z.deptid
	 and z.division = 'NM'
	 and y.groupType = 'NMContractMgr'
	 and x.ApprovingAuthority = 'True') b 

Where d.EMPLOYEEDEPTID = b.DeptId

Replacing the tablea,2 with query 1,2 did work.

Thanks to all for your guidance,
April

yf110_at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote in message news:<3cb47d5e_at_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 Thu Apr 11 2002 - 09:20:52 CDT