Archives of the TeradataForum

Message Posted: Fri, 19 Nov 2004 @ 17:20:53 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Need help with SELECT

From:		Anomy Anom

<-- Anonymously Posted: Friday, November 19, 2004 12:17 -->

Hi,

Thanks for your response.

I'm not sure if I clearly understand your request. Are you trying to convert two rows in your source table into one row in your target?

You are right that is what I want to do.

Assuming this is what you are trying to achieve, all you need to do is a self join - or perhaps I'm don't understand your request.

I wonder if the table contains like a billion rows with self join it will give the spool error.

It makes it hard to help unless the question, problem or need is clearly stated. On that note, there are a couple of things that are very confusing in your original question now that I study it more closely.

1) When you outline the desired outputs, do you mean when id2 instead of time when you state "... output like this for time=1"? I can't see how you could get the output you desire for the time values - assuming time is equivalent to timeframe?????

yes time is timeframe, sorry for the typing mistake.

2) How are the timeframe1, timeframe2, camount1 and camount2 values calculated from the input data? At first I thought that timeframe1 and camount1 came from the corresponding values from the input record where id2 is 1. And I thought the timeframe2 and camount2 values come from the input record where the id2 value is 2. However, this doesn't work for the time example.

The records will have the values by id1 amount1 and timeframe1 for each record and I want to group it by the timeframe. If table has billion records possible timeframes are like 200 (Just to give an idea). From the information you have supplied it is difficult to provide a working example.

Anyway on the assumption your question relates to how to convert two rows into one, you can achieve that with a self join which will look something like this:

          select SRC1.id1 as tgtid1, SRC2.id2 as tgtid2,
            SRC1.timeframe as timeframe1, SRC1.amount as camount1,
            SRC2.timeframe as timeframe2, SRC2.amount as camount2
          from source_test SRC1 join source_test SRC2 on
                SRC1.id1 = SRC1.id1 and
                SRC1.timeframe = 1 and
                SRC2.timeframe = 2
          where SRC1.id2 = 2   and /* This is where your time value goes */
            SRC2.id2 = SRC1.id2
           Is there a way to write query so I don't get the spool error.

I rewrote your query as this.

     select SRC1.id1,
        count(SRC1.timeframe),
        sum(zeroifnull(SRC1.camount)),
        sum(zeroifnull(SRC2.camount))
     from source_test SRC1 join source_test SRC2 on
     (
            SRC1.id1 = SRC2.id1 and
            SRC2.timeframe between 1 and 100
     )
     where SRC1.timeframe between 1 and 100
     group by SRC1.id1;

I'll leave it to you to put the actual calculations for timeframe1, timeframe2, camount1 and camount2.

I hope this helps.

Thanks again.


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference