I'm creating a view for assessment entity in SQL Oracle and I created a derived attribute result inside it I tried this code but it's not working.

the assessment is the super-entity for three sub-entities which are diagnostic and summative and formative that is why I'm creating a view for it assessment = diagnostic + summative + formative.

the result will include the calculation of score attributes from the previous three entities.

this is for Oracle SQL developer

create view assessment as

select asses_num ,d_score ,asses_type ,student_ID from diagnostic
union 
select asses_num ,s_score ,asses_type ,student_ID from summative
union
select asses_num ,f_score ,asses_type ,student_ID from formative
union
select (d_score+s_score+f_score) as result from diagnostic, summative, formative ;

I expect to have the view created including result as derived attribute.but, I have error "query block has incorrect number of result columns"

1 Answers

3
APC On Best Solutions

"query block has incorrect number of result columns"

You get ORA-01789 because all the subqueries in a UNION statement must have the same projection (number of columns, matching datatypes). Your last subquery has a different projection from the other subqueries.

It's not clear why you're trying to solve your problem with UNION anyway. It might make more sense to use regular joins:

create view assessment as 
    select d.asses_num 
           , d.student_ID
           , d.d_score as diagnostic_score
           , s.s_score as summative_score
           , f.f_score as formative_score
           , (d.d_score + s.s_score + f.f_score) as result 
    from diagnostic d
         join summative s 
              on d.asses_num = s.asses_num
              and d.student_ID = s.student_ID
          join formative f 
              on d.asses_num = f.asses_num
              and d.student_ID = f.student_ID
;

My assumptions (in the absence of sample data and structures):

  • records exist in all three tables, so we need inner joins rather than outer joins
  • ASSESS_TYPE is not a join criterion because it defines the subtype of the sub-entities

If these are not correct or you have additional requirements please edit your question to include clarifying details.