I am often having to do “select sum(cast(total_bet_amount as real) - cast(refund_amt as real)) as handle from…”
As you can see that bit in bold is a bit of a mouthfull. Would it be possible to write a function to do this, so in my future sql statements I can type “select hndl() from…” instead?
I get errors about unknown column names, so I wouldn’t be able to do it without making it a COMPLETE sql statement - which in turn stops it doing what I want it to do… (be part of an sql statement)
is it possible to create it like a #define in c? In other words the sql engine will find the word ‘hndl()’ and replace it with "sum(cast(total_bet_amount as real) - cast(refund_amt as real)) as handle "
Lobsang, are you still working in Access? I can tell you how to set up a function in either Access or MS-SQL, but I’d need to know which you were using.
I didn’t see a mention of what database system you are using (I may have missed it), but for SQL Server:
create function TestFunc
(@bet_amt varchar(15), @refund_amt varchar(15))
returns real
as
begin
declare @return real
set @return = cast(@bet_amt as real) - cast(@refund_amt as real)
return @return
end
My assumption is that you’re passing some sort of character value, since you used a CAST in your original example.
I want not to need to pass any values, since the same two fields are ALWAYS the same in this long string.
Sorry if that seemed snarky. Your code would probably work. It defeats the object a bit, but not totally. The object being not having to type all that long string just to get the handle (which is by far the most common bit of information I ever get)
If sql doesn’t have any equivalent of C’s #define feature then I guess I’ll live with it
With SQL server, I don’t think that you can get this as a true function without passing any values to it. If:
you want to be a bit clever,
total_bet_amount and refund_amt are fields within the same table, and
that field has a unique key,
then you could make the function take just one value, @key, and include lines of code declaring the parameters from Raza’s version as temporary variables, then do
select @bet_amt as total_bet_amount, @refund_amt = refund_amt from Lobsangtable where primarykey = @key
Alternately… you could try putting this expression in as a computed column, (also known as a formula column) in the table, or as an expression field in a view. This would allow you to access it as if it were an ordinary database field, without even needing to call the function.
While I’m at it - what datatype are the source fields and why do you constantly need to cast them into reals, if this is the most common bit of information you ever get? Is it possible that one of them will fail to convert to real? Why can’t the table fields be real in the first place? What do we mean by ‘reality’ anyways?
I’ll second ChrisK’s suggestion of a view, now that I know more about what you want.
And I agree that some sort of #DEFINE would be cool, but it’s just one more of those things that are in full-featured languages that are absent from T-SQL. But in fairness, they don’t work the same (T-SQL batches/stored procedures aren’t a compile once sort of affair), so something like a #DEFINE is probably incompatible on several levels.
What we need is a good built-in Proper() function.
They come from a table where nearly every field is a varchar data type. It was designed by somebody else, and it is populated by a pipe-delimited text file that comes from another system. It was badly designed. But it was designed as a ‘temporary measure’ and then it became permanent.
Would the ‘computed column’ become an actual column in the table? In other words would it require a modification of the table itself?
If you added a computed column to the table, it would require modification of the table to add a new column.
What would probably work better is to create a new view that selected whatever columns of the table you needed, plus your computed column. Then you use the view rather than the underlying table for your queries. You could also do all of your datatype conversions in the view, rather than in your TSQL code.
chrisk’s other suggestion would also work, to send through only the key field of your table and calculate from there in the function. Although it doesn’t seem like it would be that much harder to just use the function properly and send through your two fields that need to be calculated.
“select hndl(total_bet_amt, refund_amt)” isn’t that much longer than “select hndl(keyfield)”.
I like the idea of creating a view. Would doing it that way be computationally expensive? Because I’m guessing the view would be roughly a (select *,new_computed_column) from table which would be a truly massive query (16,081,502 records), on which to run a smaller one.
so… say, select handle from new_view where date = 08/11/16 where new_view is the view…
wouldn’t that be like running this… *select handle from (select ,sum(cast(total_bet_amount)-cast(refund_amt)) as handle from orig_table) where date = 08/11/16
two queries, one of which is a MASSIVE result set.
I’ve read through this thread a couple of times, and maybe I haven’t had enough caffeine, but I can’t figure out what’s wrong with something simple like this:
CREATE FUNCTION TestFunc
()
RETURNS Int
AS
BEGIN
DECLARE @Result int
SELECT @Result = sum(cast(total_bet_amount as real) - cast(refund_amt as real)) FROM LobsangsMagicTable
RETURN @Result
END
GO
Usage:
SELECT dbo.TestFunc()
Unless you need to select from different tables every time you run it. That would complicate things.
I’ll try that, but wouldn’t it just select the sum based on ALL the records (because there are no where clauses) regardless of what the where clause is in the sql statement the function is being called from?