August 5, 2002 at 2:54 pm
I have a department that wants to move a database from SQL Server to ORACLE 8i. I know almost nothing about ORACLE, but I do know I need a SQL statement that joins a number of tables together to produce the correct result set. Can anyone help me with the corresponding statement in ORACLE 8i?
The SQL Statement that works in SQL Server is as follows:
SELECT
CUSTOMER_PATIENT.PATIENT_FMP AS fldFMP, CUSTOMER_PATIENT.PATIENT_NAME AS fldLname,
CUSTOMER_PATIENT.PATIENT_SEX AS fldSex,
CUSTOMER_PATIENT.PATIENT_DOB AS fldDOB, CUSTOMER_PATIENT.PATIENT_STREET_1 AS fldAddr1,
CUSTOMER_PATIENT.PATIENT_STREET_2 AS fldAddr2,
CUSTOMER_PATIENT.PATIENT_CITY AS fldCity, CUSTOMER_PATIENT.PATIENT_STATE AS fldState,
CUSTOMER_PATIENT.PATIENT_ZIP_CODE AS fldZip, CUSTOMER_PATIENT.PATIENT_HOME_PHONE AS fldPhone1,
CUSTOMER_PATIENT.PATIENT_OFFICE_PHONE AS fldPhone2, CUSTOMER_PATIENT.PATIENT_MILITARY_GRADE AS fldAirForce, CUSTOMER_PATIENT.PATIENT_ACTIVE_DUTY_CODE, CUSTOMER_PATIENT.PATIENT_LNAME_INIT_LAST4SSN,
CUSTOMER_PATIENT.PATIENT_NOK_PHONE, CUSTOMER_PATIENT.PATIENT_NOK_CONTACT, CUSTOMER_PROVIDER.PROVIDER_NAME AS fldPCM,
CUSTOMER_PATIENT_CATEGORY.PATIENT_CATEGORY_STATUS AS fldBenStat,
CUSTOMER_HOSPITAL_LOCATION.HOSPITAL_LOCATION_DESCRIPTION AS fldPortalDescr, CUSTOMER_PATIENT.PATIENT_SSN AS fldSSN,
CUSTOMER_PATIENT.PATIENT_ID AS fldKey, CUSTOMER_PATIENT.PATIENT_UPDATED_CUSTOMER_DATETIME
FROM
CUSTOMER_PROVIDER INNER JOIN
CUSTOMER_PATIENT_PCM ON CUSTOMER_PROVIDER.PROVIDER_ID = CUSTOMER_PATIENT_PCM.PROVIDER_ID INNER JOIN
CUSTOMER_HOSPITAL_LOCATION ON
CUSTOMER_PROVIDER.HOSPITAL_LOCATION_ID = CUSTOMER_HOSPITAL_LOCATION.HOSPITAL_LOCATION_ID FULL OUTER JOIN
CUSTOMER_PATIENT INNER JOIN
CUSTOMER_PATIENT_CATEGORY ON CUSTOMER_PATIENT.PATIENT_CATEGORY_ID = CUSTOMER_PATIENT_CATEGORY.PATIENT_CATEGORY_ID ON
CUSTOMER_PATIENT_PCM.PATIENT_ID = CUSTOMER_PATIENT.PATIENT_ID
Can anyone help me with the ORACLE 8i syntax?
Cheers!
Mark
Cheers!
Mark
August 6, 2002 at 6:50 am
It's been a while, but I may be able to offer some general remarks...
In Oracle, if I recall correctly, you can't explicitly define JOINs. In the FROM clause, you have to list out all the tables you want joined in your query, separated by commas, just like in the older versions of SQL Server and Sybase. Then, you have to use the WHERE clause to specify the ON criteria for the JOIN.
As for the FULL OUTER JOIN, if memory serves, you have to specify a literal '(+)' after each column on either side of the '=' sign.
I'll take a stab at it, but maybe someone who's seen Oracle more recently can do a better job. See next post.
August 6, 2002 at 7:47 am
The following ought to be reasonably close. I don't have access to an Oracle parser, so it is probably not perfect.
The FULL OUTER JOIN criterion, I hope, is addressed in the WHERE clause by the (+) signs.
The JOIN to CUSTOMER_PATIENT_PCM was unnecessary, since no column from that table is in the results set; I simply tested for inclusion, instead.
Since the INNER JOIN to CUSTOMER_PATIENT_CATEGORY is imbedded inside the OUTER JOIN to CUSTOMER_PATIENT, I thought it more to the point to go ahead and join the two tables in a subquery, and then perform the OUTER JOIN to its results set.
SELECT p.patient_fmp fldfmp
, p.patient_name fldlname
, p.patient_sex fldsex
, p.patient_dob flddob
, p.patient_street_1 fldaddr1
, p.patient_street_2 fldaddr2
, p.patient_city fldcity
, p.patient_state fldstate
, p.patient_zip_code fldzip
, p.patient_home_phone fldphone1
, p.patient_office_phone fldphone2
, p.patient_military_grade fldairforce
, p.patient_active_duty_code
, p.patient_lname_init_last4ssn
, p.patient_nok_phone
, p.patient_nok_contact
, v.provider_name fldpcm
, p.patient_category_status fldbenstat
, h.hospital_location_description fldportaldescr
, p.patient_ssn fldssn
, p.patient_id fldkey
, p.patient_updated_customer_datetime
FROM customer_provider v
, customer_hospital_location h
, (SELECT p.patient_id
, p.patient_fmp
, p.patient_name
, p.patient_sex
, p.patient_dob
, p.patient_street_1
, p.patient_street_2
, p.patient_city
, p.patient_state
, p.patient_zip_code
, p.patient_home_phone
, p.patient_office_phone
, p.patient_military_grade
, p.patient_active_duty_code
, p.patient_lname_init_last4ssn
, p.patient_nok_phone
, p.patient_nok_contact
, g.patient_category_status
FROM customer_patient p
, customer_patient_category g
WHERE p.patient_category_id = g.patient_category_id) p
WHERE v.hospital_location_id = h.hospital_location_id
AND m.patient_id (+) = p.patient_id (+)
AND v.provider_id IN (SELECT provider_id
FROM customer_patient_pcm)
Edited by - Lee Dise on 08/06/2002 07:50:02 AM
Edited by - Lee Dise on 08/06/2002 07:50:39 AM
August 6, 2002 at 9:34 am
Lee,
Thanks! I'll give that a try and see if it works! I appreciate your time and effort in helping me out!
Cheers!
Mark
Cheers!
Mark
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply