February 28, 2005 at 9:00 am
Hi All,
Following query gives different date results when run in query analyzer and through stored proc.
select convert(char(10),cycle.bill_cycle_start_date,101)bill_cycle_start_date_mdy,
convert(char(10),orig.fixed_price_dollars) orig_rate,
convert(char(10),adj.fixed_price_dollars) adj_rate
from bill_cycle_tbl cycle,
fixed_price_tbl orig,
fixed_price_tbl adj
where cycle.bill_cycle_start_date *= orig.bill_cycle_start_date
and cycle.bill_cycle_start_date *= adj.bill_cycle_start_date
and cycle.bill_cycle_type_code = 'B'
and orig.fixed_price_adjust_ind = 'O'
and adj.fixed_price_adjust_ind = 'A'
and orig.BA_id = 'N-STK'
and adj.BA_id = 'N-STK'
Result when run through Query Analyzer
02/16/2003 1111.00 NULL
03/16/2003 2222.00 NULL
04/16/2003 3333.00 NULL
05/16/2003 4444.00 NULL
06/16/2003 5555.00 NULL
Result when run through Stored Proc
02/16/2003 1111.00 NULL
03/16/2003 2222.00 NULL
<missing 04 & 05 / 2003 … see bottom rows>
06/16/2003 5555.00 NULL
..
..
04/16/2003 3333.00 NULL
05/16/2003 4444.00 NULL
Any ideas/thoughts appreciated.
J.V. Mani
February 28, 2005 at 9:39 am
Possibly due to teh "*=" ? From BOL ...
In earlier versions of Microsoft® SQL Server™ 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way. SQL-92 compliant outer joins are specified in the FROM clause and do not result in this ambiguity. Because the SQL-92 syntax is more precise, detailed information about using the old Transact-SQL outer join syntax in the WHERE clause is not included with this release. The syntax may not be supported in a future version of SQL Server. Any statements using the Transact-SQL outer joins should be changed to use the SQL-92 syntax.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply