slxdeveloper.com Community Forums

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!

Data & Imports Forums - T-SQL & Queries
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.

Forums RSS Feed

Back to Forum List | Back to T-SQL & Queries | New Thread

View: Search:

Author Thread: Nested "In" queries

Michael Rogers
Posts: 70

Nested "In" queries Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 10 Oct 06 2:30 PM

This has been kicking my tail!

Users asks "list all accounts that I haven't contacted in 6 mos." Piece of cake: pull four activity types and run a select where history type exists within last 6 mos. Then use that query (returns ~2k records) as a sub where his accountid not in that sub. Zero records. Every time. Select only a single activity type in the sub query: no problem. This is in SQL! I haven't even tried to get it to fly in SLX yet. It just doesn't seem to like the nested "IN" statement. (doesn't work with not exists, either) Please Help; my brain is toast

[Reply][Quote]

Ryan Farley
Posts: 2265

Re: Nested "In" queries Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 10 Oct 06 4:06 PM

What does your SQL statement look like?

[Reply][Quote]

Michael Rogers
Posts: 70

Re: Nested "In" queries Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 11 Oct 06 5:13 AM

Ryan, stripped down it looks like this:

select accountid
from sysdba.history
where accountid not in
(
select distinct accountid from history where TYPE in
(
'262154', '262155', '262145', '262148', '262146'
)
and completeddate >= dateadd(m,-6,getdate())
)
A former BP had written the sub and used it as the condition for Account in an Account group. User complained that since the upgrade to 6.2 it wasn't returning results.

Executed in QA, it won't return any results unless only one type is selected. It may be interesting to note, that query takes over five minutes to run (1.8 M history records) where the query above "completes" in less than 30 seconds. Thanks for your time on this one- I'm truely stumped.

[Reply][Quote]

Ryan Farley
Posts: 2265

Re: Nested "In" queries Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 11 Oct 06 11:24 AM

I hate to tell you this, but your query appeared to work fine on my system both via the SQL provider and the SLX provider.

I took and eval database with ~1000 accounts, added history in the last 6 months on 40 of the records, then ran the query and got back the 960 I expected. I did change one thing, the select accountid from sysdba.history where accountid not in... part will only show you accounts that have had prior contact before (ie: has a previous history item at some point), not all accounts. If you wanted to see from all accounts which ones have not been contacted in the last 6 months then you'd use select accountid from sysdba.account where accountid not in...

[Reply][Quote]

Zeph
Posts: 37

Re: Nested "In" queries Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 11 Oct 06 1:19 PM

Michael,

One possible problem would be if you had a history record in the last 6 months with no accountid. SQL server will naturally return 0 results if there is a null in a "NOT IN" operator.

Try this

select accountid
from sysdba.account /*Change suggested by Ryan*/
where accountid not in
(
select distinct accountid from history where TYPE in
('262154', '262155', '262145', '262148', '262146')
and completeddate >= dateadd(m,-6,getdate())
AND accountid IS NOT NULL
)

Might not be the problem though because you had said that NOT EXISTS didn't work either.

Zeph

[Reply][Quote]

Michael Rogers
Posts: 70

Re: Nested "In" queries Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 11 Oct 06 1:29 PM

Zeph,

That turned out to be the issue! The sub was returning distinct account ID's including NULL and "".

Thank you very much for everyone's help

Michael

[Reply][Quote]

John Gundrum
Posts: 632

Re: Nested "In" queries Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 12 Oct 06 6:38 AM

SQL server will naturally return 0 results if there is a null in a "NOT IN" operator.

Thank you! This explains why the below statement was not working for me the other day even though I was getting returns from Query Analyzer. But I don't think its SQL that is th culprit for not returning any records. This is because when I ran this statement in Query Analyzer it did return records. Its just that when I ran it in SLX it did not. So I think the real culprit is the SLX OLE-DB Provider in version 6.2.1. It was fine when I was working with SLX support on the 6.2.3 version they were using. So I think that since 6.2.1 the issue has been fixed.

SELECT DISTINCT h.accountid FROM history h WHERE h.createdate > '2006-06-01 00:00:00'

My work around was this SELECT which would have return anything but a NULL (providing the account table was not corrupt in some way)

SELECT a.accountid FROM account a WHERE a.accountid IN (SELECT DISTINCT h.accountid FROM history h WHERE h.createdate > '2006-06-01 00:00:00')

After reading this post I went back and redid the SQL and now that it looks like the below all is working without the extra step of comparing against the account table. Thanks!

SELECT DISTINCT h.accountid FROM history h WHERE h.createdate > '2006-06-01 00:00:00' AND h.accountid IS NOT NULL

John G.

[Reply][Quote]

Page 1 of 1

You can subscribe to receive a daily forum digest in your user profile. View the site code of conduct for posting guidelines.

Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!

slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation. This site, and all contents herein, are Copyright © 2024 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.	code of conduct \|

page cache (param): 11/22/2024 3:05:26 PM