Sub Query Brain Fade

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Sub Query Brain Fade

Post by PointsWell »

I have three BO that are related:

Participation
ContactAgency and
Contact

These have a relationship of
Participation.psContact.psContactAgency.psAgent
Where
psContact is a reference to Contact
psContactAgency is a reference to ContactAgency and
psAgent is a reference to another Contact.

I am trying to find Participations where the Agent is a party. Ordinarily I would just map the path out, but I have had to use a number of different BO Groups and this seems to be causing AIM to get confused, so I thought why not work it out by sub query:

Find me Participations WHERE Participation.psContact.ID=ContactAgency.obContact WHERE ContactAgency.psAgent = LIRU.SavedID.

which gives me a query like this:

Code: Select all

FIND Participant WHERE Participant.psContact.ID=ContactAgency.obContact.ID WHERE EXISTS ContactAgency WHERE (ContactAgency.psPrimaryAgency.ID=LoggedInRegularUser.IDContact)
However if I try to save this I get
Syntax error at token "WHERE" (Column 106) - column 106 is the WHERE EXISTS

I appear to have forgotten how to write sub queries. Can someone assist?
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Sub Query Brain Fade

Post by PointsWell »

I have also tried

Code: Select all

FIND WorkParticipant WHERE EXISTS ContactAgencyDetails WHERE (ContactAgencyDetails.psPrimaryAgency.ID=LoggedInRegularUser.IDContact AND ContactAgencyDetails.obContactGroupSeller.ID=WorkParticipant.psContact.ID)
But worryingly it gives an error:
Unknown column "Participant.psContact_RID in 'on clause'

In my example psContact is a link to a BO Group, which is why I have been using IDs not BOs for matching.
Rennur
Posts: 1191
Joined: Thu Mar 01, 2012 5:13 am
Location: Sydney, Australia

Re: Sub Query Brain Fade

Post by Rennur »

Form experience, there are limitations / bugs referencing groups in complex query parameters, and I have never managed to fix the "on clause" issue when encountered.
customaware
Posts: 2392
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Re: Sub Query Brain Fade

Post by customaware »

Have your tried....

FIND WorkParticipant WHERE COUNT ContactAgencyDetails WHERE (ContactAgencyDetails.psPrimaryAgency.ID=LoggedInRegularUser.IDContact AND ContactAgencyDetails.obContactGroupSeller.ID=WorkParticipant.psContact.ID)<>0
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Sub Query Brain Fade

Post by PointsWell »

Just gave that a try Mark and it is providing the same error :(
Last edited by PointsWell on Fri Feb 01, 2019 4:21 am, edited 1 time in total.
customaware
Posts: 2392
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Re: Sub Query Brain Fade

Post by customaware »

Please try this one....

FIND WorkParticipant WHERE COUNT ContactAgencyDetails WHERE (ContactAgencyDetails.psPrimaryAgency.ID=LoggedInRegularUser.IDContact AND ContactAgencyDetails.obContactGroupSeller = WorkParticipant.psContact)<>0

And DOUBLE check to ensure psPrimaryAgency, obContactGroupSeller and psContact ARE indeed Peer Single and NOT Peer Multiple.
And DOUBLE Check to ensure LoggedInRegularUser.ICContact is a NUMBER type.
And DOUBLE Check to ensure ContactAgencyDetails.obContactGroupSeller is the exact same reference type as WorkParticipant.psContact
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Sub Query Brain Fade

Post by PointsWell »

eagles9999 wrote:Please try this one....

FIND WorkParticipant WHERE COUNT ContactAgencyDetails WHERE (ContactAgencyDetails.psPrimaryAgency.ID=LoggedInRegularUser.IDContact AND ContactAgencyDetails.obContactGroupSeller = WorkParticipant.psContact)<>0

And DOUBLE check to ensure psPrimaryAgency, obContactGroupSeller and psContact ARE indeed Peer Single and NOT Peer Multiple.
And DOUBLE Check to ensure LoggedInRegularUser.ICContact is a NUMBER type.
And DOUBLE Check to ensure ContactAgencyDetails.obContactGroupSeller is the exact same reference type as WorkParticipant.psContact
Thank you - changing to the Object comparison rather than the ID worked.
BLOMASKY
Posts: 1470
Joined: Wed Sep 30, 2015 10:08 pm
Location: Ocala FL

Why would Object Comparison work and ID not work?

Post by BLOMASKY »

I see that your solution was to compare two objects, NOT two ID fields. My question, isn't that the same?

Bruce
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Why would Object Comparison work and ID not work?

Post by PointsWell »

BLOMASKY wrote:I see that your solution was to compare two objects, NOT two ID fields. My question, isn't that the same?

Bruce
I was originally comparing IDs because of changing BO groups across the relationships. Fortunately I had been being consistent with my Bo Groups.

Why it makes a difference I am unsure - I did take a few minutes looking at the two versions and not seeing the difference at first.

It works though so I’m going to move along.
Post Reply