I have two tables, one is questionnaire (qstnr), which holds the parent record. The other is questionnaire item (qitem) which holds questions and responses that link to the parent table via a complex process. I have a "GENERIC" load script to essentially create columns of all of qitem question text, so that you can add all of them to a report. My problem is, my generic portion is a composite key link. For instance, say you have a qitem with an ID of 1. That item can show up multiple times per questionnaire, with an incremental index. What I need to do is join this item in my generic so that it sees a qstnr id along with a qitem index as two different items.
This is what my generic looks like right now
qstnr_item:
GENERIC SQL
SELECT qstnr_id AS lnk_qstnr_id
, 'QstnrItem_' + COALESCE(qicat_rpt_tag, qicat_external_id) AS qitem_name
, COALESCE(qiopcat_name, '') AS qitem_response
FROM qstnr_form_step_item
INNER JOIN qstnr_form_step ON qfs_id = qfsitem_qfs_id
INNER JOIN questionnaire ON qstnr_qform_id = qfs_qform_id
INNER JOIN #qstnr ON qr_qstnr_id = qstnr_id
INNER JOIN qstnr_item_catalog ON qicat_id = qfsitem_qicat_id
LEFT JOIN qstnr_item ON qitem_qicat_id = qicat_id AND qitem_qstnr_id = qstnr_id
LEFT JOIN qstnr_item_option_catalog ON qiopcat_external_id = qitem_response
WHERE qicat_type = 1
The issues is, that qitem_id of 1 might show up 3 times for the lnk_qstnr_id with an ID 1, but it would have an index in each row of 0,1,2. So I need the load script to treat those as unique. Basically my composite key would be qstnr_id and qitem_index. How can I make that work in my load script?
