|
|
Posted by Chris H on 01/31/08 15:43
What I was looking for is the correct syntax for performing a similar
operation (actually for a view). Some more detail - I have three
tables: Questions, Responses and Company Question/Response (not the
actual names).
"Questions" stores a question_id and the question text
"Responses" stores a response_id and the response text
"Company Question/Response" stores a company_id, question_id and
response_id (as well as a short text answer)
The "Company Question/Response" table stores multiple lines for
individual companies, each with different question/response ids.
I would like to create a view that would "flatten" all the question/
responses to a single line item for each company. I can do this once
for a database, but we are working with 30+ databases (with different
questions/responses) and I want to generate a script that I could run
for each db that would produce "n" views (where n is the number of
questions for the given db). These individual views would then be
joined to create the master "Company/Question1/Response1/Question2/
Response2/.../QuestionN/ResponseN view for that db. I'm hoping that
with the master view I would be able to join to that view (once) and
be able to display all the columns on output. If so, I can use the
same join across all the dbs without having to customize (the join)
based on the number of questions (obviously, the number of columns and
the header text for each in the reports would have to be changed).
The current joins look something like this:
LEFT OUTER JOIN cstd_company_qualifier_question_response qualifier1 ON
qualifier1.company_id = oncd_company.company_id AND
(qualifier1.company_qualifier_question_id = 'Q01' OR
qualifier1.company_qualifier_question_response_id IS NULL)
LEFT OUTER JOIN csta_company_qualifier_responses qualifier_responses1
ON qualifier_responses1.quest_resp_id =
qualifier1.company_qualifier_response_id
LEFT OUTER JOIN cstd_company_qualifier_question_response qualifier2 ON
qualifier2.company_id = oncd_company.company_id AND
(qualifier2.company_qualifier_question_id = 'Q02' OR
qualifier2.company_qualifier_question_response_id IS NULL)
LEFT OUTER JOIN csta_company_qualifier_responses qualifier_responses2
ON qualifier_responses2.quest_resp_id =
qualifier2.company_qualifier_response_id
LEFT OUTER JOIN cstd_company_qualifier_question_response qualifier3 ON
qualifier3.company_id = oncd_company.company_id AND
(qualifier3.company_qualifier_question_id = 'Q03' OR
qualifier3.company_qualifier_question_response_id IS NULL)
LEFT OUTER JOIN csta_company_qualifier_responses qualifier_responses3
ON qualifier_responses3.quest_resp_id =
qualifier3.company_qualifier_response_id .... this goes on to the
last question.
I think with the master view, I could cut this down to one join (on
all db's):
LEFT OUTER JOIN /*the new master view*/ MV ON MV.company_id =
oncd_company.company_id
Navigation:
[Reply to this message]
|