CODEWITHSUNDEEP
mysqloracle-database
Vijay S
Vijay S

Reputation: 1

Conversion of code from Oracle 8i to MySQL

How to convert the code below from Oracle 8i to MySQL-

select count(*) 

from   patient_visit,
        organization_master
where patient_visit.organization_id=organization_master.organization_id(+);

In the where statement "organization_master.organization_id(+)" is not working in MySQL.

Pls suggest.

Upvotes: 0

Views: 64

Answers (2)

trincot
trincot

Reputation: 350167

You would need to use the standard JOIN syntax which is supported by all modern SQL databases (also Oracle):

select    count(*)
from      patient_visit
left join organization_master
       on patient_visit.organization_id = organization_master.organization_id

If you have other joins without the (+), then just replace those with inner join: avoid the comma completely in the from clause. Other, non-join conditions just stay in the where clause.

For example:

select     count(*)
from       patient_visit
inner join patient_registration 
        on patient_registration.pprn_regd_id = patient_visit.pprn_regd_id
left join  organization_master 
        on patient_visit.com_organization_id = organization_master.com_organization_id
where      patient_visit.ghm_hosp_id = i_hosp_id

Upvotes: 0

Ary Jazz
Ary Jazz

Reputation: 1656

The (+) is an Oracle-specific notation for an outer join. I think you should write something like this

select count(*)     
FROM patient_visit
LEFT OUTER JOIN organization_master
ON patient_visit.organization_id=organization_master.organization_id

I haven't tested it since I have no data to test it but it should work.

Hope it helps

Upvotes: 1

Related Questions