Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 01 Jul 2003 @ 21:55:33 GMT


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


Subj:   Format with ZZZ.Z-
 
From:   Crisafulli, Ronald

Has anyone encountered any problems using the format of 'zzz.z-' in a Select statement under V2R5. I have a Unix script that initiates a BTEQ session and creates an Export report file. The report file is built from the Select stmt and has sub and grand total control breaks. In V2R4 this script runs with out an incident. Under V2R5 the format 'zzz.z-' is aborting with a 3530 format string error message. I included a copy of the select statement and highlighted the first format statement that caused the error..

There are other format statements that are similar but this one caused the problem. See the STDOUT from the script output run

with '' (title '')
by division.division_nbr

GROUP BY division.division_nbr, dept.dept_nbr, styrcp07.redline_ind
ORDER BY division.division_nbr, dept.dept_nbr, styrcp07.redline_ind
;
 *** Failure 3530 Invalid FORMAT string 'zzz.z-'.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+-
----
----
+---------+---------+---------+---------+---------+-

.export reset;
 *** Output returned to console.

select
       ' |' (title ' %// |// |// |// ~'),
       '   ' (title '___//Dpt//  D////___'),
       ' ' (title '_////e////_'),
       '    ' (title '____//Cls//scri////____'),
       ' ' (title '_////p////_'),
       '        '
        (title '________//Style//tion    ////________'),
       '        '
        (title '________//        //        //        //________'),
       '|U|' (title '?_?//|U|//|%|//|$|//#_#'),
       sum(str_bop_units) (format 'zzz,zzz,zzz-',
        title '____________////In Str //BOP //____________'),
       '|' (title '?//|//|//|//#'),
       sum(lw_sales_units) (format 'zzz,zzz,zzz-',
        title '____________////LW //Sales //____________'),
       ' ' (title '_////////_'),
       cast(sum(str_bop_units) as decimal(11,2))/
        nullifzero(sum(lw_sales_units))
        (format 'zzz.z-', title '______//Unit //BOP //S%S //______'),
 ' ' (title '_////////_'),
 cast(100*sum(lw_sales_units) as decimal(13,2))/
  nullifzero(sum(lw_sales_units) + sum(str_eop_units))
  (format 'zzz.z-', title '______//Sell //Thru //Pct //______'),
 ' ' (title '_////////_'),
 cast(100*sum(lw_sales_units) as decimal(13,2))/
  nullifzero(sum(one_wk_ago_sales_units)) - 100
  (format 'zzz.z-', title '______////Build //Pct //______'),
 '|' (title '?//|//|//|//#'),
 MAX(CASE WHEN styrcp07.redline_ind = 0
          THEN styrcpv20.shop_cnt
          ELSE styrcpv21.shop_cnt END)
   (format 'z,zzz', title '_____//Store//n BOP//n EOP//_____'),
 ' ' (title '_////////_'),
 sum(str_eop_units) (format 'zzz,zzz,zzz-',
  title '____________////In Str //EOP //____________'),
 ' ' (title '_////////_'),
 (CASE WHEN sum(in_transit_units) > 0 THEN
   substr(sum(in_transit_units) (format 'zzz,zzz,zzz'),1, 11) ELSE
   substr(sum(in_transit_units) (format '---,---,--z'),1, 11)
  END)
  (title '___________////         In//    Transit//___________'),
 ' ' (title '_////////_'),
 sum(dc_inv_units) (format 'zzz,zzz,zzz-',
 title '____________////DC //Inv //____________'),
' ' (title '_////////_'),
sum(total_eop_units) (format 'zzz,zzz,zzz-',
 title '____________////Total //On Hand //____________'),
'|' (title '?//|//|//|//#'),
(CASE WHEN sum(total_on_order_units) > 0 THEN
  substr(sum(total_on_order_units) (format 'zzz,zzz,zzz'),1, 11) ELSE
  substr(sum(total_on_order_units) (format '---,---,--z'),1, 11)
 END)
 (title '___________////      Total//   On Order//___________'),
