How to combine 'where' and 'where not' in load statement?

[Suus]

I am 'simply' trying to combine a 'where not' and a 'where' clause in one load statement, and can not get it done.

This works fine:

SELECT *
FROM "GAC_MSCRM".dbo.FilteredActivityPointer
WHERE NOT ([regardingobjecttypecode]='112');

This works fine too:

SELECT *
FROM "GAC_MSCRM".dbo.FilteredActivityPointer
WHERE ([modifiedon] > '20140101');

If I try to combine the 2 with 'and' or 'or' it does not work.

Let alone adding a 3rd 'where not' clause:

WHERE NOT (activitytypecodename = 'Serviceactiviteit');

Hope you can help putting all 3 where clauses in...

Thanks!

[#2]

Hi suzan

i don't see any reason why you shouldn't combine these Where clause UNLESS they give conflicting results

best regards

Chris

[#3]

Christianj is right, you should be able to make the statements.

Take a look at your data and see if there are conflicts, perhaps there is a more optimal way to write your script

[#4]

SELECT *

FROM "GAC_MSCRM".dbo.FilteredActivityPointer

WHERE ([modifiedon] > '20140101')
AND
WHERE NOT ([regardingobjecttypecode]='112');

That should work, but another option might be to load one table with the one where clause and then do a resident load from that table with the where not.

[jonathandienst · Accepted solution]

Hi

Is this what you are after?

SQL SELECT *

FROM GAC_MSCRM.dbo.FilteredActivityPointer

WHERE (regardingobjecttypecode <> '112')

  AND (modifiedon > '20140101')

  AND (activitytypecodename <> 'Serviceactiviteit');

HTH

Jonathan

[Suus]

Hi,

This worked perfectly.

Follow up question. I want to add more 'not in' values.

So something like

WHERE(regardingobjecttypecode <> '112', '3, '23')

Tried it with 'OR' , but did not work.'WILDMATCH' does not work either.

The 2 AND lines should remain active.

Anyone?

SQL SELECT *

FROM GAC_MSCRM.dbo.FilteredActivityPointer

WHERE (regardingobjecttypecode <> '112') << ???

  AND (modifiedon > '20140101')

  AND (activitytypecodename <> 'Serviceactiviteit');

Regards,

Suzan

[jonathandienst]

Use

regardingobjecttypecode NOT IN (... , .... )

Jonathan

[Suus]

Thank you!

Met vriendelijke groet,

Senior Product Specialist

Suzan Wijnans

GAC Business Solutions

Business Unit Dienstverlening

Mobiel: +31 6 252 799 89