JTX 9.3.1 Job Query SQL Error - Insufficient Permissions

2973
1
08-24-2010 05:30 AM
AndrewMcColgan
New Contributor
Hello all,

I am getting an 'Insufficient Permissions' error with a job query in which I would like the jobs on hold not to show up in the jobs in progress list.  The query works fine in Oracle, and is successfully verified in JTX Configuration, but then I get an error when trying to save the query.  The error seems to be generated from the last condition in the query:

JTX_JOBS.JOB_ID NOT IN
(SELECT JOB_ID FROM JTX_JOB_HOLDS WHERE
(JTX_JOB_HOLDS.RELEASED_DATE IS NULL))

The full query is attached.  Any ideas?

Thanks,
Andrew McColgan
Computer Software Specialist
Memphis Light, Gas, and Water
0 Kudos
1 Reply
TopeBello
Occasional Contributor III
Hi Andrew,

The JTX query framework expects you to always select a field from the jobs table. If the "Jobs" table is not in the "FROM" clause, the query is not considered valid. Unfortunately the error message displayed is probably not that accurate.

To accomplish what you are trying to do, here is what I did -
Select
JTX_JOBS.JOB_ID
From
JTX_JOBS,sde.jtx94.JTX_JOB_HOLDS
Where
sde.jtx94.JTX_JOB_HOLDS.job_id = sde.jtx94.JTX_JOBS.job_id  AND sde.jtx94.JTX_JOB_HOLDS.released_date  IS Null

Basically, I am selecting job id from the jobs table where the released date is null. I had to do a join of the jobs table to the holds table, hence the jobs.job_id = holds.job_id

I hope this helps!

Thanks,
Tope
0 Kudos