The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Can query be run as SLX Group (v7.2) 
Posted: 10 Mar 08 1:27 PM
|
I created a query to identify Account records that do not have a a Contact record designated Primary. The SQL query is
SELECT * FROM ACCOUNT WHERE (ACCOUNTID NOT IN(SELECT ACCOUNTID FROM CONTACT WHERE ISPRIMARY = 'T'))
Is there way to use this complex query in the Client:Query Builder to display records? I am thinking NOT and that I will have to pop a grid:temp table to display records.
Thanks!
Jeff Parker |
|
|
|
Re: Can query be run as SLX Group (v7.2)
Posted: 10 Mar 08 3:40 PM
|
Hi Jeff
You can paste all that sub-select into a condition where
* the "Operator" is "In"
* the "Value is" contains (SELECT ACCOUNTID FROM CONTACT WHERE ISPRIMARY = 'T') including the brackets
However, is that query going to do what you wanted? Should it be:
(Select ACCOUNTID from CONTACT group by ACCOUNTID HAVING COUNT(*) <> 1)
Hmmm - I get the same answer with both, and there are quite a lot like that on our own database. Darn, whish I hadn't looked!
Cheers
Paul Roussell, Adexio, Brisbane Australia |
|
|
|
Re: Can query be run as SLX Group (v7.2)
Posted: 11 Mar 08 9:18 AM
|
...this is strange.....Turns out my original query
SELECT * FROM ACCOUNT WHERE (ACCOUNTID NOT IN(SELECT ACCOUNTID FROM CONTACT WHERE ISPRIMARY = 'T'))
works fine if I deselect "Use Value as a Literal"
Also, if I wrap the entire SQL statement in () so that it looks like
(SELECT * FROM ACCOUNT WHERE (ACCOUNTID NOT IN(SELECT ACCOUNTID FROM CONTACT WHERE ISPRIMARY = 'T'))) and select "Use Value as a Literal" it works.
Looks like there is more than one way to skin a cat.
Thanks Paul
|
|
|
|
Re: Can query be run as SLX Group (v7.2)
Posted: 11 Mar 08 9:45 AM
|
Jeff,
You could also try
SELECT accountid FROM Account WHERE NOT EXISTS (SELECT contact.accountid FROM Contact WHERE account.accountid = contact.accountid and contact.isprimary = 'T')
|
|
|
|
Re: Can query be run as SLX Group (v7.2)
Posted: 11 Mar 08 10:37 AM
|
Hi Jeff,
I am trying to create a group using the same query, but I cannot get it to work. Exactly how did you set it up in Query Builder?
Thanks, Barbara
 |
|
|
|
Re: Can query be run as SLX Group (v7.2)
Posted: 11 Mar 08 10:43 AM
|
Here is probably the easiest I found, provided by Ron Fisher.
Create an Account Group. Add condition Account.Accountid. Operator is IN
Type the value as SELECT ACCOUNTID FROM CONTACT WHERE ISPRIMARY = 'T'.
Then choose NOT
Do NOT use the "Use Value as a Literal" Checkbox.
Then in your layout tab select a few fields to display, start with just Account.Account probably.
That's it.
Jeff |
|
|
|