October 31, 2008, 09:27 AM
Chris ShafferI'm getting a FOC14068 "ERROR IN SCALAR FUNCTION 'CHAR' UNRECOGNIZED SECOND PARAMETER " error, and I really don't know why. I'm trying to use IF statements in my SQL passthru, and I think that may be the issue.
quote:
-INCLUDE DEFDATES
-INCLUDE WHOWHERE
-SET &CMCT = 'A' | 'CMC';
-SET &INVT = 'A' | 'INVSITE';
-SET &FILE1 = 'A' | 'SERINVI';
-SET &FILE2 = 'A' | 'MATITEM';
-SET &FILE3 = 'A' | 'NSERINI';
SQL
select distinct serial_nbr, category_cd, subcat_cd, a.mtl_desc, on_hand_qty,
unit_of_msrmt, receipt_dt, AVG_PRICE_AMT,
CASE WHEN UNIT_OF_MSRMT = 'EA' THEN AVG_PRICE_AMT * ON_HAND_QTY WHEN
UNIT_OF_MSRMT = 'FT' THEN (ON_HAND_QTY/100) * AVG_PRICE_AMT END cost
from
&FILE1 a,
&FILE2 b,
&CMCT c,
&INVT d
where
a.mtl_desc = b.mtl_desc
and category_cd in ('CABLE-FIBER','CABLE-COPPER')
AND USAGE_CD NOT IN ('E','J')
and d.INV_SITE_CD = a.inv_site_cd
and c.cmc_cd = d.cmc_cd
-IF &CMC EQ '%null%' THEN GOTO SKIPCMC;
and d.cmc_cd = '&CMC'
-SKIPCMC
-IF &DIST EQ '%null%' THEN GOTO SKIPDIST;
and c.district_cd = '&DIST'
-SKIPDIST
-IF &ISITE EQ '%null%' THEN GOTO SKIPINV;
and d.inv_site_cd = '&ISITE'
-SKIPINV
union
select distinct CAST(nsii_id AS CHAR(10)) serial_nbr, category_cd, subcat_cd,
a.mtl_desc, on_hand_qty, unit_of_msrmt, receipt_dt,
AVG_PRICE_AMT, CASE WHEN UNIT_OF_MSRMT = 'EA' THEN AVG_PRICE_AMT * ON_HAND_QTY WHEN
UNIT_OF_MSRMT = 'FT' THEN (ON_HAND_QTY/100) * AVG_PRICE_AMT END cost
from
&FILE3 a,
&FILE2 b,
&CMCT c,
&INVT d
where a.mtl_desc = b.mtl_desc and
category_cd = 'TERMINAL' AND
SUBCAT_CD IN ('FDI-XBOX-HARNES','FDI-XBOX-LG-TRM','POLE-MTD-XBOX','POLE-XBOX-HARN')
AND USAGE_CD NOT IN ('E','J')
and d.INV_SITE_CD = a.inv_site_cd
and c.cmc_cd = d.cmc_cd
-IF &CMC EQ '%null%' THEN GOTO SKIPCMC1;
and d.cmc_cd = '&CMC'
-SKIPCMC1
-IF &DIST EQ '%null%' THEN GOTO SKIPDIST1;
and c.district_cd = '&DIST'
-SKIPDIST1
-IF &ISITE EQ '%null%' THEN GOTO SKIPINV1;
and d.inv_site_cd = '&ISITE'
-SKIPINV1
;
TABLE ON TABLE HOLD AS ALDATA FORMAT FOCUS
END
DEFINE FILE ALDATA
AGE/D15 = DATEDIF (RECEIPT_DT, DATECVT (&DAT, 'I8YYMD', 'YYMD'), 'D');
AGECLASS/I4 = IF AGE LT 30 THEN 0 ELSE IF AGE IS-FROM 30 TO 59 THEN 1
ELSE IF AGE IS-FROM 60 TO 89 THEN 2 ELSE IF AGE GT 90 THEN 3;
AGETXT/A10 = IF AGECLASS EQ 0 THEN '0-29 Days' ELSE IF AGECLASS EQ 1 THEN '30-59 Days'
ELSE IF AGECLASS EQ 2 THEN '60-89 Days' ELSE IF AGECLASS EQ 3 THEN '> 90 Days';
FCOST/D15.2M = COST;
CATTXT/A15 = IF CATEGORY_CD EQ 'TERMINAL' THEN 'SAI' ELSE CATEGORY_CD;
END
-SET &FILE1 = 'F' | 'SERINVI';
-SET &FILE2 = 'F' | 'MATITEM';
-SET &FILE3 = 'F' | 'NSERINI';
SQL
select distinct serial_nbr, category_cd, subcat_cd, a.mtl_desc, on_hand_qty,
unit_of_msrmt, receipt_dt, AVG_PRICE_AMT,
CASE WHEN UNIT_OF_MSRMT = 'EA' THEN AVG_PRICE_AMT * ON_HAND_QTY WHEN
UNIT_OF_MSRMT = 'FT' THEN (ON_HAND_QTY/100) * AVG_PRICE_AMT END cost
from
&FILE1 a,
&FILE2 b,
&CMCT c,
&INVT d
where
a.mtl_desc = b.mtl_desc
and category_cd in ('CABLE-FIBER','CABLE-COPPER')
AND USAGE_CD NOT IN ('E','J')
and d.INV_SITE_CD = a.inv_site_cd
and c.cmc_cd = d.cmc_cd
-IF &CMC EQ '%null%' THEN GOTO SKIPCMC2;
and d.cmc_cd = '&CMC'
-SKIPCMC2
-IF &DIST EQ '%null%' THEN GOTO SKIPDIST2;
and c.district_cd = '&DIST'
-SKIPDIST2
-IF &ISITE EQ '%null%' THEN GOTO SKIPINV2;
and d.inv_site_cd = '&ISITE'
-SKIPINV2
union
select distinct CAST(nsii_id AS CHAR(10)) serial_nbr, category_cd, subcat_cd,
a.mtl_desc, on_hand_qty, unit_of_msrmt, receipt_dt,
AVG_PRICE_AMT, CASE WHEN UNIT_OF_MSRMT = 'EA' THEN AVG_PRICE_AMT * ON_HAND_QTY WHEN
UNIT_OF_MSRMT = 'FT' THEN (ON_HAND_QTY/100) * AVG_PRICE_AMT END cost
from
&FILE3 a,
&FILE2 b,
&CMCT c,
&INVT d
where a.mtl_desc = b.mtl_desc and
category_cd = 'TERMINAL' AND
SUBCAT_CD IN ('FDI-XBOX-HARNES','FDI-XBOX-LG-TRM','POLE-MTD-XBOX','POLE-XBOX-HARN')
AND USAGE_CD NOT IN ('E','J')
and d.INV_SITE_CD = a.inv_site_cd
and c.cmc_cd = d.cmc_cd
-IF &CMC EQ '%null%' THEN GOTO SKIPCMC3;
and d.cmc_cd = '&CMC'
-SKIPCMC3
-IF &DIST EQ '%null%' THEN GOTO SKIPDIST3;
and c.district_cd = '&DIST'
-SKIPDIST3
-IF &ISITE EQ '%null%' THEN GOTO SKIPINV3;
and d.inv_site_cd = '&ISITE'
-SKIPINV3
;
TABLE ON TABLE HOLD AS FLDATA FORMAT FOCUS
END
DEFINE FILE FLDATA
AGE/D15 = DATEDIF (RECEIPT_DT, DATECVT (&DAT, 'I8YYMD', 'YYMD'), 'D');
AGECLASS/I4 = IF AGE LT 30 THEN 0 ELSE IF AGE IS-FROM 30 TO 59 THEN 1
ELSE IF AGE IS-FROM 60 TO 89 THEN 2 ELSE IF AGE GT 90 THEN 3;
AGETXT/A10 = IF AGECLASS EQ 0 THEN '0-29 Days' ELSE IF AGECLASS EQ 1 THEN '30-59 Days'
ELSE IF AGECLASS EQ 2 THEN '60-89 Days' ELSE IF AGECLASS EQ 3 THEN '> 90 Days';
FCOST/D15.2M = COST;
CATTXT/A15 = IF CATEGORY_CD EQ 'TERMINAL' THEN 'SAI' ELSE CATEGORY_CD;
END
-SET &FILE1 = 'M' | 'SERINVI';
-SET &FILE2 = 'M' | 'MATITEM';
-SET &FILE3 = 'M' | 'NSERINI';
SQL
select distinct serial_nbr, category_cd, subcat_cd, a.mtl_desc, on_hand_qty,
unit_of_msrmt, receipt_dt, AVG_PRICE_AMT,
CASE WHEN UNIT_OF_MSRMT = 'EA' THEN AVG_PRICE_AMT * ON_HAND_QTY WHEN
UNIT_OF_MSRMT = 'FT' THEN (ON_HAND_QTY/100) * AVG_PRICE_AMT END cost
from
&FILE1 a,
&FILE2 b,
&CMCT c,
&INVT d
where
a.mtl_desc = b.mtl_desc
and category_cd in ('CABLE-FIBER','CABLE-COPPER')
AND USAGE_CD NOT IN ('E','J')
and d.INV_SITE_CD = a.inv_site_cd
and c.cmc_cd = d.cmc_cd
-IF &CMC EQ '%null%' THEN GOTO SKIPCMC4;
and d.cmc_cd = '&CMC'
-SKIPCMC4
-IF &DIST EQ '%null%' THEN GOTO SKIPDIST4;
and c.district_cd = '&DIST'
-SKIPDIST4
-IF &ISITE EQ '%null%' THEN GOTO SKIPINV4;
and d.inv_site_cd = '&ISITE'
-SKIPINV4
union
select distinct CAST(nsii_id AS CHAR(10)) serial_nbr, category_cd, subcat_cd,
a.mtl_desc, on_hand_qty, unit_of_msrmt, receipt_dt,
AVG_PRICE_AMT, CASE WHEN UNIT_OF_MSRMT = 'EA' THEN AVG_PRICE_AMT * ON_HAND_QTY WHEN
UNIT_OF_MSRMT = 'FT' THEN (ON_HAND_QTY/100) * AVG_PRICE_AMT END cost
from
&FILE3 a,
&FILE2 b,
&CMCT c,
&INVT d
where a.mtl_desc = b.mtl_desc and
category_cd = 'TERMINAL' AND
SUBCAT_CD IN ('FDI-XBOX-HARNES','FDI-XBOX-LG-TRM','POLE-MTD-XBOX','POLE-XBOX-HARN')
AND USAGE_CD NOT IN ('E','J')
and d.INV_SITE_CD = a.inv_site_cd
and c.cmc_cd = d.cmc_cd
-IF &CMC EQ '%null%' THEN GOTO SKIPCMC5;
and d.cmc_cd = '&CMC'
-SKIPCMC5
-IF &DIST EQ '%null%' THEN GOTO SKIPDIST5;
and c.district_cd = '&DIST'
-SKIPDIST5
-IF &ISITE EQ '%null%' THEN GOTO SKIPINV5;
and d.inv_site_cd = '&ISITE'
-SKIPINV5
;
TABLE ON TABLE HOLD AS MSDATA FORMAT FOCUS
END
DEFINE FILE MSDATA
AGE/D15 = DATEDIF (RECEIPT_DT, DATECVT (&DAT, 'I8YYMD', 'YYMD'), 'D');
AGECLASS/I4 = IF AGE LT 30 THEN 0 ELSE IF AGE IS-FROM 30 TO 59 THEN 1
ELSE IF AGE IS-FROM 60 TO 89 THEN 2 ELSE IF AGE GT 90 THEN 3;
AGETXT/A10 = IF AGECLASS EQ 0 THEN '0-29 Days' ELSE IF AGECLASS EQ 1 THEN '30-59 Days'
ELSE IF AGECLASS EQ 2 THEN '60-89 Days' ELSE IF AGECLASS EQ 3 THEN '> 90 Days';
FCOST/D15.2M = COST;
CATTXT/A15 = IF CATEGORY_CD EQ 'TERMINAL' THEN 'SAI' ELSE CATEGORY_CD;
END
-SET &FILE1 = 'C' | 'SERINVI';
-SET &FILE2 = 'C' | 'MATITEM';
-SET &FILE3 = 'C' | 'NSERINI';
SQL
select distinct serial_nbr, category_cd, subcat_cd, a.mtl_desc, on_hand_qty,
unit_of_msrmt, receipt_dt, AVG_PRICE_AMT,
CASE WHEN UNIT_OF_MSRMT = 'EA' THEN AVG_PRICE_AMT * ON_HAND_QTY WHEN
UNIT_OF_MSRMT = 'FT' THEN (ON_HAND_QTY/100) * AVG_PRICE_AMT END cost
from
&FILE1 a,
&FILE2 b,
&CMCT c,
&INVT d
where
a.mtl_desc = b.mtl_desc
and category_cd in ('CABLE-FIBER','CABLE-COPPER')
AND USAGE_CD NOT IN ('E','J')
and d.INV_SITE_CD = a.inv_site_cd
and c.cmc_cd = d.cmc_cd
-IF &CMC EQ '%null%' THEN GOTO SKIPCMC6;
and d.cmc_cd = '&CMC'
-SKIPCMC6
-IF &DIST EQ '%null%' THEN GOTO SKIPDIST6;
and c.district_cd = '&DIST'
-SKIPDIST6
-IF &ISITE EQ '%null%' THEN GOTO SKIPINV6;
and d.inv_site_cd = '&ISITE'
-SKIPINV6
union
select distinct CAST(nsii_id AS CHAR(10)) serial_nbr, category_cd, subcat_cd,
a.mtl_desc, on_hand_qty, unit_of_msrmt, receipt_dt,
AVG_PRICE_AMT, CASE WHEN UNIT_OF_MSRMT = 'EA' THEN AVG_PRICE_AMT * ON_HAND_QTY WHEN
UNIT_OF_MSRMT = 'FT' THEN (ON_HAND_QTY/100) * AVG_PRICE_AMT END cost
from
&FILE3 a,
&FILE2 b,
&CMCT c,
&INVT d
where a.mtl_desc = b.mtl_desc and
category_cd = 'TERMINAL' AND
SUBCAT_CD IN ('FDI-XBOX-HARNES','FDI-XBOX-LG-TRM','POLE-MTD-XBOX','POLE-XBOX-HARN')
AND USAGE_CD NOT IN ('E','J')
and d.INV_SITE_CD = a.inv_site_cd
and c.cmc_cd = d.cmc_cd
-IF &CMC EQ '%null%' THEN GOTO SKIPCMC7;
and d.cmc_cd = '&CMC'
-SKIPCMC7
-IF &DIST EQ '%null%' THEN GOTO SKIPDIST7;
and c.district_cd = '&DIST'
-SKIPDIST7
-IF &ISITE EQ '%null%' THEN GOTO SKIPINV7;
and d.inv_site_cd = '&ISITE'
-SKIPINV7
;
TABLE ON TABLE HOLD AS NCDATA FORMAT FOCUS
END
DEFINE FILE NCDATA
AGE/D15 = DATEDIF (RECEIPT_DT, DATECVT (&DAT, 'I8YYMD', 'YYMD'), 'D');
AGECLASS/I4 = IF AGE LT 30 THEN 0 ELSE IF AGE IS-FROM 30 TO 59 THEN 1
ELSE IF AGE IS-FROM 60 TO 89 THEN 2 ELSE IF AGE GT 90 THEN 3;
AGETXT/A10 = IF AGECLASS EQ 0 THEN '0-29 Days' ELSE IF AGECLASS EQ 1 THEN '30-59 Days'
ELSE IF AGECLASS EQ 2 THEN '60-89 Days' ELSE IF AGECLASS EQ 3 THEN '> 90 Days';
FCOST/D15.2M = COST;
CATTXT/A15 = IF CATEGORY_CD EQ 'TERMINAL' THEN 'SAI' ELSE CATEGORY_CD;
END
TABLE FILE ALDATA
PRINT
AGE
AGETXT
AGECLASS
CATEGORY_CD
CATTXT
FCOST
ON TABLE HOLD AS ALLDATA
MORE
FILE FLDATA
MORE
FILE MSDATA
MORE
FILE NCDATA
END
TABLE FILE ALLDATA
SUM
FCOST AS 'TOTAL COST'
BY CATTXT AS 'MATERIAL CATEGORY'
BY AGETXT AS 'AGE'
ON TABLE SUMMARIZE
END
-EXIT
-NODATA
-INCLUDE NODATA