07-31-2012 02:53 AM
Can anybody provide the SQL query it needs to associate the media ids with the job ID .
08-01-2012 01:19 PM
It would be something like this (UNTESTED):
select x.id as jobId, y.id as imageId, LIST(distinct z.id) as ImageFragmentIds, LIST(distinct z.mediaId) as Media_used_for_job from domain_job x, domain_image y, domain_imageFragment z where x.id = 2048585 --doing just one job id and x.id = y.jobid and y.id = z.imageid group by x.id, y.id
08-01-2012 07:56 PM
thanks Zenshai , very useful !!!
08-01-2012 11:00 PM
Hi Zenshai , tested and worked . However i want to select one more field from domain_job , is shown a "Enter a valid query." , do you know the reason ? And how to modify the SQL stat ? Thanks for asvise .
08-07-2012 01:37 PM
Your query isnt working because you renamed one column (x.jobid) and didnt group by the new field you are selecting (x.starttime). Also OpsCenter stores date/time in Gregorian format (i think?), it needs to be converted to a readable format via the UTCBIGINTTONOMTIME function.
Here's how I'd do what you're trying to do -
with t1 as ( select x.id as jobId, x.masterServerId as MasterServerId, y.id as imageId, LIST(distinct z.id) as ImageFragmentIds, LIST(distinct z.mediaId) as Media_used_for_job from domain_job x, domain_image y, domain_imageFragment z where x.id = 2048585 and x.id = y.jobid and x.MasterServerId = y.MasterServerId and y.id = z.imageid group by x.id, x.masterServerId, y.id ) select UTCBIGINTTONOMTIME(x.StartTime) as StartTime, t1.* from t1, domain_job x where t1.jobId = x.id and t1.MasterServerId = x.MasterServerId
This lets you avoid grouping by start time since that can have strange results. Also added masterServer to the join to avoid getting extra lines if you have the same job id appearing on multiple masters.