Archives of the TeradataForum

Message Posted: Fri, 12 Apr 2013 @ 10:02:43 GMT

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

<-- Anonymously Posted: Friday, April 12, 2013 03:54 -->

Thanks Mike for your reply. I'm using SQLA version 13.00.0019. Now that none other than you saying that there is no data truncation, I'm in doubt whether this is my query !

My query goes like below; I'm trying to generate a compress statement from a preloaded table which has the data value to be compressed.

     WITH RECURSIVE TEMP (databasename,tablename, columnname,data_rank_nbr , compr_list)  AS
     (
     SELECT     seed. databasename, Seed. tablename, Seed.columnname,
     Seed.data_rank_nbr,
     CASE
        WHEN data_value IS NULL  THEN 'NULL'
        WHEN  col.columntype ='CF'     THEN     ''''|| Seed.data_value ||''''
        WHEN  col.columntype ='DA' THEN CAST(Seed.data_value AS INTEGER)
        ELSE Seed.data_value  END

      FROM UD_GEM_AD.COMP_ANALYSIS Seed,
       DBC.COLUMNS Col
     WHERE  Seed.databasename= col. databasename  AND Seed.tablename= col. tablename  AND
     Seed.columnname = col.columnname AND  Seed.databasename= 'GEM_DDT_BASE'
      AND Seed.tablename= 'MAINTENANCE_EVENT_COST'
      AND Seed.columnname ='COST_AMT'

      AND DATA_RANK_NBR  = 1


     UNION ALL


     SELECT Sub. databasename, Sub. tablename, Sub.columnname,
     Sub.data_rank_nbr,Main.compr_list  || ', ' ||

      CASE
         WHEN data_value IS NULL  THEN 'NULL'
         WHEN  col.columntype ='CF'     THEN     ''''|| Sub.data_value ||''''
         WHEN  col.columntype ='DA' THEN CAST(Sub.data_value AS INTEGER)
         ELSE Sub.data_value  END

      FROM
      UD_GEM_AD.COMP_ANALYSIS Sub ,
      TEMP MAIN,
      DBC.COLUMNS Col
      WHERE sub.databasename= Main. databasename  AND sub.tablename= Main. tablename  AND
     Sub.columnname = Main.columnname  AND sub.databasename= col. databasename  AND
     sub.tablename= col. tablename  AND Sub.columnname = col.columnname  AND
     Sub.databasename='GEM_DDT_BASE'
      AND Sub.tablename= 'MAINTENANCE_EVENT_COST'
      AND Sub.columnname ='COST_AMT'
       AND Sub.Data_rank_nbr = Main.Data_rank_nbr +1
       AND Sub.Data_rank_nbr <= 255
       )
     SEL databasename,tablename, columnname,data_rank_nbr, ('COMPRESS('|| compr_list|| ')' )
     AS list FROM TEMP ORDER BY data_rank_nbr ;

I can see the query build up values like this

     .000000      1   COMPRESS(.000000)
     ?      2   COMPRESS(.000000, NULL)
     37.500000   3   COMPRESS(.000000, NULL, 37.500000)
     15.000000   4   COMPRESS(.000000, NULL, 37.500000, 15.000000)
     7.000000   5   COMPRESS(.000000, NULL, 37.500000, 15.000000,
     7.000000)
     10.000000   6   COMPRESS(.000000, NULL, 37.500000, 15.000000,
     7.000000, 10.000000)
     45.000000   7   COMPRESS(.000000, NULL, 37.500000, 15.000000,
     7.000000, 10.000000, 45.000000)
     20.000000   8   COMPRESS(.000000, NULL, 37.500000, 15.000000,
     7.000000, 10.000000, 45.000000, 20.000000)
     14.000000   9   COMPRESS(.000000, NULL, 37.500000, 15.000000,
     7.000000, 10.000000, 45.000000, 20.000000, 14.000000)
     5.000000   10   COMPRESS(.000000, NULL, 37.500000, 15.000000,
     7.000000, 10.000000, 45.000000, 20.000000, 14.000000, 5.000000)
     22.500000   11   COMPRESS(.000000, NULL, 37.500000, 15.000000,
     7.000000, 10.000000, 45.000000, 20.000000, 14.000000, 5.000000, 22.500000)
     12.000000   12   COMPRESS(.000000, NULL, 37.500000, 15.000000,
     7.000000, 10.000000, 45.000000, 20.000000, 14.000000, 5.000000, 22.500000,
     12.000000)
     40.000000   13   COMPRESS(.000000, NULL, 37.500000, 15.000000,
     7.000000, 10.000000, 45.000000, 20.000000, 14.000000, 5.000000, 22.500000, 12.000000,
     40.000000)
     50.000000   14   COMPRESS(.000000, NULL, 37.500000, 15.000000,
     7.000000, 10.000000, 45.000000, 20.000000, 14.000000, 5.000000, 22.500000, 12.000000,
     40.000000, 50.000000)

But after row number 29 until 255, I see only a fixed set of values in compress o/p text.

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