sqlYoga wierdness

[Bob Sneidar]

<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