'|' (title '?//|//|//|//#'),
sum(one_wk_ago_sales_units) (format 'zzz,zzz,zzz-',
 title '____________//1Wk //Ago //Sls //____________'),
' ' (title '_////////_'),
sum(two_wk_ago_sales_units) (format 'zzz,zzz,zzz-',
 title '____________//2Wk //Ago //Sls //____________'),' ' (title
'_////////_'),
sum(mtd_sales_units) (format 'zzz,zzz,zzz-',
 title '____________////MTD //Sls //____________'),
' ' (title '_////////_'),
sum(std_sales_units) (format 'zzz,zzz,zzz-',
 title '____________////STD //Sls //____________'),
'|' (title '?//|//~//|//#'),
cast(sum(str_bop_units) as decimal(18,2))/
 nullifzero(MAX((CASE WHEN styrcp07.redline_ind = 0

         THEN styrcpv20.shop_cnt

         ELSE styrcpv21.shop_cnt END)))
   (format 'zz,zzz-')
   ( title '_______//Avg BOP//_______//IMU Pct//_______'),
' ' (title '_////_////_'),substr(cast(sum(lw_sales_units) as decimal(18,2))/nullifzero(
    MAX((CASE WHEN styrcp07.redline_ind = 0

         THEN styrcpv22.shop_cnt

         ELSE styrcpv23.shop_cnt END)))
   (format 'z,zzz-'), 1, 6)
 (title '______//Avg LW//______// Price//______'),
