Analytics

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-26-2010
10:56 AM

10-26-2010
10:56 AM

Temporary variable store in SQL

Hi,

Is there any way to let SQL aware of a temporary variable which value varies from record to record? The job needs to be done is following:

Suppose C1 and C2 are two columns in the table, a1 and a2 are two constants. f1(x) to fn(x) is n transformation functions. want to get:

select

sum(f1(C1*a1+C2*a2)),

sum(f2(C1*a1+C2*a2)),

...

sum(fn(C1*a1+C2*a2))

Is there any way that SQL can first remember a variable y=C1*a1+C2*a2 for each record, then directly use this y to do the select:

sum(f1(y)),

sum(f2(y)),

...

sum(fn(y))

I know one way to do this is by a temporary table. But does Teradata has such build-in functionality to calculate y first, then do the aggregation on the fly?

Thanks!

Is there any way to let SQL aware of a temporary variable which value varies from record to record? The job needs to be done is following:

Suppose C1 and C2 are two columns in the table, a1 and a2 are two constants. f1(x) to fn(x) is n transformation functions. want to get:

select

sum(f1(C1*a1+C2*a2)),

sum(f2(C1*a1+C2*a2)),

...

sum(fn(C1*a1+C2*a2))

Is there any way that SQL can first remember a variable y=C1*a1+C2*a2 for each record, then directly use this y to do the select:

sum(f1(y)),

sum(f2(y)),

...

sum(fn(y))

I know one way to do this is by a temporary table. But does Teradata has such build-in functionality to calculate y first, then do the aggregation on the fly?

Thanks!

1 REPLY

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-27-2010
03:46 PM

10-27-2010
03:46 PM

Re: Temporary variable store in SQL

Not sure if this is what you are looking for, but you could create a view and define y as C1*a1+C2*a2 and then just reference your view in the select. If you are looking to keep changing y then you just change your view to the new formula.