September 15, 2009 at 1:32 pm
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 6:19 am
quick question... what prevents you from adding agency and agency_lookup_codes tables to the main query?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 16, 2009 at 11:57 am
Hi Paul,
I have never used an inline query before and just went along with the IT guy's layout for the most part.
We got it working through some rearranging and adding spaces:
GET DATA /TYPE=ODBC /CONNECT=
'DSN=oems;UID=slgibson;PWD=xxxx; SERVER=oems;
/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' "
"AND agency_category in( '1', '2', '4' ) "
.
Execute.
September 16, 2009 at 12:19 pm
sherrina.gibson (9/16/2009)We got it working through some rearranging and adding spaces
way to go! 🙂
It just looks to me like a very unusual scenario to rely in inline views but, it's working!!! who cares? 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply