Help Debugging a Query... - 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)

Help Debugging a Query...

Author

Topic

smithygreg
Starting Member

37 Posts

Posted - 2007-02-21 : 12:19:31

Hey there...I am trying to debug a stored procedure created by someone else and I was hoping someone here could explain the syntax of this statement to me...

Update something set something = somethingelse where
(table1.field1= 0 or table1.field1 & table2.field2 != 0)

I suppose I am having trouble with operator precedence here...
How do I evaluate the "And" and "Or"

Thanks in advance...I will probably be posting a question about the 15 join query I am bumping up against after I get this one debugged...

Kristen
Test

22859 Posts

Posted - 2007-02-21 : 12:36:16

"How do I evaluate the "And" and "Or""

Well, I personally would put brackets around them so that the intended precedence was clear!

If it really is "&", rather than "AND", then that is a bitwise operator ... and I'm not sure that the query would even compile!

If you want further help you'll need to provide the actual syntax - your approximation of it above disguises too much of the original to be able to work. For example there is nothing to show how table1 or table2 are referenced

Kristen

smithygreg
Starting Member

37 Posts

Posted - 2007-02-21 : 12:44:52

Sorry..I was just trying to simplify it...
Here is the full query...


update TransactionCalculation
set

TierIncentiveServiceID = 
(
select top 1 ins.ID 
from IncentiveService ins
join TierIncentiveSet s on s.IncentiveServiceID = ins.ID
join Entry e on s.EntryID = e.ID
where 
ins.ServiceID = 22
and
e.AgreementID = 965
and
(ins.BillOptionFlags = 0 or ins.BillOptionFlags & isnull(t.BillOption,1) != 0)
and
ins.Residential & isnull(t.Residential, 1) != 0
)
from TransactionCalculation c
join TransactionData t on t.ID = c.TransactionID
join Calculation calc on calc.ID = c.CalculationID
where c.ImportID = @ImportID and c.CalculationID = @CalculationID

I am having trouble figuring out how the billoptionflags clause gets evaluated and also how the residential clause is evaluated...

I didn't write this procedure, but it isn't working correctly and it is up to me to debug it...Of course I always use parentheses..

Thanks again for your help!

Kristen
Test

22859 Posts

Posted - 2007-02-21 : 14:09:35

"Of course I always use parentheses.."

"it isn't working correctly"

I'm really suspicious of:


UPDATE TransactionCalculation
...
FROM TransactionCalculation as c

I think there is a chance that this will update EVERY row in TransactionCalculation, ignoring the FROM clause's "Alias" of the table to "c"

I suggest you change it to:


UPDATE c

I think the "SELECT TOP 1" is pretty crappy too. I suspect that there is only ever one row, and the "TOP 1" has been put in as a Belt & Braces, but the problem is that if there is MORE than one matching row this update will select its choice at random with probably, at worst, disastrous consequences and at best "unrepeatable behavior" which will make fixing it a major problem.

I think the "&" is OK, but I still would have parenthesised it. It does appear to be a bitwise OR that they are trying to use:


AND
(
       ins.BillOptionFlags = 0 
    OR (ins.BillOptionFlags & isnull(t.BillOption, 1) != 0)
)
AND (ins.Residential & isnull(t.Residential, 1) != 0)

so ins.BillOptionFlags is zero, or ins.BillOptionFlags shares at least one bit with t.BillOption (and if t.BillOption happens to be NULL then the first bit of ins.BillOptionFlags must be set)

It would probably be better to have separate BIT fields, instead of a bitmap field, but ...

AND if that criteria is met then:

ins.Residential must have at least one bit matching t.Residential unless t.Residential is NULL in which case ins.Residential must have its first bit set.

The style the code is written in is pretty shocking, which is always a sign of a rushed or messy implementation, IME.

It might be that it is indented and looks pretty, but I expect your Cut&Paste would have included that ... so I'll assume it isn't.

The JOIN styles chop and change:

join TierIncentiveSet s on s.IncentiveServiceID = ins.ID
join Entry e on s.EntryID = e.ID

For the first one TierIncentiveSet's column is on the Left of the ON phrase.
For the second Entry's column is on the Right ...

Anyway, try the "UPDATE c" thingie first.

To debug it I would put a SELECT statement instead of the "UPDATE TransactionCalculation SET" and see what sort of stuff you are getting back.

Kristen

smithygreg
Starting Member

37 Posts

Posted - 2007-02-21 : 14:40:51

Thanks Kristen!
I had changed the Update to a Select and I was getting nothing back and thats what alerted me to the problem...
I think I discovered the problem though and it actually came down to faulty user input.
As far as bitwise operations go, I am a bit of a noob when it comes to them..Anybody out there know where I can find a good tutorial?

But thanks again..I am always amazed at how helpful people are in these forums. I hope one day I can help people out too!

Kristen
Test

22859 Posts

Posted - 2007-02-21 : 14:44:39

If you have two bit-values:


      100
      101

AND = 100 - a "1" in each position where BOTH have a 1
 OR = 101 - a "1" in each position where EITHER has a 1

you do have to do it in Binary though!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources