Hi All,

Trying to write an Update Query to simply update the attachment name to replace spaces with underscore ( _ ) . The following update query below does not seem to work. All the help would be great, thanks!

update HD_ATTACHMENT as A, HD_TICKET_CHANGE as TC, HD_TICKET as T
set A.FILE_NAME = REPLACE(A.FILE_NAME,' ','_')
where A.HD_TICKET_CHANGE_ID = TC.ID and TC.HD_TICKET_ID = T.ID


This is my Select Query
select distinct HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE as ACCTNAME,
HD_TICKET.CUSTOM_FIELD_VALUE0 as ACCTNUM,
HD_TICKET.CUSTOM_FIELD_VALUE9 as AMOUNT,
HD_TICKET.CUSTOM_FIELD_VALUE1 as PAYEETAXID,
HD_TICKET.CUSTOM_FIELD_VALUE2 as BANKNAME,
HD_TICKET.CUSTOM_FIELD_VALUE3 as ABANUM,
HD_TICKET.CUSTOM_FIELD_VALUE4 as RECIPACCTNUM,
HD_TICKET.CUSTOM_FIELD_VALUE5 as RECIPACCTNAME,
HD_TICKET.CUSTOM_FIELD_VALUE6 as RECIPACCTADDR,
HD_TICKET.CUSTOM_FIELD_VALUE7 as RECIPACCTZIP,
HD_TICKET.CUSTOM_FIELD_VALUE8 as FFCACCTNUM,
HD_PRIORITY.NAME as DISBPORT,
HD_CATEGORY.NAME as DISBCODE,
HD_TICKET.CUSTOM_FIELD_VALUE14 as CODEEXP,
HD_TICKET.CUSTOM_FIELD_VALUE10 as ACCTBENTAXID,
HD_TICKET.CUSTOM_FIELD_VALUE11 as ACCTBENNAME,
HD_TICKET.CUSTOM_FIELD_VALUE12 as ACCTBENADDR,
HD_TICKET.CUSTOM_FIELD_VALUE13 as ACCTBENZIP,
HD_TICKET.CC_LIST as MESSAGES,
U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME,
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
STATE,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U2.USER_NAME as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
HD_PRIORITY.NAME as PRIORITY,
HD_TICKET_CHANGE.COMMENT as COMMENT,
HD_ATTACHMENT.FILE_NAME as ATTACHMENT
from ( HD_TICKET,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY,
HD_ATTACHMENT)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID=<CHANGE_ID>
left join USER U1 on U1.ID = HD_TICKET.OWNER_ID
left join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
left join USER U3 on U3.ID = HD_TICKET.APPROVER_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID and
HD_STATUS.ID = HD_STATUS_ID and
HD_IMPACT.ID = HD_IMPACT_ID and
HD_ATTACHMENT.HD_TICKET_CHANGE_ID = HD_TICKET_CHANGE.ID and
HD_CATEGORY.ID = HD_CATEGORY_ID and ((HD_ATTACHMENT.FILE_NAME like '% %') and HD_TICKET.HD_QUEUE_ID = 9 )
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
Kraneda0149,

I do think this is a good idea.

Replacing the " " with "_" will rename the files in the database ONLY, not in the file system.

After the udpdate you will lose the association with the files.

Here is how you can test.

select statement
select 1 from dual

update

UPDATE HD_ATTACHMENT
SET FILE_NAME = REPLACE(FILE_NAME," ","_")
WHERE HD_TICKET_CHANGE_ID = {ENTER 1 CHANGE ID HERE TO TEST}


Save the rule and run once.

After go into the ticket and look at the attachment. The file name will be updated but you can no longer select it.

Here is the update command to fix the now broken attachment.


UPDATE HD_ATTACHMENT
SET FILE_NAME = REPLACE(FILE_NAME,"_"," ")
WHERE HD_TICKET_CHANGE_ID = {ENTER 1 CHANGE ID HERE TO TEST}
Answered 11/02/2010 by: dchristian
Red Belt

Please log in to comment
0
Thanks, I'm going to test it out first and like you said if I lose the file association I might not want to change it. My purpose was to simply create SQL statement that will concatenate other items to the filename and send it in an email as a custom rule.
Answered 11/02/2010 by: kaneda0149
Orange Senior Belt

Please log in to comment
0
Here's a rule that we use that converts attachment descriptions to URLs. In that case you change the spaces to %20. There are probably others things that should be encoded for a perfect solution. But if you're interested see the attachment.

Created a new post so I could attach a file http://itninja.com/question/how-to-turns-attachments-in-tickets-into-urls&mpage=1&key=&#69095
Answered 11/17/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share