4 Replies Latest reply on May 27, 2016 1:48 PM by rodegard

    Bad codification when downloading TSV file. Now SQL SELECT.

    emsergio

      Good morning!

       

      I am trying to export the TSV file of all tickets in order to read them into another application.

      But no matter how I try to import it via programming languages, it gives me back codification errors. No matter if it's Windows or Ubuntu.

       

      I am trying to replace all the \x00 that plagues the file with Notepad++ also, to see if it helps, but it gets stuck.

       

      But I think this method is impossible. I swear I've tried it so many times with different codifications, and I would like to get all the tickets in the database with the same info as the TSV gives me, using SQL syntax into the PostgreSQL db.

       

      I would like you to tell me the exact SQL SELECT & JOIN statement in order to get, in one table, the following columns:

       

      Ticket number, time and date, state (open/closed), priority, user that first wrote the ticket, location, request type, subject, request detail, client, notes

       

      Thank you!

          • Re: Bad codification when downloading TSV file. Now SQL SELECT.
            emsergio

            Please! I would need some help on this topic...

              • Re: Bad codification when downloading TSV file. Now SQL SELECT.
                rodegard

                This query does not have notes, but has most of the columns you are asking about.  I will see how I can get notes added.  I use this query to see how many clients are using the web page to enter tickets versus e-mail/chat/phone.

                 

                 

                SELECT
                JT.JOB_TICKET_ID AS 'TICKET NUMBER',
                LT.LOCATION_NAME as 'LOCATION',
                JT.QUESTION_TEXT AS 'TicketRequest',
                CONVERT(VARCHAR(19),JT.REPORT_DATE) AS 'DATE OPENED',
                JT.ASSIGNED_TECH_ID AS 'TECH ID',
                TECH.USER_NAME AS 'TECH USER ID',
                TECH.FIRST_NAME + ' ' + tech.LAST_NAME AS 'TECH NAME',
                TG.NAME AS 'TECH GROUP',
                JT.CLIENT_CREATOR_ID,
                JT.CLIENT_ID,
                CT.USER_NAME AS 'REQUESTER USER NAME',
                CT.FIRST_NAME + ' ' + CT.LAST_NAME AS 'REQUESTER NAME',
                ST.STATUS_TYPE_NAME AS 'STATUS',
                PT.PRIORITY_TYPE_NAME AS 'PRIORITY',
                CASE
                WHEN JT.CLIENT_CREATOR_ID = JT.CLIENT_ID AND JT.IP_ADDRESS <> '' THEN 'Y'
                ELSE 'N'
                END As 'WebPortalCreated'

                FROM  JOB_TICKET "JT"
                Inner join TECH
                ON JT.ASSIGNED_TECH_ID = TECH.CLIENT_ID
                INNER JOIN PRIORITY_TYPE "PT"
                ON JT.PRIORITY_TYPE_ID = PT.PRIORITY_TYPE_ID
                INNER JOIN STATUS_TYPE "ST"
                ON JT.STATUS_TYPE_ID = ST.STATUS_TYPE_ID
                INNER JOIN TECH_GROUP_LEVEL "TGL"
                ON JT.TECH_GROUP_LEVEL_ID = TGL.ID
                INNER JOIN TECH_GROUP "TG"
                ON  TGL.TECH_GROUP_ID = TG.ID
                INNER Join LOCATION "LT"
                ON JT.LOCATION_ID = LT.location_ID
                INNER Join CLIENT CT
                ON JT.CLIENT_ID = CT.CLIENT_ID

                 

            • Re: Bad codification when downloading TSV file. Now SQL SELECT.
              rodegard

              Here's an updated SQL that includes Notes.  Remember this will repeat columns for each note entry per ticket.

               

              SELECT
              JT.JOB_TICKET_ID AS 'TICKET NUMBER',
              LT.LOCATION_NAME as 'LOCATION',
              JT.QUESTION_TEXT AS 'TicketRequest',
              HE.ENTRY_TEXT AS 'Notes',
              CONVERT(VARCHAR(19),JT.REPORT_DATE) AS 'DATE OPENED',
              JT.ASSIGNED_TECH_ID AS 'TECH ID',
              TECH.USER_NAME AS 'TECH USER ID',
              TECH.FIRST_NAME + ' ' + tech.LAST_NAME AS 'TECH NAME',
              TG.NAME AS 'TECH GROUP',
              JT.CLIENT_CREATOR_ID,
              JT.CLIENT_ID,
              CT.USER_NAME AS 'REQUESTER USER NAME',
              CT.FIRST_NAME + ' ' + CT.LAST_NAME AS 'REQUESTER NAME',
              ST.STATUS_TYPE_NAME AS 'STATUS',
              PT.PRIORITY_TYPE_NAME AS 'PRIORITY',
              CASE
              WHEN JT.CLIENT_CREATOR_ID = JT.CLIENT_ID AND JT.IP_ADDRESS <> '' THEN 'Y'
              ELSE 'N'
              END As 'WebPortalCreated'

              FROM  JOB_TICKET "JT"
              INNER join TECH
              ON JT.ASSIGNED_TECH_ID = TECH.CLIENT_ID
              INNER JOIN PRIORITY_TYPE "PT"
              ON JT.PRIORITY_TYPE_ID = PT.PRIORITY_TYPE_ID
              INNER JOIN STATUS_TYPE "ST"
              ON JT.STATUS_TYPE_ID = ST.STATUS_TYPE_ID
              INNER JOIN TECH_GROUP_LEVEL "TGL"
              ON JT.TECH_GROUP_LEVEL_ID = TGL.ID
              INNER JOIN TECH_GROUP "TG"
              ON  TGL.TECH_GROUP_ID = TG.ID
              INNER Join LOCATION "LT"
              ON JT.LOCATION_ID = LT.location_ID
              INNER Join CLIENT CT
              ON JT.CLIENT_ID = CT.CLIENT_ID
              RIGHT Join HISTORY_ENTRY HE
              ON JT.JOB_TICKET_ID = HE.JOB_TICKET_ID 
              WHERE JT.REPORT_DATE LIKE 'May 27 2016%'