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,

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 helps

This message has been edited. Last edited by: jgelona,