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.
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.
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
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
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
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,
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
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, 2005
Focal Point Forums 