APEX and/or ADF – demonstrating two similar yet different applications – part 3 – the implementation in ADF

In a previous article, I showed two versions of more or less the same application. One created in APEX by Dimitri Gielis, the other built using ADF and JHeadstart by me (see: APEX and/or ADF – demonstrating two similar yet different applications – part 2). In that article I mention several slightly more complex features and functions. In this article, I will briefly describe the implementation of these little extras. Note that the core of the application was created using JHeadstart that generated ADF artefacts based on a meta-definition of the application and the ADF BC package.

Implementation of several special features and functions

• AJAX-style disabling of Year of Birth when Gender set to Female
• Validation on valid value for Year of Birth
• AJAX-style hide and display of Answer Options for Question Style Multiple Choice (or Open)
• Displaying the answer with the proper display type (radio or drop down for multiple choice, single line or text area for open)
• The ViewObject for Survey Answers that shows records that do not yet exist in the database
• Progress Indicator for responding to a survey
• Embedding the Rich HTML editor and the unescaped (rich) output text
• Deep Link navigation from New Person Wizard to Respond to Survey
• Assign Primary Key (id) value from sequence
• ViewObjects for the Survey Summary and Survey Questions Summary

The entire JDeveloper application (10.1.3.3) can be downloaded here: Survey.zip.

 

AJAX-style disabling of Year of Birth when Gender set to Female

When the Gender is set to Female, the Year of Birth field is immediately disabled – our female visitors do not need to provide their year of birth, that closely guarded secret.

The implementation for this AJAX style functionality is incredibly simple. The Gender selectOneRadio item is made to autoSubmit – meaning that when its value is changed, the server is notified through an AJAX-style request – by setting its autoSubmit attribute to true:

<af:selectOneRadio id="NewPersonWizardGender"
                   value="#{bindings.NewPersonWizardGender.inputValue}"
                   ...
                   autoSubmit="true"
                   layout="vertical">
  <af:selectItem id="SiNewPersonWizardGender1" value="M"
                 label="Male"/>
  <af:selectItem id="SiNewPersonWizardGender2" value="F"
                 label="Female"/>
</af:selectOneRadio>

At the same time, the Year of Birth inputText element is linked to the Gender item through the partialTriggers attribute. This means that whenever the Gender value is changed, the Year of Birth item is eligible for immediate refresh as part of the AJAX-like PPR cycle. The property that may be refreshed is the disabled property: the EL expression used for disabled is directly tied to the current value of the Gender item; whenever the value in Gender is equal to F, the disabled property evaluates to true (and the Gender item will be disabled).

<af:inputText id="NewPersonWizardYearOfBirth"
              ...
              value="#{bindings.NewPersonWizardYearOfBirth.inputValue}"
              partialTriggers = " NewPersonWizardGender"
              maximumLength="4"
              disabled="#{bindings.NewPersonWizardGender.inputValue=='F'}"
/>

Validation on valid value for Year of Birth

The validation on the Year of Birth value was not added to the View layer but included in the Model. In the Entity Object for Person, I have created a validation rule:

Note that this is completely declarative – I have not written a single line of code to achieve this validation.

AJAX-style hide and display of Answer Options for Question Style Multiple Choice (or Open)

When we define a new or edit an existing Question in a Survey, it depends on the style of the question whether or not the Answer Options are shown. Only for Question with style equal to Multiple Choice are these answer options shown:

and when we change the question style to Multiple Choice, the answer options are automatically – AJAX style – displayed:

Like most AJAX functionality in ADF applications, this has been achieved with a few simple, declarative settings. The table with Answer Options is contained within a PanelHeader element. This element has its rendered attribute set to an expression that evaluates the value of the QuestionStyle; if the style is equal to ‘mc’, then the panelHeader is rendered – and the answer options are visible. The partialTriggers attribute of the parent element of the panelHeader is set to SurveyQuestionsStyle; that means that whenever a Partial Page Render request cycle is processed, the panelGroup is up for re-evaluation (and dynamic update) and its children may be rendered is before they were not or vice versa.

<af:panelGroup partialTriggers=" SurveyQuestionsStyle"
                                             id="SurveyQuestionsRegionsPanelGroup">
  <af:panelHeader text="#{nls['ANSWEROPTIONSREGION_TITLE']}"
                  rendered="#{bindings.SurveyQuestionsStyle.inputValue=='mc'}"
                  id="SurveyQuestionsRegionsAnswerOptionsRegionPanelHeader">

