| Author |
Message |
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
   Posted: 03 Dec 2014 at 10:13am |
|
please make sure your sample includes some nulls that are causing you an issue
|
 IP Logged |
|
rlivermore
Groupie
Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
|
Posted: 03 Dec 2014 at 10:20am |
|
Is this what you're looking for?
acct name, address, city, state, zip, contracttype
Alpha Direct, 8240 E. gelding, Glendale, CA, 11111 Gold Seal
Banner Casa, 2820 W. Kelton, Chicago, IL, 222222, Silver Seal
GMB Law, 1850 N. Central, Miami, FL, 333333,
Plant Solutions, 7255 E. Adobe, Austin, TX, 444444, Gold Seal
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 03 Dec 2014 at 10:24am |
oops I just realized I recommended you to use NOT ISNULL()
try
IsNull({tblContracts.ContractType}) OR {tblContracts.ContractType}<>'whatever you want to exclude here'
Edited by DBlank - 03 Dec 2014 at 10:24am
|
|
IP Logged |
|
rlivermore
Groupie
Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
|
Posted: 03 Dec 2014 at 10:38am |
|
After the adjustment it's displaying every record in the database minus the contractype=cloudit, it seems to be excluding all of the other formulas before it now.
{tblServiceOrders.DateReceived} > DateTime (2010, 11, 01, 00, 00, 00) and
{tblServiceOrders.SOType} in ["AMC", "Service"] and
{tblServiceOrders.Status} in ["Closed", "Ready for Invoicing (AR)", "Soft Close (CS)"] and
{tblServiceOrders.DateClosed} > DateTime (2014, 11, 29, 00, 00, 00) and
IsNull({tblContracts.ContractType}) or
{tblContracts.ContractType} <> "CloudIT"
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 03 Dec 2014 at 11:03am |
|
{tblServiceOrders.DateReceived} > DateTime (2010, 11, 01, 00, 00, 00) and
{tblServiceOrders.SOType} in ["AMC", "Service"] and
{tblServiceOrders.Status} in ["Closed", "Ready for Invoicing (AR)", "Soft Close (CS)"] and
{tblServiceOrders.DateClosed} > DateTime (2014, 11, 29, 00, 00, 00) and
(IsNull({tblContracts.ContractType}) or
{tblContracts.ContractType} <> "CloudIT" )
|
|
IP Logged |
|
rlivermore
Groupie
Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
|
Posted: 03 Dec 2014 at 11:12am |
|
I added the parens and now its back to square one, only displays data that has a contract type associated with it
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 03 Dec 2014 at 11:29am |
hard to say with your data set but usually once you start throwing date select criteria into the mix it will ruin your intention of the outer join. This was why I had originally indicated that if you had other select criteria you might want to change to the join process.
THe best solution is to move the criteria into the join but you will have to write a command or stored proc or SQL view to do that.
is that an option?
|
|
IP Logged |
|
rlivermore
Groupie
Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
|
Posted: 03 Dec 2014 at 11:32am |
|
I dunno, I've never tried your suggestion
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 03 Dec 2014 at 11:34am |
is your data source sql?
do you have rights to making a view?
Edited by DBlank - 03 Dec 2014 at 11:34am
|
|
IP Logged |
|
rlivermore
Groupie
Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
|
Posted: 03 Dec 2014 at 11:34am |
|
Writing the code doesn't sound like something I'm capable of doing...
|
|
IP Logged |
|
|
|
