where clause order/grouping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

where clause order/grouping

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2010-10-28 : 23:04:31

I'm driving myself nuts,and am having an issue with figuring out the order in which I need to apply an additional where class, right now I have the following for my where clause:

WHERE  m.accountnumber IN (SELECT DISTINCT account_number
                           FROM   imcwharehouse..account_detail
                           WHERE  dd1 IS NOT NULL
                                  AND dd3 IS NOT NULL)
       AND pt.tradeid IS NULL
       AND m.taxcode NOT IN ( '547', '548' )
       AND ( ( md.transactiondescription LIKE 'Accrued Int%'
               AND m.taxcode NOT IN ( '044', '045', '035' ) )
              OR md.transactiondescription NOT LIKE 'Accrued Int%' )

From the result set I get 2000 results, of these 2000, there are 2 records I'm looking to filter out further using this criteria:

and cast(cast(substring(m.UnitsHeld, 16, 1) + substring(m.UnitsHeld, 0, 16) as DECIMAL(18, 3)) / 1000 as DECIMAL (18, 3)) <> 0 and m.taxcode = 651

Having an issue bridging the gap and bring the two together, as soon as I attempt, it eliminate a whole slew of records instead of just the two that match the additional criteria

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-29 : 11:35:50

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2010-10-29 : 12:23:50

My apologizes if I didnt provide enough information as I thought I had, here is the entire select statement:

select m.RecordEntryNumber + '_' + m.accountnumber + '2' as External_ID,
       m.TaxCode + 'M' as TransactionMappingCode,
       m.BankNumber + m.AccountNumber as Portfolio,
       case
         when m.taxcode + 'M' in ( '142M', '144M', '160M', '211M',
                                   '225M', '230M', '231M', '233M',
                                   '234M', '235M', '236M', '237M',
                                   '238M', '239M', '240M', '266M',
                                   '270M', '275M', '645M', '647M',
                                   '649M', '745M', '760M', '650' ) then m.PostingDate
         else COALESCE(replace(m.TradeDate, '00000000', m.SettlementDate), replace(m.SettlementDate, '00000000', m.PostingDate), m.PostingDate)
       end as Effective_Date,
       case
         when m.taxcode + 'M' in ( '142M', '144M', '160M', '211M',
                                   '225M', '230M', '231M', '233M',
                                   '234M', '235M', '236M', '237M',
                                   '238M', '239M', '240M', '266M',
                                   '270M', '275M', '645M', '647M',
                                   '649M', '745M', '760M', '650' ) then m.PostingDate
         else COALESCE(replace(m.TradeDate, '00000000', m.SettlementDate), replace(m.SettlementDate, '00000000', m.PostingDate), m.PostingDate)
       end as TradeDate,
       case
         when m.taxcode + 'M' in ( '142M', '144M', '160M', '211M',
                                   '225M', '230M', '231M', '233M',
                                   '234M', '235M', '236M', '237M',
                                   '238M', '239M', '240M', '266M',
                                   '270M', '275M', '645M', '647M',
                                   '649M', '745M', '760M', '650' ) then m.PostingDate
         else COALESCE(replace(m.SettlementDate, '00000000', m.PostingDate), m.PostingDate)
       end as SettlementDate,
       m.PostingDate,
       /** DH - added CASE statement USD entries  **/
       case
         when m.SecurityNumber is null then '0.00'
         else cast(cast(replace(replace(m.CommissionAmount, '+', ''), '-', '') as DECIMAL(18, 2)) / 100 as DECIMAL (18, 2))
       end CommissionAmount,
       /** DH - added CASE statement USD entries  **/
       case
         when m.SecurityNumber is null then '0.00'
         else cast(replace(replace(m.OtherCosts, '+', ''), '-', '') as DECIMAL(18, 5))
       end SEC_Fee,
       case m.TaxCode
         when '151' then cast(cast(substring(m.UnitsHeld, 16, 1) + substring(m.UnitsHeld, 0, 16) as DECIMAL(18, 3)) / 1000 as DECIMAL (18, 3)) * -1
         else cast(cast(substring(m.UnitsHeld, 16, 1) + substring(m.UnitsHeld, 0, 16) as DECIMAL(18, 3)) / 1000 as DECIMAL (18, 3))
       end as Quantity,
       case
         when m.TaxCode in ( '651', '652', '655', '660',
                             '665', '670', '672', '674',
                             '675', '676', '679', '680', '699' ) then cast(( cast(substring(m.PrincipalCashAmount, 14, 1) + substring(m.PrincipalCashAmount, 0, 14) as DECIMAL(18, 2)) + cast(substring(m.IncomeCashAmount, 14, 1) + substring(m.IncomeCashAmount, 1, 13) as DECIMAL(18, 2)) ) / 100 as DECIMAL(18, 2)) * -1
         else cast(( cast(substring(m.PrincipalCashAmount, 14, 1) + substring(m.PrincipalCashAmount, 0, 14) as DECIMAL(18, 2)) + cast(substring(m.IncomeCashAmount, 14, 1) + substring(m.IncomeCashAmount, 1, 13) as DECIMAL(18, 2)) ) / 100 as DECIMAL(18, 2))
               ----added this peice to cancel out the fee trans
               + cast(( cast(substring(m.PrincipalCashAmount, 14, 1) + substring(m.PrincipalCashAmount, 0, 14) as DECIMAL(18, 2)) + cast(substring(m.IncomeCashAmount, 14, 1) + substring(m.IncomeCashAmount, 1, 13) as DECIMAL(18, 2)) ) / 100 as DECIMAL(18, 2)) * -1
       end as Total_Amount,
       'USD' as Cash_Balance,
       -- isnull(m.SecurityNumber, 'USD')                                                                                                                                                                 as [Security],
       case
         when m.securitynumber in (select cusip
                                   from   PerformMiscSecurities) then 'USD'
         else isnull(m.securitynumber, 'USD')
       end as[Security],
       'USD' as Currency_ID,
       '' as Code,
       '' as Reversal,
       '2' as [Status],
       '' as Backdated,
       '' as AccruedInterest,
       'SETTLED TRANSACTIONS' as transactionsource