The QuestionsStyle element itself is made to autoSubmit: start a PPR request cycle whenever its value changes:

                                <af:selectOneRadio id="SurveyQuestionsStyle"
                                                   value="#{row.Style}"
                                                   label="#{nls['SURVEYQUESTIONS_STYLE']}"
                                                   autoSubmit="true"
                                                   immediate="true"
                                                   layout="vertical">
                                  <af:selectItem id="SiSurveyQuestionsStyle1"
                                                 value="open" label="Open (free text)"/>
                                  <af:selectItem id="SiSurveyQuestionsStyle2"
                                                 value="mc" label="Multiple Choice"/>
                                </af:selectOneRadio>

Displaying the answer with the proper display type (radio or drop down for multiple choice, single line or text area for open)

Dimitri and me had to solve the same problem: depending on the style and the display type of the Question, we had to display a different type of input element; the answer input element should either be a single line text input, a set of radio buttons, a dropdown list or a text area.

The funny thing is: we came up with the same solution: we created input elements for each of these types, all bound to the same database column. All of them are invisible, except for the one corresponding to the display type of the current question. The JSF snippet that was needed for this functionality is really very simple – watch the rendered attribute of the selectOneRadio, selectOneChoice and both inputText elements:

                  <af:panelGroup id="ResponseAnswersRegionsAnswerPanelGroup">
                    <af:panelForm rows="1" maxColumns="1" width="100%"
                                  id="ResponseAnswersAnswerPanelForm">
                      <af:selectOneRadio id="ResponseAnswersRadioValue"
                                         value="#{bindings.ResponseAnswersRadioValue.inputValue}"
                                         label="#{nls['RESPONSEANSWERS_RADIOVALUE']}"
                                         required="#{(bindings.ResponseAnswersRadioValue.mandatory) or (jhsPageCustomization.checkRequired['ResponseAnswersRadioValue'])}"
                                         rendered="#{(bindings.ResponseAnswersDisplayType.inputValue=='radio') }"
                                         layout="vertical">
                        <f:selectItems value="#{AnswerOptionsDomainQueryBindParamsRowSpecific.selectItems}"/>
                      </af:selectOneRadio>
                      <af:selectOneChoice id="ResponseAnswersValue"
                                          value="#{bindings.ResponseAnswersDropDownValue.inputValue}"
                                          label="#{nls['RESPONSEANSWERS_DROPDOWNVALUE']}"
                                          rendered="#{(bindings.ResponseAnswersDisplayType.inputValue=='drop') }"
                                          unselectedLabel="#{nls['UNSELECTED_LABEL']}">
                        <f:selectItems value="#{AnswerOptionsDomainQueryBindParamsRowSpecific.selectItems}"/>
                      </af:selectOneChoice>
                      <af:inputText id="ResponseAnswersValue"
                                    value="#{bindings.ResponseAnswersValue.inputValue}"
                                    label="#{nls['RESPONSEANSWERS_VALUE']}"
                                    columns="100" maximumLength="4000"
                                    rendered="#{(bindings.ResponseAnswersDisplayType.inputValue=='text') }"></af:inputText>
                      <af:inputText id="ResponseAnswersTextAreaValue"
                                    value="#{bindings.ResponseAnswersValue.inputValue}"
                                    label="#{nls['RESPONSEANSWERS_TEXTAREAVALUE']}"
                                    rows="10" columns="80" maximumLength="4000"
                                    rendered="#{(bindings.ResponseAnswersDisplayType.inputValue=='tarea') }"></af:inputText>
                    </af:panelForm>
                  </af:panelGroup>
                </af:panelGroup>

Let’s see how easy it is to add an additional display type or two – say the selectOneListBox and the Radio Horizontal.

To do so, we need to add the corresponding value for display type to the page where the questions are created:

