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?
yep. That’s exactly what functions are for.
Well, urr, how do I do it?
I can probably work it out but I figured I’d get you guys to do it for me 
I’ll go work it out.
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 "
Nope (unless you’re writing in Pro*C)
This works in Oracle
create or replace function hndl( a number, b number ) return number as
begin
return a - b;
end;
This is very bad practice (database level function, it should be in a package).
Then call in a query with parameters.
SQL> select hndl( 42, 22 ) from dual;
HNDL(42,22)
-----------
20
Or more usefully use columns from your query table, something like:
select hndl(BET_AMOUNT, REFUND_AMOUNT)
from LOBSANGS_TABLE
where <<insert-Lobsangs-where-clause>>
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.
Nah I’ve never used access for work stuff.
I’m currently using microsoft server management studio express (for sql server 2005)
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:
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?
I just googled it. It would require a table change. I might have to get my IT manager’s approval for that.
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 am of the opinion that you don’t need a function for this - it’s not that hairy a clause. So that’s one vote on the “nay” side 
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.
Or is sql cleverer than that?
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?
Ahhh…is the WHERE clause going to be different each time you run it? If so, that complicates it.
(if not, just stick it in the SELECT line in the function body)
Yeah the where clause will be different nearly every time I use it.