cancel
Showing results for 
Search instead for 
Did you mean: 

associate the media ids with the job ID

whoami_batman
Level 2

Can anybody provide the SQL query it needs to associate the media ids with the job ID .

4 REPLIES 4

Zenshai
Level 4

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

whoami_batman
Level 2

thanks Zenshai , very useful !!!

whoami_batman
Level 2

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 .

 

select x.starttime as start_time,  -- amend by me 
          x.jobid 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 = 250771
  and x.id = y.jobid  
  and y.id = z.imageid
group by x.id, 
              y.id

Zenshai
Level 4

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.