<af:selectOneChoice id="SurveyQuestionsDisplayType"
                       value="#{row.DisplayType}"
                       label="#{nls['SURVEYQUESTIONS_DISPLAYTYPE']}"
                       unselectedLabel="#{nls['UNSELECTED_LABEL']}">
     <af:selectItem id="SiSurveyQuestionsDisplayType1"
                    value="text"
                    label="Single Line of Text"/>
     <af:selectItem id="SiSurveyQuestionsDisplayType2"
                    value="radio"
                    label="Radio buttons"/>
     <af:selectItem id="SiSurveyQuestionsDisplayType6"
                    value="hradi" label="Horizontal Radio Buttons"/>
     <af:selectItem id="SiSurveyQuestionsDisplayType3"
                    value="drop"
                    label="Dropdown List"/>
     <af:selectItem id="SiSurveyQuestionsDisplayType4"
                    value="tarea"
                    label="Text Area (multiline text)"/>
     <af:selectItem id="SiSurveyQuestionsDisplayType5"
                    value="list" label="List"/>
</af:selectOneChoice>
                                 

This means we can specify that a question has List or Horizontal Radio Buttons display type. We have to translate that into a rendering action, by adding the corresponding input elements to the page where answers to survey questions are collected.

<af:selectOneListbox id="ResponseAnswersListValue"
                     value="#{bindings.ResponseAnswersValue.inputValue}"
                     label="#{nls['RESPONSEANSWERS_LISTVALUE']}"
                     rendered="#{(bindings.ResponseAnswersDisplayType.inputValue=='list') }">
  <f:selectItems value="#{AnswerOptionsDomainQueryBindParamsRowSpecific.selectItems}"/>
</af:selectOneListbox>
<af:selectOneRadio id="ResponseAnswersRadioValue"
                   value="#{bindings.ResponseAnswersValue.inputValue}"
                   label="#{nls['RESPONSEANSWERS_RADIOVALUE']}"
                   rendered="#{(bindings.ResponseAnswersDisplayType.inputValue=='hradi') }"
                   layout="horizontal">
  <f:selectItems value="#{AnswerOptionsDomainQueryBindParamsRowSpecific.selectItems}"/>
</af:selectOneRadio>

The ViewObject for Survey Answers that shows records that do not yet exist in the database

The biggest challenge I faced while creating the Survey demo application for the APEX and ADF session, was easily the answering of questions in a survey by a person. The challenge is hidden in the data model:

After the Person has selected a Survey to respond to, a Response record is created. Now the person would like to be presented with the questions in the Survey and being given the opportunity to provide answers for them. Well, that’s easy enough, you might think, you just query the ANSWER records that are the children of the newly created RESPONSE. Ah, but that is the problem: those ANSWERs do not yet exist! All we have is the RESPONSE that ties PERSON to SURVEY, and of course the QUESTIONs that belong to the SURVEY. But how do you present records that do not yet exist?

Well, one can think of several solutions in the database (View with outer joins and Instead Of trigger, insert trigger on RESPONSE that creates default ANSWER records,..). However, Dimitri and I had said to work from the same database design, so that would not have been fair- the solution had to come from the middle tier.

In the recent past, I have had several opportunities to work on a similar issue, when creating a Matrix component where users can provide values for the cells for specific combinations of two masters (row and column). Here I have benefited from an Undocumented Sample by Steve Muench: #89 Maintaining Many-to-Many Intersection Rows in a Multiselect Table  . The basic structure of the trick is as follows:

1. create a ViewObject that – through the clever use of Outer Joins – returns records for every possible combination of the two masters (in this case ANSWERs for all SURVEY and QUESTION combinations).

2. create a ViewLink that associates these ‘dummy records’ with one of the masters (the RESPONSE in this case)

3. add some logic to the ViewRowImpl class for this ViewObject to recognize the creation of a real record (when a value for the answer to a question is provided) and turn it into the creation of a real ANSWER entity object instance and indirectly an ANSWER database record; add some logic to

Note: all of this is completely transparent to the Application – it is all hidden inside the Model layer. As far as the View is concerned, the ViewObject is a normal one and all its records really exist.

1. create the ResponseQuestionAnswersView, based on entities Question and Answer, both of them Updateable:

Note: the Master for the Answer records to be created through this view (Response) is not included as entity in the View.

Define the SQL Statement using Outer Joins to tie Questions to Answers (even if they do not exist) and those same Answers to the Response, identified through the Bind Variable :Bind_id. This query returns Answer records for all Questions in the Survey that the Response identified by the bind variable is associated with:

SELECT Answer.ID,
       Answer.VALUE,
       nvl(Answer.RPE_ID, rpe.id) rpe_id,
       Answer.QTN_ID,
       Question.ID AS ID1,
       Question.TEXT,
       Question.STYLE,
       Question.DATA_TYPE,
       Question.DISPLAY_TYPE,
       Question.EXPLANATION,
       Question.SVY_ID
FROM  ANSWERS Answer
,     QUESTIONS Question
,     responses rpe
WHERE Answer.QTN_ID(+) = Question.ID
and   answer.rpe_id (+) = :Bind_Id
and   :Bind_Id = rpe.id
and   rpe.svy_id = question.svy_id

2. Create ViewLink from the Survey Master to the ResponseQuestionAnswer View

Check the ViewLink SQL tab to learn about the Bind variable created for this ViewLink:

I have already made use of this bind-variable when I created the ViewObject ResponseQuestionAnswersView: in the Where clause of that view I have related the outer joined ANSWER records’ RpeId to that bind variable. I likewise joined that bind variable to the Id of the Response that in turn through its svy_id was associated with the svy_id of the Questions in the Survey the Response was for.

3. Add some logic to the ViewRowImpl class

I have instructed JDeveloper to generate the ViewRowImpl class for this ViewObject: ResponseQuestionAnswersViewRowImpl. In this class, I am going to override the setValue() method. Whenever the user enters an answer to a survey question, this method is invoked and the potentially virtual Answer record is about to be turned into a real one.

    public void setValue(String value) {
        // If we're being set to a value and we're currently null, then create
        // a new Answer entity instance with the right rpeId and qtnId
        // and associate it with the second entity usage of the current view row.
        if (value!=null && getId()==null ) {
           // questionId is outer joined and available
           Number qtnId = getId1();
           Number rpeId = getRpeId();
            EntityDefImpl answerDef = AnswerImpl.getDefinitionObject();
            DBTransaction dbTrans = ((ApplicationModuleImpl)getApplicationModule()).getDBTransaction();
            AnswerImpl exp = (AnswerImpl)answerDef.createInstance2(dbTrans,null);
            exp.setQtnId(qtnId);
            exp.setRpeId(rpeId);
            exp.setValue(value);
            /*
            * Set this new EO instance to be the second EO usage (zero-based!)
            * (Answer EO) in the current view row.
            */
            setEntity(1,exp);
            /*
            * Notify the companion view object that view attributes related to
            * this programmatically set entity row part have been changed so
            * the client can immediately update.
            */
            ((ResponseQuestionAnswersViewImpl)getViewObject()).notifyUpdatedReferencedAnswerEntityForViewRow(this);

        }
        else
          setAttributeInternal(VALUE, value);
    }

If the value is set, while the Id is null – we are dealing with a virtual Answer record that is about to be turned into a real record. To make that happen, we create a new instance of the Answer EntityObject, set the proper values on its attributes to make it link to the Question it is an answer for (QtnId) and the Response it is a part of (RpeId). The Id value is populated from the create() method in the superclass SurveyEntityImpl. When the Entity instance is complete, it is set on the current (ViewObjectRowImpl ) Row. When we created the ViewObject, we made the Answer Entity its second Entity Usage; now that we want to base the Row on a new Answer Entity Instance, we need to change the second entity usage – hence setEntity(1, newEntity) (the 1 because the counting of Entity Usages is zero-based).

Finally, we want to inform the Client (in this case the ADF DataBinding based on the ViewObject) that some attributes have been updated. I have created the method notifyUpdatedReferencedAnswerEntityForViewRow() in the ResponseQuestionAnswersViewImpl that extends the ViewObjectImpl class.

    /*
      * This method is used by the companion ResponseQuestionAnswersViewRowImpl class
      * to notify the view object that the view attributes related to the
      * underlying Answer entity object have changed. This allows the
      * user interface to immediately update with the new values.
      */
    void notifyUpdatedReferencedAnswerEntityForViewRow(ResponseQuestionAnswersViewRowImpl viewRow) {
        notifyRowUpdated((ViewRowSetImpl)viewRow.getRowSet(),
                         new Row[] { viewRow },
                         new int[] { ResponseQuestionAnswersViewRowImpl.ID,
                                     ResponseQuestionAnswersViewRowImpl.QTNID,
                                     ResponseQuestionAnswersViewRowImpl.VALUE });
    }

When the setValue() method creates the new (real) Answer Entity instance, it also calls this method to have it initiate the row-updated notification for the attributes involved.

 

Progress Indicator for responding to a survey

Dimitri had used a very nice progress indicator on the pages where the user answers the survey questions, indicating how much of the survey the user had already completed. I would like to have a similar feature in the ADF application. It is easy to do, and looks for example something  like this:

