Deterministic and Nondeterministic functions

[Willie78]

Hi all

I have a query re Deterministic and Nondeterministic functions. I have read up on them and get the general idea.

I had a formula that ended up being Nondeterministic but i don't understand why this is. Please see below formula

(Transaction_Date / 86400) as int)*86400

Can anyone explain this to me.

Cheers

Paul W

 

[vongrunt]

Formula" as computed column... or function?

What data type is Transaction_date, and can you provide complete syntactically correct code?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Willie78]

This is the code i wads trying to use Transactio_date is an int field

Select dateadd(s,cast((Transaction_Date / 86400) as int)*86400,'01/01/1970') trandate

from aim_t_holding

group by (Transaction_Date / 86400) as int)*86400

In the end i had to do this

Select dateadd(s,cast((Transaction_Date / 86400) as int)*86400,'01/01/1970') trandate
from aim_t_holding
group by dateadd(s,cast((Transaction_Date / 86400) as int)*86400,'01/01/1970')

I'm just looking for an expliantion of why.....

Cheers


Paul W

 

[JamesLean]

Why do you think this has anything to do with deterministic functions? Your original query was just syntactically incorrect. The DATEADD function is deterministic.

Incidentally, if this is your whole query then you can just use SELECT DISTINCT rather than have to specify the whole function again in the GROUP BY clause.

--James

 

[vongrunt]

Implicit conversion of '01/01/1970' is non-deterministic.

I still don't understand where this code is used and how you checked for it's determinism.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Willie78]

The query will end up something like this but with a few other fields something like

Select dateadd(s,cast((Transaction_Date / 86400) as int)*86400,'01/01/1970') trandate,fund_code,source_code,pay_code,sum(tran_amt_base)

from aim_t_holding


group by dateadd(s,cast((Transaction_Date / 86400) as int)*86400,'01/01/1970'),fund_code,source_code,pay_code

My assumption was that it was to do with Determinism. Maybe it was just my code

cheers for your help anyway

Paul W