' ' (title '_////_////_'),
substr(cast(sum(str_eop_units) as decimal(18,2))/nullifzero(
    MAX((CASE WHEN styrcp07.redline_ind = 0

         THEN styrcpv22.shop_cnt

         ELSE styrcpv23.shop_cnt END)))
   (format 'zz,zzz-'), 1, 7)  (title '_______//Avg EOP//_______//Sls
AUR//_______'),
 '|\ ' (title '^\//|\//!\//|\//!\'),
 MAX(company.comp_name) (title ''),
 substr(dept.dept_nbr, 1) (title ''),
 substr(division.division_nbr, 1) (title ''),
 MAX(substr(date (format 'yyyy-mm-dd'), 1, 10)) (title ''),
 MAX(substr(time, 1)) (title ''),
 styrcp07.redline_ind
  (title ''),
 MAX((CASE WHEN styrcp07.redline_ind = 0

       THEN 'REGULAR'

       ELSE 'REDLINE' END)) as redline_desc,
 MAX(dept.dept_desc) (title ''),
 MAX(division.division_desc) (title ''), '|' ||
 'DEPT ' || substr(dept.dept_nbr (format 'zzz'), 1, 3) || ' TOTAL '
  (title ''),
 redline_desc (title ''),
 '   |$|' (title ''),
 substr(sum(str_bop_dollars) (format 'zzz,zzz,zzz-'), 1, 12) (title ''),
 '|' (title ''),
 substr(sum(lw_sales_dollars) (format 'zzz,zzz,zzz-'), 1, 12) (title
''),
 ' ' (title ''),
 substr(sum(str_bop_dollars)/nullifzero(sum(lw_sales_dollars))
   (format 'zzz.z-'), 1, 6) (title ''),
 ' ' (title ''),
 substr(100*sum(lw_sales_dollars)/nullifzero(sum(lw_sales_dollars) +
  sum(str_eop_dollars))
  (format 'zzz.z-'), 1, 6) (title ''),
 ' ' (title ''), substr(100*sum(lw_sales_dollars)/
  nullifzero(sum(one_wk_ago_sales_dollars))-100
  (format 'zzz.z-'), 1, 6) (title ''),
 '|' (title ''),
 substr(
 MAX(CASE WHEN styrcp07.redline_ind = 0

          THEN styrcpv22.shop_cnt

          ELSE styrcpv23.shop_cnt END)
  (format 'z,zzz'), 1, 5) (title ''),
 ' ' (title ''),
 substr(sum(str_eop_dollars) (format 'zzz,zzz,zzz-'), 1, 12) (title ''),
 ' ' (title ''),
 (CASE WHEN sum(in_transit_units) > 0 THEN
   substr(sum(in_transit_units) (format 'zzz,zzz,zzz'),1, 11) ELSE
   substr(sum(in_transit_units) (format '---,---,--z'),1, 11)
  END) (title ''),
 ' ' (title ''),
 substr(sum(dc_inv_dollars) (format 'zzz,zzz,zzz-'), 1, 12) (title ''),
 ' ' (title ''),
 substr(sum(total_eop_dollars) (format 'zzz,zzz,zzz-'), 1, 12) (title
''),

 '|' (title ''),
 (CASE WHEN sum(total_on_order_dollars) > 0 THEN
   substr(sum(total_on_order_dollars) (format 'zzz,zzz,zzz'),1, 11) ELSE
   substr(sum(total_on_order_dollars) (format '---,---,--z'),1, 11)
  END)
  (title ''),
 '|' (title ''),
 substr(sum(one_wk_ago_sales_dollars)
  (format 'zzz,zzz,zzz-'), 1, 12) (title ''), ' ' (title ''),
 substr(sum(two_wk_ago_sales_dollars)
  (format 'zzz,zzz,zzz-'), 1, 12) (title ''),
 ' ' (title ''),
 substr(sum(mtd_sales_dollars) (format 'zzz,zzz,zzz-'), 1, 12) (title
''),

 ' ' (title ''),
 substr(sum(std_sales_dollars) (format 'zzz,zzz,zzz-'), 1, 12) (title
''),

 '|' ||
 substr(zeroifnull(100 - 100*sum(current_imu_pct)/
  nullifzero(sum(own_price)))
    (format 'zzzz.z-'), 1, 7) || ' ' ||
 substr(zeroifnull(sum(own_price)/nullifzero(sum(total_eop_units)))
    (format 'zzz.zz'), 1, 6) || ' ' ||
 substr(zeroifnull(sum(lw_sales_dollars)/
nullifzero(sum(lw_sales_units)))
          (format 'zzz.zz-'), 1, 7)
        (title ''),
       '|\' (title ''),
       '$UNDERSCORES' (title '')
from
     $TMP_DATABASE.styrcp07 styrcp07,
     $DB_DATABASE.company company,
     $DB_DATABASE.division division,
     $DB_DATABASE.dept dept,
     $DB_DATABASE.style style,
     $RPT_DATABASE.styrcpv24 styrcpv24,
     $RPT_DATABASE.styrcpv27 styrcpv27,
     $RPT_DATABASE.styrcpv20 styrcpv20,
     $RPT_DATABASE.styrcpv21 styrcpv21,
     $RPT_DATABASE.styrcpv22 styrcpv22,     $RPT_DATABASE.styrcpv23
styrcpv23,
     $DB_DATABASE.merchandise_class merchclass
where
      (style.class_nbr = merchclass.class_nbr) and
      (merchclass.dept_nbr = dept.dept_nbr) and
      dept.division_nbr = division.division_nbr and
      division.company_nbr = company.comp_nbr and
      style.style_nbr = styrcp07.style_nbr
      and (dept.dept_nbr = styrcpv24.dept_nbr)
      and (dept.dept_nbr = styrcpv27.dept_nbr)
      and (dept.dept_nbr = styrcpv20.dept_nbr)
      and (dept.dept_nbr = styrcpv21.dept_nbr)
      and (dept.dept_nbr = styrcpv22.dept_nbr)
      and (dept.dept_nbr = styrcpv23.dept_nbr)

with '' (title '')by dept.dept_nbr, styrcp07.redline_ind

with
     ' |                         ' || '|U|' ||
     substr(zeroifnull(sum(str_bop_units)) (format 'zzz,zzz,zzz-'), 1, 12) ||
     '|' ||
     substr(zeroifnull(sum(lw_sales_units)) (format 'zzz,zzz,zzz-'), 1, 12) ||
     ' ' ||
     substr(zeroifnull(cast(sum(str_bop_units) as decimal(11,2))/
        nullifzero(sum(lw_sales_units)))
        (format 'zzz.z-'), 1, 6) ||
     ' ' ||
     substr(zeroifnull(cast(100*sum(lw_sales_units) as decimal(13,2))/
        nullifzero(sum(lw_sales_units) + sum(str_eop_units)))
        (format 'zzz.z-'), 1, 6) ||
     ' ' ||     substr(zeroifnull(cast(100*sum(lw_sales_units) as decimal(13,2))/
        nullifzero(sum(one_wk_ago_sales_units)) - 100)
        (format 'zzz.z-'), 1, 6) ||
     '|' ||
     substr(zeroifnull(avg(styrcpv24.shop_cnt)) (format 'z,zzz'), 1, 5) ||
     ' ' ||
     substr(zeroifnull(sum(str_eop_units)) (format 'zzz,zzz,zzz-'), 1, 12) ||
     ' ' ||
     (CASE WHEN zeroifnull(sum(in_transit_units)) > 0 THEN
         substr(zeroifnull(sum(in_transit_units)) (format 'zzz,zzz,zzz'),1, 11)
           ELSE
         substr(zeroifnull(sum(in_transit_units)) (format '---,---,--z'),1, 11)
        END) ||
     ' ' ||
     substr(zeroifnull(sum(dc_inv_units)) (format 'zzz,zzz,zzz-'), 1, 12) ||
     ' ' ||
   ' ' ||
   substr(zeroifnull(sum(total_eop_units)) (format 'zzz,zzz,zzz-'), 1, 12) ||
   '|' ||
   (CASE WHEN zeroifnull(sum(total_on_order_units)) > 0 THEN
       substr(zeroifnull(sum(total_on_order_units))
        (format 'zzz,zzz,zzz'),1, 11) ELSE
       substr(zeroifnull(sum(total_on_order_units))
        (format '---,---,--z'),1, 11)
      END) ||
   '|' ||
   substr(zeroifnull(sum(one_wk_ago_sales_units))
    (format 'zzz,zzz,zzz-'), 1, 12) ||
   ' ' ||
   substr(zeroifnull(sum(two_wk_ago_sales_units))
    (format 'zzz,zzz,zzz-'), 1, 12) ||
   ' ' ||
 substr(zeroifnull(sum(mtd_sales_units)) (format 'zzz,zzz,zzz-'), 1, 12) ||
 ' ' ||
 substr(zeroifnull(sum(std_sales_units)) (format 'zzz,zzz,zzz-'), 1, 12) ||
 '|' ||
 substr(zeroifnull(cast(sum(str_bop_units) as decimal(18,2))/
   nullifzero(avg(styrcpv24.shop_cnt))) (format 'zz,zzz-'),1,7) ||
 ' ' ||
 substr(zeroifnull(cast(sum(lw_sales_units) as decimal(18,2))/
   nullifzero(avg(styrcpv27.shop_cnt))) (format 'z,zzz-'),1,6) ||
 ' ' ||
 substr(zeroifnull(cast(sum(str_eop_units) as decimal(18,2))/
   nullifzero(avg(styrcpv27.shop_cnt))) (format 'zz,zzz-'),1,7) ||
 '|\' (title ''),
 ' |DEPT ' ||     substr(MAX(dept.dept_nbr) (format 'zzz'), 1, 3) ||' TOTAL |$|' ||

     substr(zeroifnull(sum(str_bop_dollars)) (format 'zzz,zzz,zzz-'), 1, 12) ||
     '|' ||
     substr(zeroifnull(sum(lw_sales_dollars)) (format 'zzz,zzz,zzz-'), 1, 12) ||

     ' ' ||

substr(zeroifnull(sum(str_bop_dollars)/nullifzero(sum(lw_sales_dollars))
)
        (format 'zzz.z-'), 1, 6) ||
     ' ' ||
     substr(zeroifnull(100*sum(lw_sales_dollars)/
       nullifzero(sum(lw_sales_dollars) + sum(str_eop_dollars)))
        (format 'zzz.z-'), 1, 6) ||
     ' ' ||
     substr(zeroifnull(100*sum(lw_sales_dollars)/
       nullifzero(sum(one_wk_ago_sales_dollars)) - 100)
        (format 'zzz.z-'), 1, 6) ||
     '|' ||
     substr(zeroifnull(avg(styrcpv27.shop_cnt)) (format 'z,zzz'), 1, 5) ||
     ' ' ||
     substr(zeroifnull(sum(str_eop_dollars)) (format 'zzz,zzz,zzz-'), 1, 12) ||
     ' ' ||
     (CASE WHEN zeroifnull(sum(in_transit_dollars)) > 0 THEN
         substr(zeroifnull(sum(in_transit_dollars))
          (format 'zzz,zzz,zzz'),1, 11) ELSE
         substr(zeroifnull(sum(in_transit_dollars))
          (format '---,---,--z'),1, 11)
        END) ||
     ' ' ||
     substr(zeroifnull(sum(dc_inv_dollars)) (format 'zzz,zzz,zzz-'), 1, 12) ||
     ' ' ||
     substr(zeroifnull(sum(total_eop_dollars))
      (format 'zzz,zzz,zzz-'), 1, 12) ||
     '|' ||
     (CASE WHEN zeroifnull(sum(total_on_order_dollars)) > 0 THEN
         substr(zeroifnull(sum(total_on_order_dollars))
          (format 'zzz,zzz,zzz'),1, 11) ELSE
         substr(zeroifnull(sum(total_on_order_dollars))
          (format '---,---,--z'),1, 11)
        END) ||
     '|' ||
     substr(zeroifnull(sum(one_wk_ago_sales_dollars))
      (format 'zzz,zzz,zzz-'), 1, 12) ||
     ' ' ||
     substr(zeroifnull(sum(two_wk_ago_sales_dollars))
      (format 'zzz,zzz,zzz-'), 1, 12) ||
     ' ' ||
     substr(zeroifnull(sum(mtd_sales_dollars))
      (format 'zzz,zzz,zzz-'), 1, 12) ||
     ' ' ||
     substr(zeroifnull(sum(std_sales_dollars))
      (format 'zzz,zzz,zzz-'), 1, 12) ||
     '|' ||
     substr(zeroifnull(100 - 100*sum(current_imu_pct)/
      nullifzero(sum(own_price)))
        (format 'zzzz.z-'), 1, 7) ||
     ' ' ||
     substr(zeroifnull(sum(own_price)/nullifzero(sum(total_eop_units)))
          (format 'zzz.zz'), 1, 6) ||
     ' ' ||

substr(zeroifnull(sum(lw_sales_dollars)/nullifzero(sum(lw_sales_units)))
      (format 'zzz.zz-'), 1, 7) ||
     '|\' (title ''),
     '$UNDERSCORES' (title '')by 46

with '' (title '')
by division.division_nbr

GROUP BY division.division_nbr, dept.dept_nbr, styrcp07.redline_ind
ORDER BY division.division_nbr, dept.dept_nbr, styrcp07.redline_ind
;

.export reset;

.logoff;
.quit;

Any ideas are welcome. I'm going to pull this apart and isolate the single line of SQL with the format stmt and try to get that to run .If not then I'll concentrate on that piece without the rest of the SQL getting in the way. That's a start. Any other suggestions are welcome.

Ron

Ron Crisafulli



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016