Using Variables inplace of nested Queries

[duke442games]

Hi Gang

I am working on fixing a corrupted database (A yearly proc did not handel the conversion from year to year and now I have to fix the resulting lines in the DB). Most of my fixes I have been able to adjust with simply SET statements or nested queries. To fix one field I need to either make a very complex nested query or find a way to set variables to queries. But first I have to check to confirm that each query is not null.

Please tell me if this is even possible. (or is there a way to initialize to a 0 value and then not subsitute if NULL?)

Here is an example of what I am trying to do:

DECLARE @q4TotRebate int, @sumRebate int, @q4TotRed int, @sumRedeemed int

SET @q4TotRebate = (Select n.TotalRebateAmount
FROM tblSummary as n
WHERE t.AccountNumber = n.AccountNumber
AND Quarter = '2006/Q4')
SET @sumRebate = (SELECT SUM(b.TransactionAmount)
FROM tblTransactionDetail as b
WHERE t.AccountNumber = b.AccountNumber
AND b.TransactionType = 'P'
and b.TransactionDate > '12/31/06'
AND b.TransactionDate < '01/06/07')
SET @q4TotRed = (Select SUM(g.QtrTotalRebateRedeemed)
FROM tblSummary as g
WHERE g.AccountNumber = t.AccountNumber
And Quarter < '2007/Q1')
SET @sumRedeemed = (SELECT SUM(m.TransactionAmount)
FROM tblTransactionDetail as m
WHERE t.AccountNumber = m.AccountNumber
AND m.TransactionType = 'R'
AND m.TransactionDate > '12/31/06'
AND m.TransactionDate < '01/06/07')

SET @q4TotRebate =
CASE
WHEN @q4TotRebate IS Null
THEN 0
WHEN @q4TotRebate IS NOT NULL
THEN @q4TotRebate
END

SET @sumRebate =
CASE
WHEN @sumRebate IS NULL
THEN 0
WHEN @sumRebate IS NOT NULL
THEN @sumRebate
END

SET @q4TotRed =
CASE
WHEN @q4TotRed is NULL
THEN 0
WHEN @q4TotRed IS NOT NULL
THEN @q4TotRed
END

SET @sumRedeemed =
CASE
WHEN @sumRedeemed IS NULL
THEN 0
WHEN @sumRedeemed IS NOT NULL
THEN @sumRedeemed
END

From tblTransactionSummary as t


Update tblTransactionSummary

Set TotalRebate =
CASE
WHEN ((@q4TotRebate + @sumRebate)-(@q4TotRed + @sumRedeemed)) < 0
THEN (TotalRebate - ((@q4TotRebate + @sumRebate)-(@q4TotRed + @sumRedeemed)))
WHEN ((@q4TotRebate + @sumRebate)-(@q4TotRed + @sumRedeemed)) > 0
THEN TotalRebate
END

[iburyak]

Try this:

[PHP]DECLARE @q4TotRebate int, @sumRebate int, @q4TotRed int, @sumRedeemed int

SET @q4TotRebate = ISNULL((Select n.TotalRebateAmount
FROM tblSummary as n
WHERE t.AccountNumber = n.AccountNumber
AND Quarter = '2006/Q4'),0)

SET @sumRebate = ISNULL((SELECT SUM(b.TransactionAmount)
FROM tblTransactionDetail as b
WHERE t.AccountNumber = b.AccountNumber
AND b.TransactionType = 'P'
and b.TransactionDate > '12/31/06'
AND b.TransactionDate < '01/06/07'), 0)

SET @q4TotRed = ISNULL((Select SUM(g.QtrTotalRebateRedeemed)
FROM tblSummary as g
WHERE g.AccountNumber = t.AccountNumber
And Quarter < '2007/Q1'),0)

SET @sumRedeemed = ISNULL((SELECT SUM(m.TransactionAmount)
FROM tblTransactionDetail as m
WHERE t.AccountNumber = m.AccountNumber
AND m.TransactionType = 'R'
AND m.TransactionDate > '12/31/06'
AND m.TransactionDate < '01/06/07'),0)


Update tblTransactionSummary

Set TotalRebate =
CASE
WHEN ((@q4TotRebate + @sumRebate)-(@q4TotRed + @sumRedeemed)) < 0
THEN (TotalRebate - ((@q4TotRebate + @sumRebate)-(@q4TotRed + @sumRedeemed)))
WHEN ((@q4TotRebate + @sumRebate)-(@q4TotRed + @sumRedeemed)) > 0
THEN TotalRebate
END[/PHP]

[duke442games]

This is great, Thanks!

The only problem I am now having (and I had before) is where do I place the "FROM tblTransactionSummary as t" statement? I need to make sure that the account numbers used in the child queries "b, m, n, r" match that of the parent query "t".

Normally the FROM statement goes after the SELECT (or in this case SET) statement. But if I put it at the end, then in initiallizing the variables it does not yet initialized the "t".

The error message is as follows:

"Server: Msg 107, Level, 16 State 2, Line 3
The column prefix 't' does not match with a table name or alias name used in the query"