To implement this, I need to do three things:

1. drag the ADF Faces progressIndicator component to the ResponseAnswer page
2. create a simple Java class that implements the Map interface and extends the BoundedRangeModel abstract class
3. configure a managed bean based on that class and associate it with the value attribute of the progressIndicator component

 

1. drag the ADF Faces progressIndicator component to the ResponseAnswer page

The JSPX snippet looks like:

                <af:panelGroup layout="vertical"
                               inlineStyle="width:200px;position:10px;">
                  <af:panelGroup layout="horizontal">
                    ... FORM_BROWSE_BUTTONS
                  </af:panelGroup>
                  <af:objectSpacer height="15"/>
                  <af:progressIndicator value="#{IteratorNavigationProgressIndicatorModelBean[bindings.ResponseAnswersIterator]}"/>
                </af:panelGroup>

2. create a simple Java class that implements the Map interface and extends the BoundedRangeModel abstract class

package nl.amis.survey.view;

import java.util.Collection;
import java.util.Map;
import java.util.Set;

import oracle.adf.view.faces.model.BoundedRangeModel;

import oracle.jbo.uicli.binding.JUIteratorBinding;


public class IteratorNavigationProgressIndicatorModel extends BoundedRangeModel implements Map {
    JUIteratorBinding iterator;

    public IteratorNavigationProgressIndicatorModel() {
    }

    // two methods from the BoundedRangeModel abstract class
    public long getMaximum() {
        return iterator.getEstimatedRowCount();
    }

    public long getValue() {
        return iterator.getCurrentRowIndexInRange()+ iterator.getRangeStart();
    }

    // start of the methods in the Map interface
    public Object get(Object key) {
        iterator= (JUIteratorBinding)key;
        return this;
    }
... default implementations of other methods from the Map interface
}

3. configure a managed bean based on that class and associate it with the value attribute of the progressIndicator component

in the faces-config.xml file:

  <managed-bean>
    <managed-bean-name>IteratorNavigationProgressIndicatorModelBean</managed-bean-name>
    <managed-bean-class>nl.amis.survey.view.IteratorNavigationProgressIndicatorModel</managed-bean-class>
    <managed-bean-scope>request</managed-bean-scope>
  </managed-bean>

And the value attribute in the Progress Indicator:

<af:progressIndicator value="#{IteratorNavigationProgressIndicatorModelBean[bindings.ResponseAnswersIterator]}"/>

Note how the value is set to the object returned by the get(Object key) method on the IteratorNavigationProgressIndicatorModelBean bean. That happens to be that bean itself – and since that bean has the getMaximum() and getValue() methods from the BoundedRangeModel abstract class, that works out well. The iterator binding for which the progress indicator should display the maximum number of steps and the current step is passed to the bean as the key in the call to the get() method; here that value is the bindings.ResponseAnswersIterator expression.

(I made use of Frank Nimphius’ recent article on the ADF Progress Indicator component)

Embedding the Rich HTML editor and the unescaped (rich) output text

I wanted to make use of the Rich HTML Editor for defining the description of the Survey:

 

This was one of the easier features to add, as a colleague of mine – Wouter – had just described in an article on our blog: Using a rich editor in ADF applications. The steps are:

1. Download OpenWYSIWYG from http://www.openwebware.com/products/openwysiwyg/

2. Unzip into ViewController\public_html\openwysiwyg

3. Change the values for the directories in public_html\openwysiwyg\wysiwyg.js as Wouter describes in his blog.

4. Decorate the inputText elements that should appear as Rich Text editors

<af:inputText id="SurveyManagementDescription"
              value="#{bindings.SurveyManagementDescription.inputValue}"
              label="#{nls['SURVEYMANAGEMENT_DESCRIPTION']}"
              rows="2" columns="60" maximumLength="4000"
              readOnly="#{!(!bindings.SurveyManagementHasResponses.inputValue)}"
</af:inputText>
<f:verbatim>
  <script language="JavaScript" type="text/javascript">
      generate_wysiwyg('SurveyManagementDescription');
  </script>
</f:verbatim>

The JavaScript call takes care of hiding the inputText element identified by the ID value passed as parameter to the generate_wysiwyg function. Instead, the Rich Text Editor is displayed.

