sqlYoga wierdness

[Bob Sneidar]

<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