jasonslan
asked on
Oracle SQL PLus Query,. spool output format
I've got a query that I wrote.. (I'm no DBA so it probably looks like crap) but it works... my only problem is how it outputs when I spool it in HTML.. it comes across as multiple tables, I would like it to be output in one table with multiple coloumns Columns rather than multiple tables with only one column each. could somebody please point me in the right direction ? (please!)
Thanks!
Thanks!
spool c:\pregnancy.html
SELECT count(study.MY_ACCESSION_NUMBER)CR_CT_Total_11_50
FROM study,
patient
WHERE
patient.my_patient_id=study.patient_id
AND MY_EXAM_DATE_TIME >= '03/01/2009'
AND MY_EXAM_DATE_TIME <= '03/31/2009'
AND MODALITY_ID <= '2'
AND UPPER(patient.MY_GENDER)='F'
AND ((sysdate - patient.MY_BIRTH_DATE) / 365.25) >= '11'
AND ((sysdate - patient.MY_BIRTH_DATE) / 365.25) <= '50';
SELECT count(study.MY_ACCESSION_NUMBER)CR_Total_11_50
FROM study,
patient
WHERE
patient.my_patient_id=study.patient_id
AND MY_EXAM_DATE_TIME >= '03/01/2009'
AND MY_EXAM_DATE_TIME <= '03/31/2009'
AND MODALITY_ID = '1'
AND UPPER(patient.MY_GENDER)='F'
AND ((sysdate - patient.MY_BIRTH_DATE) / 365.25) >= '11'
AND ((sysdate - patient.MY_BIRTH_DATE) / 365.25) <= '50';
SELECT count(study.MY_ACCESSION_NUMBER)CT_Total_11_50
FROM study,
patient
WHERE
patient.my_patient_id=study.patient_id
AND MY_EXAM_DATE_TIME >= '03/01/2009'
AND MY_EXAM_DATE_TIME <= '03/31/2009'
AND MODALITY_ID = '2'
AND UPPER(patient.MY_GENDER)='F'
AND ((sysdate - patient.MY_BIRTH_DATE) / 365.25) >= '11'
AND ((sysdate - patient.MY_BIRTH_DATE) / 365.25) <= '50';
SELECT count(study.MY_CUSTOM_FIELD5)CR_NO_11_to_50
FROM study,
patient
WHERE
UPPER(study.MY_CUSTOM_FIELD5) LIKE '%NO%'
AND patient.my_patient_id=study.patient_id
AND MY_EXAM_DATE_TIME >= '03/01/2009'
AND MY_EXAM_DATE_TIME <= '03/31/2009'
AND MODALITY_ID = '1'
AND UPPER(patient.MY_GENDER)='F'
AND ((sysdate - patient.MY_BIRTH_DATE) / 365.25) >= '11'
AND ((sysdate - patient.MY_BIRTH_DATE) / 365.25) <= '50';
SELECT count(study.MY_CUSTOM_FIELD5)CT_NO_11_to_50
FROM study,
patient
WHERE
UPPER(study.MY_CUSTOM_FIELD5) LIKE '%NO%'
AND patient.my_patient_id=study.patient_id
AND MY_EXAM_DATE_TIME >= '03/01/2009'
AND MY_EXAM_DATE_TIME <= '03/31/2009'
AND MODALITY_ID = '2'
AND UPPER(patient.MY_GENDER)='F'
AND ((sysdate - patient.MY_BIRTH_DATE) / 365.25) >= '11'
AND ((sysdate - patient.MY_BIRTH_DATE) / 365.25) <= '50';
SELECT count(study.MY_CUSTOM_FIELD5)CR_YES_11_to_50
FROM study,
patient
WHERE
UPPER(study.MY_CUSTOM_FIELD5) LIKE '%YES%'
AND patient.my_patient_id=study.patient_id
AND MY_EXAM_DATE_TIME >= '03/01/2009'
AND MY_EXAM_DATE_TIME <= '03/31/2009'
AND MODALITY_ID = '1'
AND UPPER(patient.MY_GENDER)='F'
AND ((sysdate - patient.MY_BIRTH_DATE) / 365.25) >= '11'
AND ((sysdate - patient.MY_BIRTH_DATE) / 365.25) <= '50';
SELECT count(study.MY_CUSTOM_FIELD5)CT_YES_11_to_50
FROM study,
patient
WHERE
UPPER(study.MY_CUSTOM_FIELD5) LIKE '%YES%'
AND patient.my_patient_id=study.patient_id
AND MY_EXAM_DATE_TIME >= '03/01/2009'
AND MY_EXAM_DATE_TIME <= '03/31/2009'
AND MODALITY_ID = '2' AND UPPER(patient.MY_GENDER)='F'
AND ((sysdate - patient.MY_BIRTH_DATE) / 365.25) >= '11'
AND ((sysdate - patient.MY_BIRTH_DATE) / 365.25) <= '50';
spool off;
exit;
ASKER
that looks exactly like what I want.. but for some reason I'm getting "SP2-0042: unknown command "UNION ALL" - rest of line ignored. " all through out the output.. and the stuff its showing for error's at line 12 and 25, doesn't actually exist on those lines.. and everything is ended properly as far as I can tell..
AND ((sysdate - patient.MY_BIRTH_DATE) / 365.25) <= '50'
*
ERROR at line 25:
ORA-00907: missing right parenthesis
SP2-0042: unknown command "UNION ALL" - rest of line ignored.
0 CR_TOTAL_11_50 0 0 0 0 0
0 381 0 0 0 0 0
SP2-0042: unknown command "UNION ALL" - rest of line ignored.
0 0 CT_TOTAL_11_50 0 0 0 0
0 0 166 0 0 0 0
SP2-0042: unknown command "UNION ALL" - rest of line ignored.
0 0 0 CR_NO_11_TO_50 0 0 0
0 0 0 146 0 0 0
SP2-0042: unknown command "UNION ALL" - rest of line ignored.
0 0 0 0 CT_NO_11_TO_50 0 0
0 0 0 0 81 0 0
SP2-0042: unknown command "UNION ALL" - rest of line ignored.
0 0 0 0 0 CR_YES_11_TO_50 0
0 0 0 0 0 3 0
SP2-0042: unknown command "UNION ALL" - rest of line ignored.
AND ((sysdate - patient.MY_BIRTH_DATE) / 365.25) <= '50')
*
ERROR at line 12:
ORA-00933: SQL command not properly ended
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That is EXACTLY what I needed.. thank you SOOO MUCH!
You would then use a number of CASE statements in the SELECT to determine which values would be counted. This is possible because all queries are retrieving data from the same two tables.
Alternatively, you could use the query I've written below, which simply combines the existing queries into one large unioned statement and then wraps another query around it. It may not be the best in terms of performance but it is simple to do and additional queries (with different conditions, even different tables) can be easily added to it.
You will notice that although 7 expressions are retrieved by each select statement, each select statement only returns a value in one of those expressions; a different one for each query. These are then combined into a single row by the outer query performing a sum of each retrieved expression.
Open in new window