Bad codification when downloading TSV file. Now SQL SELECT.

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!

Find more posts tagged with

export

PostgreSQL

tsv

Accepted answers

All comments

FormerMember

Anyone...?

FormerMember

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

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

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%'