Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Getting FOC14068 Error, and don't know why

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Getting FOC14068 Error, and don't know why
 Login/Join
 
Member
posted
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,
 
Posts: 19 | Registered: October 24, 2006Report This Post
<JG>
posted
quote:

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

Not possibly missing a , are we
 
Report This Post
Member
posted Hide Post
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
 
Posts: 19 | Registered: October 24, 2006Report This Post
Expert
posted Hide Post
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
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Getting FOC14068 Error, and don't know why

Copyright © 1996-2020 Information Builders