Archives of the TeradataForum

Message Posted: Sat, 05 Feb 2005 @ 12:33:11 GMT

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

mark barlow wrote:

          insert into table_1
          select
          b.id,
          brand,
          case when (substr(offer,1,5) between 'G100' and 'G816' )
           or (offer not like 'G%' and substr (band,1,1) = 'T')
           or (a.code = c.code) then 1
                  else 0
          end,
          case when (substr(offer,1,5) not between 'G100' and 'G816')
           and substr (band,1,1) ne 'T'
           and (a.code ne c.code) then 1
                  else 0
          end
          from table_4 a,
          table_5 b,
          table_6 c
          where a.id = b.id
          and date >= date '2004-01-31'
          group by 1,2,3,4
          ;

There's nothing wrong with your code, so how does it fail?

Btw, isn't the second case just a negation of the first one? Then there are easier ways to write it:

     case when (substr(offer,1,5) between 'G100' and 'G816' )
       or (offer not like 'G%' and substr (band,1,1) = 'T')
       or (a.code = c.code) then 0
              else 1
     end,

or

     case when NOT((substr(offer,1,5) between 'G100' and 'G816' )
       or (offer not like 'G%' and substr (band,1,1) = 'T')
       or (a.code = c.code)) then 1
              else 0
     end,

or

     your_first_case AS XXX,
     case when XXX = 1 then 0 else 1 end

or

     your_first_case AS XXX,
     (XXX + 1) mod 1

Dieter

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