SQL for all JOBS objects, that also lists the Custom Attributes that are set

[Rick Murray]

Yes, it is time for what appears to be my "Weekly SQL question" 

I am in the process of setting up a Custom Attribute that contains the Department assigned to JOBS.  So far, that is working fine. 

The next step is being able to report all of our JOBS objects, and include the Custom Attribute.  That is where I am stuck

I used the listing of table to locate any table associated with "attribute", then I searched each table, but wasn't able to locate the following



My eventual goal is to include the Custom Attribute info in the following, so I can create a list of the JOBS and what department is the business owner

SELECT OH_CLIENT, OH_NAME, OH_TITLE, OH_CRDATE, OH_LASTDATE, OH_LASTCNT
FROM OH
WHERE OH.OH_CLIENT = '99'
AND OH.OH_DELETEFLAG = '0'
AND OH.OH_OTYPE = 'JOBS'

Anyone know what table this info should be in?

Also, any chance someone already has the sql created?

Thanks in advance

Rick

[Josef Scharl]

Hi Rick,
We don't use customer attribute here, but I play with them in a sandbox environment.
Maybe this SQL can help:

select oh_client as "Client", oh_name as "Name", oca_value as "Department" 
  from uc4.oh 
  join uc4.oca on oh_idnr = oca_oh_idnr 
  where oca_name = '&CustAtt_D#'

In this case I named the attribute "&CustAtt_D#", so I used this in the where clause.

Cheers Josef

[Rick Murray]

Thanks Josef, I will give that a try

[Christoph Rekers]

Hi @Rick Murray

This works for me (MS SQL):

SELECT
    oh_name,
    STRING_AGG(OCA.OCA_VALUE, ', ') AS concatenated_values
FROM
    oh
JOIN
    OCA ON OCA.oca_oh_idnr = oh.oh_idnr
WHERE
    OCA.OCA_VALUE IS NOT NULL
    AND oh.OH_otype = 'JOBS'
    AND oh.oh_client = '1000'
GROUP BY
    oh.oh_name;

Cheers

Christoph 

------------------------------
----------------------------------------------------------------
Automic AE Consultant and Trainer since 2000
----------------------------------------------------------------
------------------------------