NIPO ODIN Version 5.17
Scenario 3: Store New Record in Another Sample Table
This scenario applies the following directives:
- We have a survey called
Sqladd3, using a Sample table called SampleSqladd3. - We have created a second Sample table called
SampleSqladd4. - In the first survey we ask if the respondent is willing to co-operate with our second survey.
- The first survey uses the
*SQLADD command to write the address information from the first survey into the Sample table for the second survey. - The
*SAMPLEDATA command is used to retrieve all address information from the Sample record in the first survey, to be reused for the new Sample record in the second survey. - The newly created records in the second survey may be distributed directly to another interviewer working on the second survey.
Example NIPO ODIN questionnaire for *SQLADD: scenario 3
*SAMPLEDATA TTInterviewNumber,TTDisplayField1,TTDisplayField2
*SAMPLEDATA TTDisplayField3,TTDisplayField4,TTShowDisplayFields
*SAMPLEDATA TTTelephoneNumber,TTRespEmail,TTAppointmentName
*VARS NewStatus,NewResponseCode, Count
*PUT NewStatus [ 0 ]
*PUT NewResponseCode [ 0 ]
*QUESTION 1 *CODES 79L1
Do you want to participate in another survey?
1: Yes
2: No *GOTO 9999
*SQLADD Count TTInterviewNumber,TTDisplayField1,TTDisplayField2,\
TTDisplayField3,TTDisplayField4,TTShowDisplayFields,\
TTTelephoneNumber,TTRespEmail,TTAppointmentName,\
NewStatus,NewResponseCode \
"SELECT InterviewNumber,DisplayField1,DisplayField2,\
DisplayField3,DisplayField4,TTShowDisplayFields,\
TelephoneNumber,Email,AppointmentName,\
Status,ResponseCode \
FROM SampleSqlAdd4"
*QUESTION 2 *IF [Count >= 1]
Writing a new record was successful
*QUESTION 3 *IF [Count = 0]}
Writing a new record failed
*QUESTION 9999
That's all
*END
You may even make appointments for your new survey. Note that the format for the AppointmentTime field is "YYYYMMDD HHmm", regardless of the date / time format used by the MS SQL server.
Example NIPO ODIN questionnaire for *SQLADD: scenario 3b
*PUT NewAppointmentTime "*?Year*?Month*?Day *?Hour*?Minutes"
*PUT NewStatus [ 4 ]
*PUT NewResponseCode [ 6 ]
*SQLADD Count TTInterviewNumber,TTDisplayField1,TTDisplayField2,\
TTDisplayField3,TTDisplayField4,TTShowDisplayFields,\
TTTelephoneNumber,TTRespEmail,\
TTAppointmentName,NewAppointmentTime,\
NewStatus,NewResponseCode \
"SELECT InterviewNumber,DisplayField1,DisplayField2,\
DisplayField3,DisplayField4,,ShowDisplayFields,\
TelephoneNumber,Email,\
AppointmentName,AppointmentTime,\
Status,ResponseCode \
FROM SampleSqlAdd4"
Do not forget to initialize the fields Status and ResponseCode in the new survey. Records will only be distributed to interviewers when they have a valid and non-definitive Status and ResponseCode.
In this example we used the Sample records of one survey to create new Sample records in another survey. If we changed the contents of any of the fields, this would be changed in both the old and the new survey. This is why we used the variables NewStatus, NewResponseCode and NewAppointmentTime rather than TTAvailabilityCode, TTStatusCode and TTAppointmentTime, because you are obviously not allowed to change these fields in a running survey.
SQL has no range limit on primary keys, therefore it is perfectly possible so create record numbers with negative values or values over 99999999, which are outside of the InterviewNumber range. The same applies to the Status field (0 or 4) and the ResponseCode field, which be set with an indefinite response code. The script does not check for valid input values. Alternatively, configure constraints on the Sample table but be advised that the *SQLADD command will then simply fail on an attempt to add a record with out-of-bounds values with the message "Database error in <database>(<owner>.<tablename>) : INSERT statement conflicted with COLUMN CHECK constraint '<field>'. The conflict occurred in database <database>', table '<tablename>', column '<field>'. The statement has been terminated.".
|
