Archives of the TeradataForum

Message Posted: Thu, 20 May 2004 @ 14:20:52 GMT

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

Hi Folks, Look at this create Table Stt

     CREATE TABLE TMP_ALLOCATED_INTERMEDIATE as
     (
     SELECT tmp.ADID,
            tmp.MarketID,
            tmp.MediaSubTypeID,
            tmp.DateID,
            tmp.DaypartCode,
            tmp.CompanyID,
            tmp.AdvertiserID,
            tmp.DistributorID,
            tmp.ProgramID,
            tmp.CreativeID,
            tmp.ClassificationID,
            tmp.AttributeID,
            tmp.Market_Alloc,
            tmp.natl_spend,
            SUM(1) OVER(PARTITION BY cbd.MarketID, cbd.CompanyID, cbd.DateID, cbd.MediaSubTypeID, cbd.creativeID)
                 as total_clearance, tmp.DMA
     FROM TMP_MRKT_WITH_CLEARANCE_BY_DT cbd,
          (SELECT c.ADID,
              c.MarketID,
              c.MediaSubTypeID,
              c.DateID,
              c.DaypartCode,
              c.CompanyID,
              c.AdvertiserID,
              c.DistributorID,
              c.ProgramID,
              c.CreativeID,
              c.ClassificationID,
              c.AttributeID,
              (c.local_hhld_univ / s.natl_hhld_univ) as Market_Alloc,
              s.natl_spend,
              c.DMA
          FROM TMP_MRKT_WITH_CLEARANCE_BY_DT c
      JOIN TMP_NATL_COMPANY_SPEND_BY_DATE s  on c.DateID = s.DateID
       AND c.CompanyID = s.CompanyID
       AND ((s.MediaSubTypeID = 6 AND c.MediaSubTypeID = 14)
        OR (s.MediaSubTypeID = 5 AND c.MediaSubTypeID = 15)
        OR (s.MediaSubTypeID = 13 AND c.MediaSubTypeID = 16))
       AND c.CreativeID = s.CreativeID
           )tmp
     WHERE cbd.MarketID = tmp.MarketID
     AND cbd.CompanyID = tmp.CompanyID
     AND cbd.DateID = tmp.DateID
     AND cbd.MediaSubTypeID = tmp.MediaSubTypeID --1/7/03 don't add 14 and 16together.
     AND cbd.creativeID = tmp.CreativeID
     )WITH DATA ;

i have c.local_hhld_univ and s.natl_hhld_univ as decimal values with decimal part 18 and no integer part i.e all values are less than < 1 . Even when i tried using CAST(18,18) the resultnat division is 0.What could be the prob!!!!!Help me out folks . Also can anyone explain me how this is able to do SUM without any GROUP BY Clause

thanx & regards,

Prasanna.

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