sqlYoga wierdness

[Bob Sneidar]

I see why. sqlquery_delete does not take anything into account except for the where clause. The join and distinct properties are ignored. I will have to recode using some other method. 

Bob S


> On Nov 28, 2022, at 16:30 , Bob Sneidar via use-livecode <use-livecode at lists.runrev.com> wrote:
> 
> <sigh again> I don't think there is a way to do this with sqlYoga. I tried "... service.siteid AS siteid1..." and then referring to the columns with their aliases, but that still does not work. 
> 
> Bob S
> 
> 
>> On Nov 28, 2022, at 16:17 , Bob Sneidar via use-livecode <use-livecode at lists.runrev.com> wrote:
>> 
>> <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
>> 
>> 
>> _______________________________________________
>> 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
> 
> 
> _______________________________________________
> 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