September 15, 2009 at 12:17 pm
To Anyone Who Can Help:
I am trying to pull some data from two Oracle databases into SPSS. I do this all the time. Simple, right?
The issue is the IT guys will not give me the table schema/data model of one of the tables I need, but he provided me with some code to pull what I needed. However, I am not familiar with adding sub-queries nor the format the Oracle guy provided, and I feel like a totally idiot because I cannot make the code work.
Here is my original SPSS query (that worked fine)
GET DATA /TYPE=ODBC /CONNECT=
'DSN=oems;UID=slgibson;PWDxxxxxx;'
/SQL =
"Select incident_quarter, lic.name, pec3.description Signs_Symptoms, regions.description Regions "
"FROM ppcr_call_sheet pcs "
" Left join ppcr_signs_and_symptoms ss on ss.ppcr_form_number = pcs.ppcr_form_number "
" Left join licensure.agency lic on lic.agency_id = pcs.agency_id "
" Left join ppcr_edit_codes pec3 on pec3.edit_code = ss.sign_or_symptom AND pec3.edit_code_type = 'SYMP' "
" Left join fipses on SUBSTR(fipses.code,3) = lic.fips "
" Left join regions on fipses.r_r_id = regions.r_id "
"WHERE pcs.incident_reported between to_date('20080101','yyyymmdd') and to_date('20081231','yyyymmdd') "
"AND sign_or_symptom in( '06' , '04', '16', '24', '12', '27' ) "
"AND type_of_call='06' "
.
Execute.
After stating that I needed to pull one additional field (actually two; one field has the numeric value and the other has the corresponding description)into my code, the bold text is what he provided me:
GET DATA /TYPE=ODBC /CONNECT=
'DSN=oems;UID=slgibson;PWD=xxxx;'
/SQL =
"Select incident_quarter,
(SELECT name FROM agency a
WHERE a.agency_id=pcs.agency_id ) agency_name,
(SELECT description FROM agency_lookup_codes ac,agency a
WHERE ac.edit_code=a.agency_category
AND ac.edit_code_type='ACAT'
AND a.agency_id=pcs.agency_id ) agency_category, pec3.description Signs_Symptoms, regions.description Regions "
"FROM ppcr_call_sheet pcs "
" Left join ppcr_signs_and_symptoms ss on ss.ppcr_form_number = pcs.ppcr_form_number "
" Left join licensure.agency lic on lic.agency_id = pcs.agency_id "
" Left join ppcr_edit_codes pec3 on pec3.edit_code = ss.sign_or_symptom AND pec3.edit_code_type = 'SYMP' "
" Left join fipses on SUBSTR(fipses.code,3) = lic.fips "
" Left join regions on fipses.r_r_id = regions.r_id "
"WHERE pcs.incident_reported between to_date('20080101','yyyymmdd') and to_date('20081231','yyyymmdd') "
"AND sign_or_symptom in( '06' , '04', '16', '24', '12', '27' ) "
"AND type_of_call='06' "
.
Execute.
I cleaned up the format, and tried to run it multiple times with no luck:
"Select incident_quarter, lic.name, pec3.description Signs_Symptoms, regions.description Regions "
"FROM ppcr_call_sheet pcs "
( "SELECT name "
"FROM agency a "
"WHERE a.agency_id=pcs.agency_id" )
agency_name,
(" SELECT description "
"FROM agency_lookup_codes ac, agency a "
"WHERE ac.edit_code=a.agency_category "
"AND ac.edit_code_type='ACAT' "
" AND a.agency_id=pcs.agency_id" )
" Left join ppcr_signs_and_symptoms ss on ss.ppcr_form_number = pcs.ppcr_form_number "
" Left join licensure.agency lic on lic.agency_id = pcs.agency_id "
" Left join ppcr_edit_codes pec3 on pec3.edit_code = ss.sign_or_symptom AND pec3.edit_code_type = 'SYMP' "
" Left join fipses on SUBSTR(fipses.code,3) = lic.fips "
" Left join regions on fipses.r_r_id = regions.r_id "
"WHERE pcs.incident_reported between to_date('20080101','yyyymmdd') and to_date('20081231','yyyymmdd') "
"AND sign_or_symptom in( '06' , '04', '16', '24', '12', '27' ) "
"AND type_of_call='06' "
.
Execute.
Any assistance you can provide would be helpful!!
September 16, 2009 at 8:19 am
Sherrina, not sure, but what happens if you replace your first sub query with this?
(SELECT a.name FROM agency a, ppcr_call_sheet pcs
WHERE a.agency_id=pcs.agency_id ) agency_name
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply