cancel
Showing results for 
Search instead for 
Did you mean: 

Query all BP's without specific Activities

Former Member
0 Kudos

I'm attempting to query our database to narrow down BP's that do not have specific activities defined by activity type.  However when I run a simple LEFT JOIN for the OCRD to the OCLG table it is only returning BP's with activities that meet the criteria the same as an INNER JOIN.  Any ideas why this is occurring?  Here's a simple query I used for testing.

SELECT T0,[CardCode], T0.[CardName], T1.[ClgCode]

FROM OCRD T0

LEFT JOIN OCLG T1 ON T0.CardCode = T1.CardCode

WHERE T1.[CntctType] = '4'

I expect to see all BP's from OCRD and then any activity numbers or blank/null based on the criteria.  Changing the join in the query doesn't change the results which I find strange.  Maybe I'm looking at this completely wrong.  Thanks in advance.

Accepted Solutions (1)

Accepted Solutions (1)

KennedyT21
Active Contributor
0 Kudos

Hi Karl,

Try This

select T0.[CardCode], T0.[CardName], Clgcode= ISNULL(( select top 1 T1.[ClgCode] from OCLG T1 where T0.CardCode = T1.CardCode and T1.[CntctType] = '1'),0)

from OCRD t0

Regards

Kennedy

Answers (2)

Answers (2)

zal_parchem2
Active Contributor
0 Kudos

OK Karl - try this for excluding specific types of activities...you could also add the OUSR table to get the person attending to the activity.  This company had only two personnel, so the name was not required...

Regards - Zal

--K-SR Activities Not Related to Specific Activity Types Ver 1 ZP 2012 02 13

--DESCRIPTION:  SQL lists out those activities which are not associated with specific TYPES on the Activity screen.  Needed to ensure TZC can see all items NOT associated with troubleshoot and only new types are posted.

--AUTHOR(s):
--Version 1 Zal Parchem 13 February 2012

SELECT

T0.CardCode AS 'Cust/Vend Code',
T0.CardName AS 'Cust/Vend Name',
T1.ClgCode AS 'Activity Numb',
T1.AttendUser AS 'Assigned To',
T2.Name AS 'Activity Type'

FROM OCRD T0

LEFT OUTER JOIN OCLG T1
ON T0.CardCode = T1.CardCode

LEFT OUTER JOIN OCLT T2
ON T1.CntctType = T2.Code

WHERE T2.Name <> '[%0]'

ORDER BY

T0.CardName,
T1.ClgCode

FOR BROWSE

Former Member
0 Kudos

Thanks, Zal and Kennedy.  Both of your responses pointed me in the right direction to complete my query. 

zal_parchem2
Active Contributor
0 Kudos

Hello Karl...

A few suggestions...

1.  You have a comma after the first "T0" - please change that comma to a period, and

2.  Instead of an equal sign ("="), try a not equal sign ( " < > ").

Regards - Zal