Please help.

[duke442games]

ok, I was setting the variables early- I think... (it now works, but now 0 rows are affected- may be a logical operator)


The code needs to appear as follows:


DECLARE @q4TotRebate int, @sumRebate int, @q4TotRed int, @sumRedeemed int

Update tblTransactionSummary

Set TotalRebate =
CASE
WHEN ((@q4TotRebate + @sumRebate)-(@q4TotRed + @sumRedeemed)) < 0
THEN (TotalRebate - ((@q4TotRebate + @sumRebate)-(@q4TotRed + @sumRedeemed)))
WHEN ((@q4TotRebate + @sumRebate)-(@q4TotRed + @sumRedeemed)) > 0
THEN TotalRebate
END

From tblTransactionSummary as t
WHERE

@q4TotRebate = ISNULL((Select n.TotalRebateAmount
FROM tblSummary as n
WHERE t.AccountNumber = n.AccountNumber
AND Quarter = '2006/Q4'),0)
AND
@sumRebate = ISNULL((SELECT SUM(b.TransactionAmount)
FROM tblTransactionDetail as b
WHERE t.AccountNumber = b.AccountNumber
AND b.TransactionType = 'P'
and b.TransactionDate > '12/31/06'
AND b.TransactionDate < '01/06/07'), 0)
AND
@q4TotRed =ISNULL((Select SUM(g.QtrRebateRedeemed)
FROM tblSummary as g
WHERE g.AccountNumber = t.AccountNumber
And Quarter < '2007/Q1'),0)
AND
@sumRedeemed = ISNULL((SELECT SUM(m.TransactionAmount)
FROM tblTransactionDetail as m
WHERE t.AccountNumber = m.AccountNumber
AND m.TransactionType = 'R'
AND m.TransactionDate > '12/31/06'
AND m.TransactionDate < '01/06/07'),0)

[iburyak]

Your update should look like this. I might made some mistakes with () and you should fix it. But main idea is to replace variables with actual statements.


[PHP]Update tblTransactionSummary

Set TotalRebate =
CASE
WHEN ((ISNULL((Select n.TotalRebateAmount FROM tblSummary as n
WHERE t.AccountNumber = n.AccountNumber AND Quarter = '2006/Q4'),0) +
ISNULL((SELECT SUM(b.TransactionAmount) FROM tblTransactionDetail as b
WHERE t.AccountNumber = b.AccountNumber AND b.TransactionType = 'P' and b.TransactionDate > '12/31/06'
AND b.TransactionDate < '01/06/07'), 0)
)-
(ISNULL((Select SUM(g.QtrRebateRedeemed) FROM tblSummary as g
WHERE g.AccountNumber = t.AccountNumber And Quarter < '2007/Q1'),0) +
ISNULL((SELECT SUM(m.TransactionAmount) FROM tblTransactionDetail as m
WHERE t.AccountNumber = m.AccountNumber AND m.TransactionType = 'R' AND m.TransactionDate > '12/31/06'
AND m.TransactionDate < '01/06/07'),0))
) < 0
THEN (TotalRebate - ((ISNULL((Select n.TotalRebateAmount FROM tblSummary as n
WHERE t.AccountNumber = n.AccountNumber AND Quarter = '2006/Q4'),0) +
ISNULL((SELECT SUM(b.TransactionAmount) FROM tblTransactionDetail as b
WHERE t.AccountNumber = b.AccountNumber AND b.TransactionType = 'P' and b.TransactionDate > '12/31/06'
AND b.TransactionDate < '01/06/07'), 0))-
(ISNULL((Select SUM(g.QtrRebateRedeemed) FROM tblSummary as g
WHERE g.AccountNumber = t.AccountNumber And Quarter < '2007/Q1'),0) +
ISNULL((SELECT SUM(m.TransactionAmount) FROM tblTransactionDetail as m
WHERE t.AccountNumber = m.AccountNumber AND m.TransactionType = 'R' AND m.TransactionDate > '12/31/06'
AND m.TransactionDate < '01/06/07'),0))
)
)
WHEN ((ISNULL((Select n.TotalRebateAmount FROM tblSummary as n
WHERE t.AccountNumber = n.AccountNumber AND Quarter = '2006/Q4'),0) +
ISNULL((SELECT SUM(b.TransactionAmount) FROM tblTransactionDetail as b
WHERE t.AccountNumber = b.AccountNumber AND b.TransactionType = 'P' and b.TransactionDate > '12/31/06'
AND b.TransactionDate < '01/06/07'), 0))-

(ISNULL((Select SUM(g.QtrRebateRedeemed) FROM tblSummary as g
WHERE g.AccountNumber = t.AccountNumber And Quarter < '2007/Q1'),0) +
@sumRedeemed)
) > 0
THEN TotalRebate
END

From tblTransactionSummary as t[/PHP]

Good Luck.

[duke442games]

With a little tweeking of the (), this works PERFECT. Thanks so much!

[iburyak]

I saw it had a problem and couldn't fix it because didn't have data to update and test ... :)