June 20, 2011 at 3:42 pm
We received data with the positive / negative switch for a money field on the wrong side. I can think of a million and 1 different ways to update this, but one that popped in was using a tally table in place a while loop. This was my first real experiment using a tally table since I first learned about them a little while back, and I seem to have hit a wall.
Data Set:
If Object_ID('dbo.WorkingTable') Is Not Null
Drop Table dbo.WorkingTable
Go
----------
Create Table dbo.WorkingTable
(AccountBalance Money,
PosNegSwitch Varchar(5))
Go
----------
Insert Into dbo.WorkingTable
(AccountBalance,
PosNegSwitch)
Select 5640,'+'
Union All
Select 50680,'+'
Union All
Select 6548,'+'
Union All
Select 540,'+'
Union All
Select 5608,'+'
Union All
Select 6054,'+'
Union All
Select 608,'+'
Union All
Select 123,'+'
Union All
Select 535686,'+'
Union All
Select 65857,'+'
Union All
Select 534153,'+'
Union All
Select 5050,'+'
Union All
Select 654,'+'
Union All
Select 583,'+'
Union All
Select 5408,'+'
Union All
Select 5406,'+'
Union All
Select 5063,'+'
Union All
Select 36056,'+'
Union All
Select 50,'+'
Union All
Select 5640,'+'
Union All
Select 50680,'+'
Union All
Select 6548,'+'
Union All
Select 540,'+'
Union All
Select 5608,'+'
Union All
Select 6054,'+'
Union All
Select 608,'+'
Union All
Select 123,'+'
Union All
Select 535686,'+'
Union All
Select 65857,'+'
Union All
Select 534153,'+'
Union All
Select 5050,'+'
Union All
Select 654,'+'
Union All
Select 583,'+'
Union All
Select 5408,'+'
Union All
Select 5406,'+'
Union All
Select 5063,'+'
Union All
Select 36056,'+'
Union All
Select 50,'+'
Union All
Select 5640,'+'
Union All
Select 50680,'+'
Union All
Select 6548,'+'
Union All
Select 540,'+'
Union All
Select 5608,'+'
Union All
Select 6054,'+'
Union All
Select 608,'+'
Union All
Select 123,'+'
Union All
Select 535686,'+'
Union All
Select 65857,'+'
Union All
Select 534153,'+'
Union All
Select 5050,'+'
Union All
Select 654,'-'
Union All
Select 583,'+'
Union All
Select 5408,'+'
Union All
Select 5406,'+'
Union All
Select 5063,'-'
Union All
Select 36056,'+'
Union All
Select 50,'+'
Union All
Select 5640,'+'
Union All
Select 50680,'+'
Union All
Select 6548,'-'
Union All
Select 540,'+'
Union All
Select 5608,'+'
Union All
Select 6054,'+'
Union All
Select 608,'+'
Union All
Select 123,'+'
Union All
Select 535686,'+'
Union All
Select 65857,'+'
Union All
Select 534153,'+'
Union All
Select 5050,'+'
Union All
Select 654,'+'
Union All
Select 583,'-'
Union All
Select 5408,'+'
Union All
Select 5406,'+'
Union All
Select 5063,'+'
Union All
Select 36056,'+'
Union All
Select 50,'+'
Union All
Select 5640,'+'
Union All
Select 50680,'+'
Union All
Select 6548,'+'
Union All
Select 540,'+'
Union All
Select 5608,'-'
Union All
Select 6054,'-'
Union All
Select 608,'+'
Union All
Select 123,'+'
Union All
Select 535686,'+'
Union All
Select 65857,'+'
Union All
Select 534153,'-'
Union All
Select 5050,'+'
Union All
Select 654,'+'
Union All
Select 583,'+'
Union All
Select 5408,'+'
Union All
Select 5406,'+'
Union All
Select 5063,'-'
Union All
Select 36056,'+'
Union All
Select 5640,'+'
Union All
Select 50680,'+'
Union All
Select 6548,'+'
Union All
Select 540,'+'
Union All
Select 5608,'-'
Union All
Select 6054,'+'
My final goal is to run this formula on each line: AccountBalance * (@PosNegSwitch)1
I started (maybe the wrong way, but I was making this up as I went along) by adding dbo.Tally, and I was going to add and update a PosNegSwitch field on the tally table as well. That is where the issue popped up. I'm so used to the While loop logic that I can't "see" how the data should link to execute the update.
Here is the code I have so far:
If Object_ID('dbo.Tally') Is Not Null
Drop Table dbo.Tally
Select Top 11000
Identity(Int,1,1) As N
Into dbo.Tally
From Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
Alter Table dbo.Tally
Add Constraint PK_Tally_N Primary Key Clustered (N) With FillFactor=100
Grant Select,References On dbo.Tally To Public
Go
Select *
From dbo.Tally
------------------------------
Alter Table dbo.WorkingTable
Add ID_Field Int
Identity(1,1)
Primary Key
--------------------
Alter Table dbo.Tally
Add PosNegSwitch Varchar(5)
Go
------------------------------
Select N,
(Select PIS.PosNegSwitch
From dbo.WorkingTable PIS Inner Join dbo.Tally T
On PIS.ID_Field=T.N)
From dbo.Tally
Order By N
If you run this, you'll see it errors saying:
Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Any thoughts or advice?
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
June 20, 2011 at 3:53 pm
Errrrm, not sure why you'd want a while loop/Tally table at all here.
This code will return what you need:
select
( AccountBalance * CONVERT( Money, PosNegSwitch + '1')) AS AcctBal
FROM
#WorkingTable
TallyTable/WhileLoop is usually used when you either need to A) iterate through a field finding multiple locations or B) need to create multiple rows from a single row.
This is neither of those cases, or I'm completely misunderstanding the requirements.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 20, 2011 at 3:56 pm
Yeah, this is one that is more of a curiosity since I haven't used the tally table logic before. I was able to get my data to work with:
Update dbo.Working_PIS
Set AccountBalance=PositiveNegativeSwitch + AccountBalance
----------
Update dbo.Working_PIS
Set AccountBalance=Replace(AccountBalance,'+','')
--------------------
Update dbo.Working_TRN
Set Monarch_TrnAmount=PositiveNegativeSwitch + Monarch_TrnAmount
----------
Update dbo.Working_TRN
Set Monarch_TrnAmount=Replace(Monarch_TrnAmount,'+','')
Go
--------------------
Alter Table dbo.Working_PIS
Drop Column PositiveNegativeSwitch
----------
Alter Table dbo.Working_TRN
Drop Column PositiveNegativeSwitch
Go
I'm more curious how to get the logic to work in something like this. It is frustrating me because I can't think of it yet.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
June 20, 2011 at 3:59 pm
jarid.lawson (6/20/2011)
I'm more curious how to get the logic to work in something like this. It is frustrating me because I can't think of it yet.
Why not just:
UPDATE Working_PIS SET AccountBalance = ( AccountBalance * CONVERT( Money, PositiveNegativeSwitch + '1'))
UPDATE Working_TRN SET Monarch_TRNAmount = ( Monarch_TRNAmount * CONVERT( Money, PositiveNegativeSwitch + '1'))
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 20, 2011 at 4:15 pm
Craig Farrell (6/20/2011)
Why not just:UPDATE Working_PIS SET AccountBalance = ( AccountBalance * CONVERT( Money, PositiveNegativeSwitch + '1'))
UPDATE Working_TRN SET Monarch_TRNAmount = ( Monarch_TRNAmount * CONVERT( Money, PositiveNegativeSwitch + '1'))
First, in my data all of the fields are required to be set as Varchar. That of course would be easy to get around with:
Update dbo.Working_PIS
Set AccountBalance=(Cast(AccountBalance As Money) * Convert(Money,PositiveNegativeSwitch + '1'))
etc.
The question I'm trying to understand is how to use tally table logic. Not specifically to solve this problem, but if this problem could be solved with it, then I'll understand the logic a little better for use in the future. Like I say, this is purely for educational reasons.
ADHD topic hop...the code that works for you vs. the working code I came up with is a great example why fields should be set to their correct data type instead of all being set to Varchar. I have dealt with this so much that I don't think of the data as anything but string format. I don't get a say in what format it is in so I have to deal with it as is, but for those in a position to make these choices, save yourself and others the headache and pick the correct data types.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
June 20, 2011 at 5:07 pm
jarid.lawson (6/20/2011)
The question I'm trying to understand is how to use tally table logic. Not specifically to solve this problem, but if this problem could be solved with it, then I'll understand the logic a little better for use in the future. Like I say, this is purely for educational reasons.
Even for purely educational reasons, there's no application of Tally Table here. You have no reason to use a row iterator. Understand the power of the TallyTable is in the CrossJoin application forcing it to multi-row, using the numeric as the @C counter in the while iterator loop, nothing more.
I can't see any form of application of it here.
ADHD topic hop...the code that works for you vs. the working code I came up with is a great example why fields should be set to their correct data type instead of all being set to Varchar.
Can't argue that...
I have dealt with this so much that I don't think of the data as anything but string format. I don't get a say in what format it is in so I have to deal with it as is, but for those in a position to make these choices, save yourself and others the headache and pick the correct data types.
Ouch.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply