Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle SQL PLus Query,. spool output format

Posted on 2009-04-09
4
Medium Priority
?
1,393 Views
Last Modified: 2013-12-19
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!
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;

Open in new window

0
Comment
Question by:jasonslan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 24108199
There are a couple of ways of doing this.  The most performant way would be to combine all your queries into a single query such that the WHERE clause contains the conditions that appear in every statement i.e. "AND UPPER(patient.MY_GENDER)='F' etc.
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.
SELECT sum(CR_CT_Total_11_50) CR_CT_Total_11_50, 
       sum(CR_Total_11_50) CR_Total_11_50,
       sum(CT_Total_11_50) CT_Total_11_50,
       sum(CR_NO_11_to_50) CR_NO_11_to_50,
       sum(CT_NO_11_to_50) CT_NO_11_to_50,
       sum(CR_YES_11_to_50) CR_YES_11_to_50,
       sum(CT_YES_11_to_50) CT_YES_11_to_50
FROM (
SELECT  count(study.MY_ACCESSION_NUMBER)CR_CT_Total_11_50,
        0 CR_Total_11_50,
        0 CT_Total_11_50,
        0 CR_NO_11_to_50,
        0 CT_NO_11_to_50,
        0 CR_YES_11_to_50,
        0 CT_YES_11_to_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';
UNION ALL 
SELECT  0,count(study.MY_ACCESSION_NUMBER)CR_Total_11_50,0,0,0,0,0
       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';
UNION ALL 
SELECT  0,0,count(study.MY_ACCESSION_NUMBER)CT_Total_11_50,0,0,0,0
       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';
UNION ALL 
SELECT  0,0,0,count(study.MY_CUSTOM_FIELD5)CR_NO_11_to_50,0,0,0
       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';
UNION ALL 
SELECT  0,0,0,0,count(study.MY_CUSTOM_FIELD5)CT_NO_11_to_50,0,0
       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';
UNION ALL 
SELECT  0,0,0,0,0,count(study.MY_CUSTOM_FIELD5)CR_YES_11_to_50,0
       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';
UNION ALL 
SELECT  0,0,0,0,0,0,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'
);

Open in new window

0
 

Author Comment

by:jasonslan
ID: 24108486
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 

Open in new window

0
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 1000 total points
ID: 24108540
Sorry, I left in the ; at the end of each statement.  That's the problem with not being able to run the query to test it ;-) Try taking out all the semicolons except the last one:
SELECT sum(CR_CT_Total_11_50) CR_CT_Total_11_50, 
       sum(CR_Total_11_50) CR_Total_11_50,
       sum(CT_Total_11_50) CT_Total_11_50,
       sum(CR_NO_11_to_50) CR_NO_11_to_50,
       sum(CT_NO_11_to_50) CT_NO_11_to_50,
       sum(CR_YES_11_to_50) CR_YES_11_to_50,
       sum(CT_YES_11_to_50) CT_YES_11_to_50
FROM (
SELECT  count(study.MY_ACCESSION_NUMBER)CR_CT_Total_11_50,
        0 CR_Total_11_50,
        0 CT_Total_11_50,
        0 CR_NO_11_to_50,
        0 CT_NO_11_to_50,
        0 CR_YES_11_to_50,
        0 CT_YES_11_to_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'
UNION ALL 
SELECT  0,count(study.MY_ACCESSION_NUMBER)CR_Total_11_50,0,0,0,0,0
       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'
UNION ALL 
SELECT  0,0,count(study.MY_ACCESSION_NUMBER)CT_Total_11_50,0,0,0,0
       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'
UNION ALL 
SELECT  0,0,0,count(study.MY_CUSTOM_FIELD5)CR_NO_11_to_50,0,0,0
       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'
UNION ALL 
SELECT  0,0,0,0,count(study.MY_CUSTOM_FIELD5)CT_NO_11_to_50,0,0
       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'
UNION ALL 
SELECT  0,0,0,0,0,count(study.MY_CUSTOM_FIELD5)CR_YES_11_to_50,0
       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'
UNION ALL 
SELECT  0,0,0,0,0,0,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'
); 
  

Open in new window

0
 

Author Comment

by:jasonslan
ID: 24108571
That is EXACTLY what I needed.. thank you SOOO MUCH!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question