JOIN BETWEEN 2 HOLD FILES FAILS 1

[ozi2]

Hi all,
I have 2 questions please:
1)Currently I'm working with 5.16 WF server and 4.36 client,
with CGI, so in my url I see "cgi-bin/ibi_cgi/ibiweb.exe?IBIF_ex=myfex"
Will it be the same when working with 5.2 WF version, both
server and client? (assuming it is configured to servlet option)

2)I have 2 hold files and I want to join them,
however the primary file can't seem to find the field out of the slave file.
The code is:
SET ASNAMES=ON
TABLE FILE FACT_OVD
SUM
MONTH_EXECUTION
BY SECTOR_CODE
BY SECTOR_DESC
BY DEPARTMENT_CODE
BY DEPARTMENT_DESC
WHERE ( DEPARTMENT_CODE EQ 29 )
AND ( EMPLOYEE_TYPE_CODE EQ 1)
AND (Identity_Card NE '999999999')
AND (YYEAR EQ 2003)
AND MMONTH EQ 10);
ON TABLE HOLD AS HOLD1
END

TABLE FILE FACT_OVD
SUM
MONTH_EXECUTION
BY SECTOR_CODE
BY DEPARTMENT_CODE
WHERE ( DEPARTMENT_CODE EQ 29 )
AND ( EMPLOYEE_TYPE_CODE EQ 1)
AND (Identity_Card NE '999999999')
AND (YYEAR EQ 2003)
AND (MMONTH LE 10);
ON TABLE HOLD AS HOLD2
END
-*to have a different name from HOLD1
DEFINE FILE HOLD2 ADD
TOT_EXECUTION/D12.2=MONTH_EXECUTION;
END

-RUN
JOIN
HOLD1.SECTOR_CODE IN HOLD1
TO ALL
HOLD2.SECTOR_CODE
AS J1
END

TABLE FILE HOLD1
PRINT SECTOR_CODE MONTH_EXECUTION TOT_EXECUTION
END

I get the following error:
THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: AS
0 ERROR AT OR NEAR LINE 183 IN PROCEDURE IDINVAGEZORRASHUT
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: TOT_EXECUTION
BYPASSING TO END OF COMMAND

The goal is to show MONTH_EXECUTION FOR current month
and until current month. I thought to use to SUM commands
and join them....
Thanks in advanced,
OZ

 

[WFGuru]

1) The preferred method in 5.2 is servlet which would be /ibi_apps/WFServlet. CGI will still work however.

2) Try using HOLD AS name FORMAT FOCUS INDEX field instead where the field name is the field you are JOINing TO.

 

[craigiep]

The second filename is missing from your join

JOIN SECTOR_CODE IN HOLD1 TO ALL SECTOR_CODE IN HOLD2 AS J1

 

[rasETL]

craigiep is correct.

However, you may want to revise your process per WFGuru's reply. If the HOLD Files being created are quite large, the use of temporary Focus Databases via FORMAT FOCUS INDEX will provide exceptional process improvement. Even if they are small, FOCUS works so much better with Databases as opposed to Files.

 

[ozi2]

Thank you all for helping me out.
craigiep - you saved me!!!

OZ