 |
 |
Archives of the TeradataForum
Message Posted: Tue, 01 Jul 2003 @ 21:55:33 GMT
| 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
| |
