how to write this query?

Liquidloop

Hi,

I am having trouble writing a query,

My query calculates discount using some fields of a table, like,

(CASE WHEN [cLineDiscTag] = '2' THEN
(CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN
(nTrnsQty * nLineDisc1) / [nReceiptsCurrConRate]
ELSE
(nTrnsQty * nLineDisc1) * [nReceiptsCurrConRate]
END)
ELSE
(CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN
(nTrnsQty * nUnitPrice * nLineDisc1 / 100) / [nReceiptsCurrConRate]
ELSE
(nTrnsQty * nUnitPrice * nLineDisc1 / 100) * [nReceiptsCurrConRate]
END)
END) As Discount1

Now in the query at the same level(it has many subqueries), i need to calculate discount2 and discount3, which needs value of discount1. I tried naming the whole table as A, then using this as subquery and so on.. but it makes things complicated.

Is their any other proper method

Please also tell me if calculation can be optimized further.

Thank You.

Kent Waldrop Ap07

If the question you are asking is can you use the alias at other locations instead of re-writing this case statement, the answer is no; TSQL does not support this kind of alias.

As this post says:

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=444539&SiteID=1

You will need to use a CTE or a derived table. You might also want to give a look to:

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=380479&SiteID=1

Arnie Rowland

Consider creating a FUNCTION to calculate the discount, and then use the function in your code.

Kent Waldrop Ap07

If you can create an inline table function to do the job the functon is a reasonable to make the code more readable; however, I would avoid a scalar function because scalar functions can put a lot of drag on a query.

Something like this might work:

create function dbo.discount
( @cLineDiscTag char(1),
@cReceiptsMultiDivFlag char(1),
@nTrnsQty integer,
@nLineDisc1 integer,
@nReceiptsCurrConRate numeric (9,2),
@nUnitPrice numeric (9,2)
)
returns table
as return
( select CASE WHEN @cLineDiscTag = '2' THEN
(CASE WHEN @cReceiptsMultiDivFlag = 'D' THEN
(@nTrnsQty * @nLineDisc1) / @nReceiptsCurrConRate
ELSE
(@nTrnsQty * @nLineDisc1) * @nReceiptsCurrConRate
END)
ELSE
(CASE WHEN @cReceiptsMultiDivFlag = 'D' THEN
(@nTrnsQty * @nUnitPrice * @nLineDisc1 / 100) / @nReceiptsCurrConRate
ELSE
(@nTrnsQty * @nUnitPrice * @nLineDisc1 / 100) * @nReceiptsCurrConRate
END)
END as Discount1
)

Thanks for nudging me Arnie; I'm afraid I kind of fell asleep at the wheel. You have picked me up a couple of times today and I really appreciated it. I am just not fully together today; I don't know why, but I really appreciate you nudging me a couple of times today.

:-)

Kent

how to write this query?

Re: how to write this query?

Re: how to write this query?

Re: how to write this query?