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
_______________________________________________
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
