Database

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

09-06-2010
04:39 AM

09-06-2010
04:39 AM

I have a table with the following data

Stn A Stn B Class rate rank

AAA BBB FIRST 0.00 1

AAA BBB SECOND 18.27 2

AAA BBB THIRD 0.00 3

AAA BBB FOURTH 15.40 4

now I need a query which results the following ,

If class is First then I need rate to be coalesce(rate(rank1), rate(rank2), rate(rank3), rate(rank4), 0)

Similarly

If class is Second then I need rate to be coalesce(rate(rank2), rate(rank3), rate(rank4), 0)

for Third Class rate to be coalesce(rate(rank3), rate(rank4), 0)

for Fourth class rate to be coalesce(rate(rank4), 0)

Could you please let me know how to get this result by using the functions in teradata?

Stn A Stn B Class rate rank

AAA BBB FIRST 0.00 1

AAA BBB SECOND 18.27 2

AAA BBB THIRD 0.00 3

AAA BBB FOURTH 15.40 4

now I need a query which results the following ,

If class is First then I need rate to be coalesce(rate(rank1), rate(rank2), rate(rank3), rate(rank4), 0)

Similarly

If class is Second then I need rate to be coalesce(rate(rank2), rate(rank3), rate(rank4), 0)

for Third Class rate to be coalesce(rate(rank3), rate(rank4), 0)

for Fourth class rate to be coalesce(rate(rank4), 0)

Could you please let me know how to get this result by using the functions in teradata?

2 REPLIES

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

09-07-2010
02:53 AM

09-07-2010
02:53 AM

I really don't understand your question, but you can use CASE to return NULL for unwanted values, like:

coalesce(min(case class when 'first' then rate end),

min(case class when 'second' then rate end),

min(case class when 'third' then rate end),

min(case class when 'fourth' then rate end))

You should try to rephrase your question, include DDL/Inserts and tell the exact business rules.

Dieter

coalesce(min(case class when 'first' then rate end),

min(case class when 'second' then rate end),

min(case class when 'third' then rate end),

min(case class when 'fourth' then rate end))

You should try to rephrase your question, include DDL/Inserts and tell the exact business rules.

Dieter

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

09-20-2010
01:44 AM

09-20-2010
01:44 AM

Thanks Dieter for the reply I have solved the query now... I have used two work tables and a Partition by Clause to getthe desired result.. Anyways thanks for ur reply..