Buenas tardes me gustaria que me colaboran, en saber como hago la consulta al la base de datos y que me llame todos los datos , por que la que hago me llama los id , y no con los datos del export
Thank you very much ALEXANDER, this is what I need but with all the data of the survey.
https://communities.ca.com/thread/241774404Buenas tardes me gustaria que me colaboran, en saber como hago la consulta al la base de datos y que me llame todos los datos , por que la que hago me llama los id , y no con los datos del export
Good Morning John. Please explain in more detail on what you are asking in this thread?Which product is involved? Ca Service Desk or CA Service Catalog?And which release are you running then? You are asking for a query on the mdb, am i correct?And what would you like to retrieve from the mdb? What should the result of the query then be? Thanks in advance for your help in clarifying this and kind regards, Louis van Amelsfort.
Good morning Louis. Please explain in more detail what you are asking in this thread? I want to make a query to the database to extract the information of all the surveys received What product is involved? Ca Service Desk CA Service Catalog? Service Desk And what release are you running then? Oracle You are asking for a query in the mdb, am I correct? If it's an mdb query And what would you like to recover from mdb? All the information about the survey received and have a detail What should be the result of the consultation then? This query should bring all the data of the finder and id of tikect, But what happens with the original id example Case 1236-17 shows me cr 4023656 Thank you , John.
English is helping
the table storing survey data are starting with survey in the mdb
The technical reference guide containing the dictionary must be your starting point for such information :
note that the cr:1223456 is the persid of your ticket that you can join with the call_req table to retrieve the ref_num
below a simple TSQL example to retrieve some particular survey info:
DECLARE @StartDate DATETIME, @EndDate DATETIME SET @StartDate = DATEADD(mm, DATEDIFF(mm, 0, getdate()) - 1, 0) SET @EndDate = dateadd(dd, -1, DATEADD(mm, 1, @StartDate))SELECT call_req.ref_num, DATEADD(ss, survey.last_mod_dt, '1970/01/01 00:00:00') AS [Survey Date], call_req.type AS [Call Type], prob_ctg.sym AS category, [Group].last_name AS Group_name, requester.first_name AS [Requester first name], requester.last_name AS [Requester last name], customer.first_name AS [Customer first name], customer.last_name AS [Customer last name], survey.nx_comment AS [Survey comment], survey_question.sequence AS [Question Sequence], survey_question.txt AS [Question text], survey_answer.sequence AS [Answer Sequence], survey_answer.txt AS [Answer Text], survey_answer.selected AS [Answer Selected]FROM ca_contact AS customer RIGHT OUTER JOIN survey LEFT OUTER JOIN call_req LEFT OUTER JOIN ca_contact AS [Group] ON call_req.group_id = [Group].contact_uuid LEFT OUTER JOIN prob_ctg ON call_req.category = prob_ctg.persid LEFT OUTER JOIN usp_owned_resource AS CI ON call_req.affected_rc = CI.owned_resource_uuid ON survey.object_id = call_req.id ON customer.contact_uuid = call_req.customer LEFT OUTER JOIN ca_contact AS requester ON call_req.requested_by = requester.contact_uuid FULL OUTER JOIN survey_question LEFT OUTER JOIN survey_answer ON survey_question.id = survey_answer.own_srvy_question ON survey.id = survey_question.owning_surveyWHERE (survey.object_type = N'cr') AND (DATEADD(ss, survey.last_mod_dt, '1970/01/01 00:00:00') BETWEEN @StartDate AND @EndDate)ORDER BY call_req.ref_num
Hope this help
It should be noted that CABI reporting includes out of the box survey reports that may gather what you want.
I threw together a query that provides you in the results the case number, the survey name, the survey question, the answers provided from the customer, as well as comments. The query may be crude, and I'm sure it can be written cleaner. I also excluded a bunch of the data to only include the minimum, so of course adjust it to something that makes sense for you:
select call_req.ref_num, survey.sym, survey_question.txt, survey_answer.selected,survey_answer.txt,survey_question.qcomment_label,survey_question.qcomment from survey LEFT JOIN call_req ON survey.object_id = call_req.id FULL JOIN survey_question ON survey_question.owning_survey=survey.id FULL JOIN survey_answer ON survey_answer.own_srvy_question=survey_question.id where survey_answer.selected='1' OR qcomment IS NOT NULL
Like I said, you can modify it to include whatever information you'd like. If you want all the survey question and answer information you can adjust it to be:
select call_req.ref_num, survey.sym, survey_question.*, survey_answer.* from surveyLEFT JOIN call_reqON survey.object_id = call_req.idFULL JOIN survey_questionON survey_question.owning_survey=survey.idFULL JOIN survey_answerON survey_answer.own_srvy_question=survey_question.idwhere survey_answer.selected='1' OR qcomment IS NOT NULL
John 777, what are you missing in your query?
The queries examples provided by myself and Alex both return you with the re_num of the corresponding ticket.
Yes, but I need all the data of the survey and the asignataruo of the ticket
Alex, thanks for the example.
small correction of you query as it's important to filter the object type too otherwise you may report a wrong ticket as id may cross from a change or another object.
In your case for the cr object the where clause will look like:
WHERE (survey_answer.selected='1' OR qcomment IS NOT NULL) AND (survey.object_type = N'cr')
my 2 cents
I need to perform this query with all this data in excel but the crystal display distorts the data.
Good Afternoon John.
Thanks for your explanation.
Expecting then for some one of the CA Service Desk users, to further respond to this.
Kind regards, Louis.
Retrieving data ...