I need to get data from a HOLD file and compare it with an SQL table. I'd rather use SQL code. I'm new to SQL and using it in FOCUS code. This is what I have that doesn't work: TABLE FILE TEST01 PRINT * ON TABLE PCHOLD AS SQLTEST1 FORMAT ALPHA END
-MRNOEDIT BEGIN SQL EDA SET SERVER WFSA2003 -REMOTE BEGIN
SET SQLENGINE = SQLMSS SQL SQLMSS SET SERVER FTLDSQL02 SQL PREPARE SQLIN FROM WHRePack.Description, SQLTEST1.PARTNO, WHRePack.Loc FROM Repack.dbo.WHRePack JOIN SQLTEST1 ON WHRePack.Partno = SQLTEST1.PARTNO ; TABLE ON TABLE PCHOLD FORMAT ALPHA END
-REMOTE END
TABLE FILE SQLOUT PRINT * ON TABLE HOLD AS TEST02 END
TABLE FILE TEST02 PRINT * END
-MRNOEDIT END -EXIT
Seems easy enough? But it fails BIG TIME. Am I not using the SQLIN statement correctly? Should I even be using it? Any help would be greatly appreciated. -robert-This message has been edited. Last edited by: Robert B Clark,
Robert B. Clark -- Business Systems Analyst Daimler Trucks North America Phone: 503.745.7057 "There's always a better way." IBM Mainframe zOS 8.1.05, DB2 adapter WF 8.1.05 Hub to Sub, Self-Service, SQL Server adapter
November 01, 2007, 08:49 AM
jgelona
Robert,
I am using Oracle and don't use MRE all that much but I think your sql syntax is wrong. Here's a snipit of code that I use.
SET SQLENGINE=SQLORA
SQL SET SERVER &&KIDS_SERVER
SQL PREPARE REFERRAL FOR
SELECT T2.REFER_ID REFERRAL_ID,
NVL(TO_CHAR(T1.STRT_DT,'YYYYMMDD HH24MI'),' ') AS_ABEGDT,
.
.
.
T2.DISP_DY_TB_INITD INITDAYS
FROM TASSIGNMENT T1,
TREFERRAL T2,
TUNIT T3
WHERE (T1.D_ENT_FOR_ID(+) = T2.REFER_ID)
AND (T3.UNT_ID(+) = T1.D_UNT_TYP_CDE)
.
.
.
ORDER BY T2.REFER_ID,
NVL(T1.END_DT,TO_DATE('&WRKFY 2359','YYYYMMDD HH24MI'));
END
DEFINE FILE REFERRAL
CURRBOM/A8YYMD='&CURRBOM';
REFER_ID/I8=REFERRAL_ID;
.
.
.
END
-*
TABLEF FILE REFERRAL
SUM LST.RF_ICOUNTYTY/I5
.
.
.
BY REFER_ID
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS WK640A MISSING OFF
END
-*
DEFINE FILE REFERRAL CLEAR
END
-RUN
Notice my PREPARE statement ends with FOR not FROM. It is followed by the SELECT command. Then I TABLEF the "file" named in the PREPARE statement, in my case REFERRAL, in your case, use SQLIN.
Hope this helpsThis message has been edited. Last edited by: jgelona,
In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.