union statement

[antonyb]

Hi,

Below is my sql statement which is using union all keyword
,i dunno it's my union all keyword put in the right way, can somebody give me a right answer, thx

sql statement in ASP :
select JOB_NO "Job number PM",
JOB_TITLE "Job title PM",
FACILITY_CODE "Facility code PM",
FACILITY_TYPE "Facility type PM",
ACTIVITY_TYPE "Activity type PM",
ACTIVITY_CAUSE "Activity cause PM",
JOB_CODE1 "Job code1 PM",
JOB_CODE2 "Job code2 PM",
JOB_CODE3 "Job code3 PM",
ORIGINATOR "Originator PM",
TO_CHAR(DATE_RAISED, 'DD/MM/YY HH24:MI:SS') "Raised PM",
TO_CHAR(DATE_ACT_START, 'DD/MM/YY HH24:MI:SS') "Start PM",
TO_CHAR(DATE_ACT_END, 'DD/MM/YY HH24:MI:SS') "End PM",
ROUND(ELAPSED_HOURS_ACT,2) "Elapsed PM"
from job_history where Facility_code like 'ATS%'
and DATE_raised > '01-APR-03'
AND DATE_raised < '02-MAY-03'
and ACTIVITY_TYPE ='PM'
union all
select J.JOB_NO &quot;Job number&quot;,
J.JOB_TITLE &quot;Job title&quot;,
RS.JOB_REQ_NO &quot;Job request&quot;,
I.PARENT_FACILITY &quot;Parent facility&quot;,
J.FACILITY_CODE &quot;Facility code&quot;,
J.FACILITY_TYPE &quot;Facility type&quot;,
J.ACTIVITY_TYPE &quot;Activity type&quot;,
J.ACTIVITY_CAUSE &quot;Activity cause&quot;,
J.JOB_CODE1 &quot;Job code1&quot;,
J.JOB_CODE2 &quot;Job code2&quot;,
J.JOB_CODE3 &quot;Job code3&quot;,
J.ORIGINATOR &quot;Originator&quot;,
TO_CHAR(R.DATE_RAISED, 'DD/MM/YY HH24:MI:SS') &quot;Request time&quot;,
TO_CHAR(J.DATE_ACT_START, 'DD/MM/YY HH24:MI:SS') &quot;Start&quot;,
TO_CHAR(J.DATE_ACT_END, 'DD/MM/YY HH24:MI:SS') &quot;End&quot;,
ROUND((J.DATE_ACT_START-R.DATE_RAISED)*24,2) &quot;Waiting time&quot;,
ROUND(J.ELAPSED_HOURS_ACT,2) &quot;Repair time&quot;,
ROUND((J.DATE_ACT_END-R.DATE_RAISED)*24,2) &quot;Total down time&quot;
from job_history J, JOB_REQUEST_RESULT RS, job_request R, facility_item I
WHERE J.JOB_NO=RS.JOB_NO AND RS.JOB_REQ_NO=R.JOB_REQ_NO
and J.SEQUENCE_NO= 0
and RS.SEQUENCE_NO = 0
and I.facility_code = J.facility_code
and J.Facility_code like 'ATS%'
and J.DATE_RAISED >= to_date('02-APR-03','DD/MM/YY') and
J.DATE_RAISED <= to_date('02-MAY-03','DD/MM/YY')
and J.ACTIVITY_TYPE ='DOWN'


Rgds,
antony

 

[shaddow]

Each SQL statement within the UNION ALL query must have the same number of fields in the result sets with similar data types.
Wich means that if you make an
select name,age UNION ALL select age,name it wont work right.

________
George, M

 

[qwertyguy]

Not only do the fields have to be the same type, since you are aliasing the field names, the fields have to be identical in name as well