sqlYoga wierdness

[Bob Sneidar]

Hi all.

If anyone has any experience in using sqlYoga for joins, I have a curious issue. The following code produces a variable tFoundOrphans containing a list of service record IDs with no corresponding siteid in the Sites table, so I know the query object works. However, when I use sqlquery_delete with the same object, I get an error!


sqlyoga_executesql_err,0,0,Unknown column 'sites.siteid' in 'where clause' (DELETE FROM service WHERE sites.siteid IS NULL)

Here's the code:

   put sqlquery_createObject("service") into qServiceObjectA
   sqlquery_set qServiceObjectA, "select clause", "service.siteid, sites.siteid"
   sqlquery_set qServiceObjectA, "distinct", true
   sqlquery_set qServiceObjectA, "joins", \
         "LEFT OUTER JOIN sites ON service.siteid=sites.siteid"
   sqlquery_set qServiceObjectA, "conditions", "sites.siteid IS NULL"
   put dbQuery(qServiceObjectA, "data") into tFoundOrphans
   setStatusMsg "Deleting service orphans from the service table...", tParentCard

   if tFoundOrphans is empty then
      answer info "No orphaned service records found!" as sheet
   else
      sqlquery_delete qServiceObjectA
      Answer info "Orphaned service records have been purged!" as sheet
   end if

The SQL I am shooting for is:

SELECT DISTINCT
  service.siteid
 FROM
   service
 LEFT OUTER JOIN
   sites
 ON
   service.siteid=sites.siteid
 WHERE
   sites.siteid IS NULL;

This works also in a SQL editor.

Bob S