sqlYoga wierdness
Bob Sneidar
bobsneidar at iotecdigital.com
Mon Nov 28 19:17:26 EST 2022
<sigh> NVM. Of course, the query is going to rename the second siteid column for sites to siteid2.
Bob S
> On Nov 28, 2022, at 16:11 , Bob Sneidar via use-livecode <use-livecode at lists.runrev.com> wrote:
>
> 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 at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
More information about the use-livecode
mailing list