How to convert SOQL query to SQL query?

Question

I am working on SQL query, I have pre-created SOQL query, I am looking for a way to convert it to SQL query. The query is:

SELECT CronJobDetail.Name, Id, CreatedDate, State
FROM CronTrigger
WHERE CronjobDetail.JobType = 'bla bla'
    AND CronJobDetail.Name LIKE '%bla bla2%'

But It does not run on terminal when I try to create monitoring script in Ruby. The error that I get:

(Got exception: INVALID_FIELD: No such relation 'CronJobDetail' on entity 'CronTrigger'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names. in /Users/gakdugan/.rvm/gems/ruby-1.9.3-p547/gems/restforce-2.2.0/lib/restforce/middleware/raise_error.rb:18:in `on_complete'

Do you have any idea how can I fix it and make it run on SQL?

Welcome to SO. Please read "[mcve]". When asking about code that doesn't work we need to see the minimal code necessary to demonstrate the problem. — the Tin Man, Apr 29 '16 at 18:36

score 1 · Answer 1 · answered Apr 29 '16 at 18:18

You are trying to access a relation without adding it to your FROM clause. Alternatively, if that's a custom field name, then do what the error message suggests you to do (add __c after the custom field name).

You probably want to do something like this:

SELECT CronJobDetail.Name, CronTrigger.Id, CronTrigger.CreatedDate, CronTrigger.State
FROM CronTrigger
    INNER JOIN CronJobDetail ON CronJobDetail.id = CronTrigger.foreign_id // this you have to do yourself
WHERE CronjobDetail.JobType = 'bla bla'
    AND CronJobDetail.Name LIKE '%bla bla2%'

How to convert SOQL query to SQL query?

1 Answers1