Focal Point
[SOLVED] Getting FOC14068 Error, and don't know why

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6831018403

October 31, 2008, 09:27 AM
Chris Shaffer
[SOLVED] Getting FOC14068 Error, and don't know why
I'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.

Here is my report code:

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


Honestly, I think the problem may be with these blocks:

quote:
-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


Is it possible to do webfocus "if" statements inside an SQL passthru?

Thanks!

This message has been edited. Last edited by: Kerry,
October 31, 2008, 09:35 AM
<JG>
quote:

select distinct CAST(nsii_id AS CHAR(10)) serial_nbr, category_cd, subcat_cd,

Not possibly missing a , are we
October 31, 2008, 09:50 AM
Chris Shaffer
I don't think so.

This works perfectly:
quote:
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 where a.mtl_desc = b.mtl_desc
and category_cd in ('CABLE-FIBER','CABLE-COPPER') AND USAGE_CD NOT IN ('E','J')
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 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');
TABLE ON TABLE HOLD AS ALDATA FORMAT FOCUS
END


While this fails:

quote:
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

October 31, 2008, 09:57 AM
GinnyJakes
Run with ECHO on and XRETRIEVAL off to see the generated SQL and compare it to what is working.

There are developers here who generate SQL with Dialogue Manager so I don't think the -IF is your problem.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
October 31, 2008, 10:42 AM
j.gross
quote:
select distinct CAST(nsii_id AS CHAR(10)) serial_nbr, ...


It looks like that's where Focus is choking.

If the CAST transformation is isomorphic over the range of possible nsii_id values in the tables, I suggest you select nsii_id as is (in the four places this line appears), and apply an equivalent transformation downstream in a COMPUTE or DEFINE.


- Jack Gross
WF through 8.1.05