1 Reply Latest reply on Apr 9, 2018 8:49 AM by GreggSmith

    Unable to run the query in xtraction prod

    SurabhiSingh Rookie

      I had tried running a query for getting SURVEY COMMENTS in Xtraction DEV and was successful. But when I tried putting the same query in Xtraction PROD I am unable to get SURVEY COMMENTS columns. Below is the query:

      (select SUR_RESPONSE_FREEFORM.freeformtext, (select sur_submitdetails.recordkey from sur_submitdetails where sur_submitdetails.submitid in (select submitid from sur_response where questionid='1008' and responseid = SUR_RESPONSE_FREEFORM.responseid )) as ticketid, (select sur_submitdetails.submitdate from sur_submitdetails where sur_submitdetails.submitid in (select submitid from sur_response where questionid='1008' and responseid = SUR_RESPONSE_FREEFORM.responseid )) as submitdate from SUR_RESPONSE_FREEFORM)

       

      We are also able to run the query in SQL Management Studio production but not in xtraction prod.

      Also,have attached the screen shot of joins I have used in data model.

      Please help me with the same resolving the issue.

        • 1. Re: Unable to run the query in xtraction prod
          GreggSmith SupportEmployee
          1. When you say you are "unable to get SURVEY COMMENTS columns", can you please be more specific?  Is Xtraction returning blank values? No records? An error message?
          2. If you received an error message, can you please include that error message here?
          3. How are you trying to retrieve the columns in Xtraction? Record List? Group Component? Date Component?
          4. Have you tried in different components?  Do they all result in the same issue, or do some work and others don't?

           

          One issue is probably using nested selects within the SELECT portion of the main query.  Results from that type of query tend to not work in some components.  I tried following the logic of your query and wasn't quite sure why you have it structured the way you do.

           

          The query appears to reference 3 tables: Sur_SubmitDetails, Sur_Response, and Sur_Response_Freeform.  Can you please explain the relationship between these three tables?  For each survey, will all 3 tables always have 1 or more records, or could some surveys not have records in certain tables?

           

          Try this query to see if it returns what you are looking for.  It if does, then this may work for you:

           

          (

          SELECT sd.RecordKey TicketId, sd.SubmitDate, ff.FreeformText

          FROM Sur_SubmitDetails sd

               LEFT JOIN Sur_Response sr

                 ON sd.SubmitId = sr.SubmitId

               LEFT JOIN Sur_Response_Freeform ff

                 ON sr.ResponseId = ff.ResponseId

                    AND sr.QuestionId = '1008'

          )