5. Add an onClick or other type of listener that fires whenever the user navigates out of the page; this listener must ensure that the value entered into the Rich Text Editor is copied into the inputText element before processing takes place.

<af:commandButton actionListener="#{bindings.Commit.execute}"
                  action="Commit"
                  onclick="updateTextArea('SurveyManagementDescription');return validateForm('dataForm');"
                  textAndAccessKey="#{nls['SAVE_BUTTON_LABEL_SURVEYMANAGEMENT']}"
                  id="SurveyManagementSaveButton">
  <af:resetActionListener/>
</af:commandButton>

Here we have the commit button perform the updateTextArea() operation before its normal processing takes place. This action copies the value from the rich text editor to the hidden input element, with ID equal to SurveyManagementDescription.

Part 2 – displaying the rich text

One example of displaying the Rich Text is on the page where a person starts the response to a survey:

The JSF snippet for displaying the Rich Text interpreted as HTML is simple:

 <af:panelLabelAndMessage label="#{nls['SURVEYRESPONSE_DESCRIPTION']}">
       <af:outputText escape="false" id="LovSurveysDescription"
                      partialTriggers=" SurveyResponseTitle"
                      value="#{bindings.SurveyResponseDescription.inputValue}"
       </af:outputText>
 </af:panelLabelAndMessage>
     

instead of a readOnly inputText or a plain outputText, it is panelLabelAndMessage – to have the properly aligned prompt – with an outputText with its escape attribute set to false. That means that instead of escaping the special characters in the value of the outputText, all content should be interpreted as is, interpreted and displayed by the browser.

Deep Link navigation from New Person Wizard to Respond to Survey

To be provided.

Assign Primary Key (id) value from sequence

I forgot to include a sequence in the DDL scripts I sent to Dimitri. I later sent him an email in which I suggested we should derive Primary Key values from a database sequence. However, since I more or less assumed we had agreed to work from the same database, I did not want to add anything to the database besides the sequence. So no database trigger (before row insert) that could derive the Primary Key value.

That meant deriving the value in the middle tier, more specifically the ADF BC layer. Here I have allowed myself a few short-cuts: I assume that the primary key attribute in all Entity Objects is called Id and I assume that they all may use the same database sequence. Based on these two assumptions I can create a single generic SurveyEntityImpl class that is the superclass for all Entity Objects in my Survey ApplicationModule.

package nl.amis.survey.model;

import oracle.jbo.AttributeList;
import oracle.jbo.server.EntityImpl;
import oracle.jbo.server.SequenceImpl;


public class SurveyEntityImpl extends EntityImpl {
    public SurveyEntityImpl() {
    }

    protected void create(AttributeList attributeList) {
        super.create(attributeList);
        SequenceImpl s = new SequenceImpl("survey_seq", getDBTransaction());
        setAttribute("Id", s.getSequenceNumber());
    }
}

Whenever a new instance of any EntityObject based on this super class is created, its Id attribute is set to the latest value retrieved from the database sequence. Note that if the new entity instance is never (successfully) committed to the database, the sequence value disappears, never to be seen again.

 

ViewObjects for the Survey Summary and Survey Questions Summary

The ViewObjects used for the Summary of all Surveys as well as the Question/Answers summary within one survey are both read only ViewObjects with free format queries. The two views are connected via a ViewLink; this makes for easy navigation & synchronization in the Summary page.

The SurveySummaryView was created with this SQL statement:

select rpe.svy_id
,      count(id) number_of_responses
,      min(timestamp_of_response) first_response_timestamp
,      max(timestamp_of_response) last_response_timestamp
from   responses rpe
group
by     svy_id

A very simple collection of three aggregate values. Note that this view is connected to the SurveyView via a ViewLink based on the SvyId attribute.

The QuestionSummaryView was created with this query:

select qtn.id
,      qtn.text
,      qtn.svy_id
,      qtn.data_type
,      qtn.display_type
,      qtn.style
,      qtn.explanation
,      case qtn.data_type
       when 'number'
       then (select avg(value)
             from   answers asr
             where  asr.qtn_id = qtn.id
            )
       else to_number(null)
       end average_value
,     ( select count(value)
        from   answers asr
        where  asr.qtn_id = qtn.id
       ) number_of_replies
from   questions qtn

Its only slightly complex bit is the CASE expression. It inspects the data_type of the Question; when it is ‘number’, the case expression will return the average value of all answers to the question. If it is not numeric, it will return NULL.