Comments

• 

  • Druis
  • 1 month ago

  Try this
  
  SELECT HD_TICKET.ID,
  HD_CATEGORY.NAME AS CATEGORY,
  Q.NAME AS QUEUE_NAME
  FROM HD_TICKET
  JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
  JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID
  WHERE (HD_TICKET.HD_QUEUE_ID = 1)
  AND (HD_CATEGORY.NAME LIKE 'Software%')
  
  UNION ALL
  
  SELECT HD_ARCHIVE_TICKET.ID,
  HD_CATEGORY.NAME AS CATEGORY,
  Q.NAME AS QUEUE_NAME
  FROM HD_ARCHIVE_TICKET
  JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_ARCHIVE_TICKET.HD_CATEGORY_ID)
  JOIN HD_QUEUE Q ON Q.ID = HD_ARCHIVE_TICKET.HD_QUEUE_ID
  WHERE (HD_ARCHIVE_TICKET.HD_QUEUE_ID = 1)
  AND (HD_CATEGORY.NAME LIKE 'Software%')
• 

  • Jklass
  • 1 month ago

  i tried the entire script and both separately....and no results
  • 

    • chucksteel
    • 1 month ago

    Did you adjust the queue ID value and category for your environment? I just ran the query on my appliance using queue 2 and it worked (that queue has a Software category, so I left it).
    • 

      • Jklass
      • 1 month ago

      'General Queries' is the label for my queue and i do have a category labelled 'Software'.
      
      i used this:
      
      SELECT HD_TICKET.ID,
      HD_CATEGORY.NAME AS CATEGORY,
      Q.NAME AS QUEUE_NAME
      FROM HD_TICKET
      JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
      JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID
      WHERE (HD_TICKET.HD_QUEUE_ID LIKE 'General%')
      AND (HD_CATEGORY.NAME LIKE 'Software%')
      
      UNION ALL
      
      SELECT HD_ARCHIVE_TICKET.ID,
      HD_CATEGORY.NAME AS CATEGORY,
      Q.NAME AS QUEUE_NAME
      FROM HD_ARCHIVE_TICKET
      JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_ARCHIVE_TICKET.HD_CATEGORY_ID)
      JOIN HD_QUEUE Q ON Q.ID = HD_ARCHIVE_TICKET.HD_QUEUE_ID
      WHERE (HD_ARCHIVE_TICKET.HD_QUEUE_ID LIKE 'General%')
      AND (HD_CATEGORY.NAME LIKE 'Software%')
    • 

      • chucksteel
      • 1 month ago

      If you want to use the queue name, then you need to change HD_TICKET.HD_QUEUE_ID LIKE 'General%' and HD_ARCHIVE_TICKET.HD_QUEUE_ID LIKE 'General%' to Q.NAME like 'General%', the query is expecting an ID, which is a number.
      
      You also need to change the HD_CATEGORY.NAME LIKE 'Software%' to match the category that you want your report to reflect. Make sure you change it in both places.
    • 

      • Jklass
      • 1 month ago

      this worked thank you, ill try to amend the other columns i need on the report