Hi, I've problem with my sql qu

[antonyb]

Hi,

I've problem with my sql query, after i run this query statement it come out the error

Error :
select JOB_NO "Job number PM",
*
ERROR at line 1:
ORA-01789: query block has incorrect number of result columns


Query statement :
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'

So, where is the problem come out, can someone give me a solution, thanks

Rgds,
Antony

 

[Hablaras]

Your first statement returns 14 fields, your second 18. This is not allowed when using the UNION clause. The no. of fields should be the same and in the same order.

Now, it would put the &quot;job request&quot; of the second query in the &quot;facitility code pm&quot; field, specified by the first. Use e.g. NULL if the field is in the first query, but not in the second
select x.col1, x.col2 from tab1 x union all
select y.col1, NULL from tab2 y