h_itsm_requests_attachments Table

[will.good]

Hi,

What unit is the h_size column in the above table?

e.g. one entry in the table is for a pdf, on the request it shows the size as (733.4 kB), but in the h_size column it shows as 751020

[Keith Stevenson]

@will.good
The figure is in bytes, 1024 Bytes to a KB. 

Kind Regards

Hornbill Cloud Team

[will.good]

@Keith Stevenson

I am trying to join h_itsm_requests_attachments with h_itsm_requests to get the request status, but i get an error when trying to run this in database direct, works fine without adding the status column and joining the table...any idea where I am going wrong with the join?

SELECT
h_request_id as "Request ID",
h_pk_id as "ID",
h_filename as "Filename",
h_description as "Description",
h_size as "Size",
concat(substring(h_timestamp,9,2),"/",substring(h_timestamp,6,2),"/",substring(h_timestamp,1,4)) as "Date Added",
substring(h_timestamp,12,8) as "Time Added",
h_visibility as "Visibility",
h_status as "Status"
FROM h_itsm_requests_attachments
JOIN h_itsm_requests on h_request_id = h_pk_reference
Where h_size is not null
order by h_size DESC

[Met]

On 4/21/2023 at 3:41 PM, will.good said:

@Keith Stevenson

I am trying to join h_itsm_requests_attachments with h_itsm_requests to get the request status, but i get an error when trying to run this in database direct, works fine without adding the status column and joining the table...any idea where I am going wrong with the join?

SELECT
h_request_id as "Request ID",
h_pk_id as "ID",
h_filename as "Filename",
h_description as "Description",
h_size as "Size",
concat(substring(h_timestamp,9,2),"/",substring(h_timestamp,6,2),"/",substring(h_timestamp,1,4)) as "Date Added",
substring(h_timestamp,12,8) as "Time Added",
h_visibility as "Visibility",
h_status as "Status"
FROM h_itsm_requests_attachments
JOIN h_itsm_requests on h_request_id = h_pk_reference
Where h_size is not null
order by h_size DESC

@will.good the tables share the same column names, hence the error. Just need to specify the table name as well as the column e.g. h_itsm_requests.h_description and h_itsm_requests_attachments.h_description. Worth using aliases to make them easier to read like the example below:

 

SELECT 
  att.h_request_id as "Request ID", 
  att.h_pk_id as "ID", 
  att.h_filename as "Filename", 
  att.h_description as "Description", 
  att.h_size as "Size", 
  concat(
    substring(att.h_timestamp, 9, 2), 
    "/", 
    substring(att.h_timestamp, 6, 2), 
    "/", 
    substring(att.h_timestamp, 1, 4)
  ) as "Date Added", 
  substring(att.h_timestamp, 12, 8) as "Time Added", 
  att.h_visibility as "Visibility", 
  req.h_status as "Status" 
FROM 
  h_itsm_requests_attachments att 
  JOIN h_itsm_requests req on att.h_request_id = req.h_pk_reference 
Where 
  att.h_size is not null 
order by 
  att.h_size DESC