[SOLVED] Use values in a file in a DEFINE or COMPUTE
I thought this would work, but it doesn't - the compute always results in 'NO'. Am I losing my mind or can't we do this?
TABLE FILE CAR
PRINT COUNTRY
WHERE COUNTRY NE 'W GERMANY'
ON TABLE SAVE AS S001
END
-RUN
TABLE FILE CAR
PRINT COUNTRY CAR MODEL SALES
COMPUTE FLAG1/A3 = IF COUNTRY IN FILE S001 THEN 'YES' ELSE 'NO';
END
-RUN
This message has been edited. Last edited by: Francis Mariani,
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
February 20, 2009, 04:03 PM
FrankDutch
What if you put the compute in a DEFINE first Francis?
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
February 20, 2009, 04:08 PM
Francis Mariani
This doesn't work either:
TABLE FILE CAR
PRINT COUNTRY
WHERE COUNTRY NE 'W GERMANY'
ON TABLE SAVE AS S001
END
-RUN
DEFINE FILE CAR
FLAG1/A3 = IF COUNTRY IN FILE S001 THEN 'YES' ELSE 'NO';
END
TABLE FILE CAR
PRINT COUNTRY CAR MODEL SALES FLAG1
END
-RUN
I can't seem to find a single example of this.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
February 20, 2009, 04:11 PM
susannah
I think IN FILE requires a master in order to find/read the file. but then again....its been a long week.
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
February 20, 2009, 04:14 PM
FrankDutch
how about
DEFINE FILE CAR
FLAG0/I2 = DECODE COUNTRY(S001 ELSE 1);
FLAG1/A3=IF FLAG0 EQ 0 THEN 'NO' ELSE 'YES';
END
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
February 20, 2009, 04:23 PM
Francis Mariani
Frank, thank you! While you were typing that example out, I also found it in the documentation under "DECODE: Decoding Values". The solution seems counterintuitive but it works, so thanks very much. Here's my example code corrected:
TABLE FILE CAR
PRINT COUNTRY
WHERE COUNTRY NE 'W GERMANY'
ON TABLE SAVE AS S001
END
-RUN
DEFINE FILE CAR
NOT_WHAT_I_WANT/I1 = DECODE COUNTRY (S001 ELSE 1);
FLAG1/A3 = IF NOT_WHAT_I_WANT EQ 1 THEN 'NO' ELSE 'YES';
END
TABLE FILE CAR
PRINT COUNTRY CAR MODEL SALES FLAG1
END
-RUN
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
February 20, 2009, 04:27 PM
FrankDutch
But I wonder what happens if you say "EQ 'W GERMANY' or 'ITALY' I think the space between W G will give some problems.
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
February 20, 2009, 04:33 PM
Francis Mariani
You're right! The values in the file must have single quotes. Lucky for me, my real-world file has no blanks.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
February 20, 2009, 04:46 PM
Francis Mariani
I spoke too soon I can't get this to work
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
February 20, 2009, 04:55 PM
FrankDutch
I'm sure you can Francis
What is the problem? number of records? or spaces...
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
February 20, 2009, 05:04 PM
Francis Mariani
The error message I'm getting is quite misleading:
(FOC272) FORMAT ERROR IN DECODE OR FILE ELEMENT: BCPF030SA
It's the length of the file name that is the problem - here we are in v7.6.5 and the syntax still calls for an 8 character file name (I was using 9).
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
February 20, 2009, 05:12 PM
FrankDutch
Yes
The 8 character boundary is especially for the younger once among us a problem.....
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
February 20, 2009, 05:21 PM
GinnyJakes
I haven't used this and I don't know if it will help you but there is a new function called DB_LOOKUP for 7.6.
Here is documentation from the SofNF for 7.6:
Retrieving a Value From a Fixed Format Sequential File in a TABLE Request The following procedure creates a fixed format sequential file named GSALE from the GGSALES data source. The fields in this file are PRODUCT (product description), CATEGORY (product category), and PCD (product code). The file is sorted on the PCD field: SET ASNAMES = ON TABLE FILE GGSALES SUM PRODUCT CATEGORY BY PCD ON TABLE HOLD AS GSALE FORMAT ALPHA END
12. Reporting Language The following Master File is generated as a result of the HOLD command: FILENAME=GSALE, SUFFIX=FIX , $ SEGMENT=GSALE, SEGTYPE=S1, $ FIELDNAME=PCD, ALIAS=E01, USAGE=A04, ACTUAL=A04, $ FIELDNAME=PRODUCT, ALIAS=E02, USAGE=A16, ACTUAL=A16, $ FIELDNAME=CATEGORY, ALIAS=E03, USAGE=A11, ACTUAL=A11, $ The following TABLE request against the GGPRODS data source, sorts the report on the field that matches the key field in the lookup file. It retrieves the value of the CATEGORY field from the GSALE lookup file by matching on the product code and product description fields. Note that the DEFINE FILE command is cleared at the end of the request: DEFINE FILE GGPRODS PCAT/A11 MISSING ON = DB_LOOKUP(GSALE, PRODUCT_ID, PCD, PRODUCT_DESCRIPTION, PRODUCT, CATEGORY); END TABLE FILE GGPRODS PRINT PRODUCT_DESCRIPTION PCAT BY PRODUCT_ID END -RUN DEFINE FILE GGPRODS CLEAR END
There is more stuff there before and after this example.
the DB_LOOKUP is a great function, but it does not always work well. Small lookup tables do work.
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
February 20, 2009, 05:55 PM
Francis Mariani
Ginny,
Thanks for the suggestion. In this particular case, I'd rather not create masters for the files I want to use in the DECODE.
Regards,
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
February 22, 2009, 04:05 PM
Doug
I know this states that it's been solved. But, it did not seem that you got your answer. So, try adding this code between your two TABLE FILE requests and change your IN statement to "IN (&CountryList)": in your second TABLE FILE request.
... -SET &CountryList = '' ; -REPEAT READ_LOOP &LINES TIMES ; -READ S001 &ThisCountry.A10. -SET &CountryList = IF &CountryList EQ '' - THEN '''' || &ThisCountry || '''' - ELSE &CountryList || ',' || '''' || &ThisCountry || ''''; -TYPE *** &|ThisCountry = &ThisCountry -READ_LOOP -TYPE The following countries are in your SAVE file: &|COUNTRIES. -RUN ... COMPUTE FLAG1/A3 = IF COUNTRY IN (&CountryList) THEN 'YES' ELSE 'NO'; ...
You'll see the -TYPE messages ("&ThisCountry" and "The following countries are in your SAVE file") in your View Source. Note: No master file description needed.
-Doug
In FOCUS Since 1983 ~ from FOCUS to WebFOCUS. Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
February 22, 2009, 04:49 PM
Francis Mariani
Doug, thanks for your input. My problem was with accessing an external file in a DEFINE or COMPUTE. The solution is to use a DECODE and make sure the file name is 8 characters or less.
Cheers,
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
February 23, 2009, 09:34 AM
ira
I know it is listed as solved but thought I would throw my 2 cents in. Of course it comes this is using a 'hold' ddname file and that this technique is limited based on the number of bytes being housed in that ddname, but I kept it out of the compute/define range... TABLE FILE CAR PRINT COUNTRY CAR MODEL WHERE COUNTRY NE 'W GERMANY' ON TABLE HOLD AS S001 END TABLE FILE CAR PRINT COUNTRY CAR MODEL IF COUNTRY EQ (S001) END