Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2014
    Posts
    1

    Question Unanswered: Calculated column reference in DB2

    I have a table with the columns:date1,name and price. What i want to do is to add 2 columns one right having the minimum and maximum dates of the consecutive dates of the same name.
    I have written the following query that explains the rule:
    select date1,name,price
    case
    when lag(name,1) over(order by date1 ASC,name ASC)=name then lag(minDate,1) over(order by date1 ASC,name ASC)
    else date1
    end as minDate,
    case
    when lag(name,1) over(order by date1 DESC,name DESC)=name then lag(maxDate,1) over(order by date1 DESC,name DESC)
    else date1
    end as maxDate
    from MyTable order by date1 ASC,name ASC
    My problem is that i get an "invalid context for minDate/maxDate" (SQLCODE=-206, SQLSTATE=42703) Why can't i refer to a calculated column? Is there any other way?

  2. #2
    Join Date
    Nov 2014
    Posts
    2

    Thumbs up

    Can you approach it this way?...
    Code:
    select 
      a.name1, a.date, a.price, b.first_date, b.last_date
    from 
      mytable a
      inner join (
        select a2.name1, min(a2.date) as first_date, max(a2.date) as last_date
        from mytable a2 
        group by a2.name1 
      ) as b
      on a.name1 = b.name1

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •