Combining "And" with "Or"

[tnayfeh]

We have just upgraded to 10g. I have a query that has been working fine until the upgrade, and I have narrowed down the problem to the combination of "AND" and "OR" in my WHERE clause. The problem is that one column (rate_mult) is returning the wrong rate multiple for all records and therefore throwing off all the currency conversions. If I seperate the query with a "UNION ALL" the rates returned are correct. Something else I noticed, if I seperate the query into individual accounting periods, again the rate returned is correct.

Are there any bugs when combining AND and OR? Like I said, this query hasn't changed in years. Very odd.

select T1.BUSINESS_UNIT, T1.OPERATING_UNIT , T1.ACCOUNT, T1.PRODUCT , 
T1.DEPTID , T1.CHARTFIELD1, T1.CHARTFIELD2, T1.CHARTFIELD3, 
T1.FISCAL_YEAR , T1.ACCOUNTING_PERIOD , T1.POSTED_BASE_AMT *1, 
T1.POSTED_BASE_AMT*T2.RATE_MULT , T2.RATE_MULT 
from SYSADM.PS_TDS_COG_RATE_VW T2, SYSADM.PS_TDS_COG_MTD_VW T1
where T2.FROM_CUR=T1.BASE_CURRENCY 
and T2.FISCAL_YEAR=T1.FISCAL_YEAR 
and T2.ACCOUNTING_PERIOD=T1.ACCOUNTING_PERIOD 
[b] and (T1.TDS_CAL_BASE_CUR='CAD' OR (T1.BUSINESS_UNIT in ('04473','04470'))) [/b] 
and T1.LEDGER in ('MTD_ADB', 'ADBMTDCONS') 
and T1.FISCAL_YEAR = 2006 AND T1.ACCOUNTING_PERIOD <=11

Thanks,
TN

 

[SimonSellick]

You haven't got any NULL values in t1.tds_cal_base_cur, have you?

Also, the query looks as though it must have changed recently - there's a literal of 2006 in the last line - or is it generated automatically?

 

[tnayfeh]

Nope, no nulls in t1.tds_cal_base_cur. The query's has not changed since the beginning of the year and was working fine until after the upgrade.

What's weird is that sometimes the query returns the correct values for rate_mult while other times it applies the same rate_mult for all periods. When I split up the accounting_period <=11 to individual accounting periods with a "UNION ALL", each period returns the correct rate.

This is very odd.

TN

 

[Dagon]

It couldn't be something to do with accounting_period being a varchar2 rather than a number ? You might get problems with data type conversion and comparison semantics.

 

[BJCooperIT]

Good point Dagon. You would experience problems if T1.BUSINESS_UNIT is a numeric column too.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[tnayfeh]

Thanks guys, I'll check it out. I'll try the conversion functions on those fields. Or do you guys have any other suggestions on how the query should be modified?

Thanks again!
TN

 

[BJCooperIT]

PS_TDS_COG_RATE_VW T2 and PS_TDS_COG_MTD_VW appear to be views. Is it possible that either of these views were modified during your 10g upgrade?

 

[tnayfeh]

Believe me BJCooperIT, they weren't touched at all.

I added a to_char(T1.BUSINESS_UNIT) = ... in the where clause and ran the query several times which returned the correct results. I'm going to run it throughout the day to see if the results change. The original query worked at times but not always.

TN