Re: Oracle SQL statement question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 03 Sep 1998 14:30:29 GMT
Message-ID: <3608a73e.92080334@192.86.155.100>

A copy of this was sent to "Mike" <mmilli_at_priment.com> (if that email address didn't require changing) On Thu, 3 Sep 1998 07:24:33 -0700, you wrote:

>OK, I'm glad I found this group -
>
>I'm developing a VB5 application with an Oracle 7.x backend (Which will be
>converted to 8 this weekend ) I am VERY new to Oracle, a total newbie, this
>project was placed in my lap mid-stream.
>
>I need some assistance in converting this MS Access SQL statement to a
>format that Oracle will understand. I keep getting an ODBC error message
>saying I'm missing the Select keyword - since I'm not missing it I need to
>know what I am doing wrong.
>
>Thanks in advance for any assistance or advice to point me in the right
>direction:
>
>Three tables being accessed are loans_yesterday, repo and warranty:
>
>SELECT loans_yesterday.key, loans_yesterday.customer_name,
>loans_yesterday.note_date, warranty.warr_contract_amount,
>warranty.repo_mileage, warranty.warr_month_term, warranty.warr_mileage_term,
>warranty.original_loan_mileage, repo.repo_date, warranty.warr_file_amt
>FROM (loans_yesterday inner join warranty on loans_yesterday.key =
>warranty.key) inner join repo on (warranty.key_seq = repo.key_seq) and
>(loans_yesterday.key = repo.key)
>WHERE (((loans_yesterday.note_date) is not null) and
>((warranty.warr_contract_amount) is not null) and ((warranty.repo_mileage)
>is not null) and ((warranty.warr_month_term) is not null) and
>((warranty.warr_mileage_term) is not null) and
>((warranty.original_loan_mileage) is not null) and ((repo.repo_date) is not
>null) and ((warranty.warr_file_amt) is null))
>

the problem is the syntax "inner join" -- thats not valid in Oracle.

The query could be:

SELECT loans_yesterday.key, loans_yesterday.customer_name,

       loans_yesterday.note_date, warranty.warr_contract_amount,
       warranty.repo_mileage, warranty.warr_month_term,
       warranty.warr_mileage_term, warranty.original_loan_mileage,
       repo.repo_date, warranty.warr_file_amt

  from loans_yesterday, warranty, repo
 where ( loans_yesterday.key = warranty.key and

         warranty.key_seq = repo.key_seq and
         loans_yesterday.key = repo.key )

   AND
(((loans_yesterday.note_date) is not null) and ((warranty.warr_contract_amount) is not null) and ((warranty.repo_mileage) is not null) and ((warranty.warr_month_term) is not null) and ((warranty.warr_mileage_term) is not null) and ((warranty.original_loan_mileage) is not null) and ((repo.repo_date) is not null) and ((warranty.warr_file_amt) is null))

that is, you'll move the join conditions from the FROM clause to the where clause.

>Mike Milliron
>mmilli_at_primenet.com
>mikem_at_msamail.com
>
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Sep 03 1998 - 09:30:29 CDT