from   MIINT..MetavanteTrans m
       left join performtrans pt
         on pt.tradeid = m.UniqueTradeIdNumber
       left join MIINT..MetavanteTransdesc md
         on md.id = m.id
where  m.AccountNumber in (select distinct account_number
                           from   IMCWharehouse..account_detail
                           where  DD1 is not null
                                  and DD3 is not null)
       and pt.tradeid is null
       and m.TaxCode not in ( '547', '548' )
       and ( ( md.TransactionDescription like 'Accrued Int%'
               and m.taxcode not in ( '044', '045', '035' ) )
              or md.transactiondescription not like 'Accrued Int%' )-- or (cast(cast(substring(m.UnitsHeld, 16, 1) + substring(m.UnitsHeld, 0, 16) as DECIMAL(18, 3)) / 1000 as DECIMAL (18, 3)) <> 0 and m.taxcode = 651)   -----added the taxcode on a temp basis, will implement better logic soon DKH 9/24/2010
order  by externalid

From this result set I get 2K records, I want to filter out two more records from the results set, this where condition would do that:

and (cast(cast(substring(m.UnitsHeld, 16, 1) + substring(m.UnitsHeld, 0, 16) as DECIMAL(18, 3)) / 1000 as DECIMAL (18, 3)) <> 0 and m.taxcode = 651)

The problem I'm having is understanding where in the where clause I need to drop this, each time I try, it eliminates more the the expected two records. Please let me know what else I may be able to provide.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-29 : 12:59:25

In you subject title you have grouping, so I thought you might be doing a GROUP BY, which you didn't show. But, since you do not have a group by.. Just a guess as we have no sample data to run against. But, I suspect that you need to move the predicate(s) from the WHERE clause to the ON clause on your join.

You might want to do some searching on the effect of placing your predicte in the WHERE clause verus the ON clause when doing an OUTER join.

Here are a couple of links that might help:
http://www.sqlteam.com/article/additional-criteria-in-the-join-clause
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98481#374748

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2010-10-29 : 13:22:06

sorry it was a poor choice of words in this case for the title description. i was referring to the grouping of the where clause and how it might or is impacting what i'm expect to see.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources