SQL PREPARE SQLIN FROM

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

Posts: 25 | Location: Portland, OR | Registered: October 13, 2006

jgelona

Master

posted

Hide Post

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,

In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.

Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

iWay Software Product Forum on Focal Point

SQL PREPARE SQLIN FROM

Focal Point | Privacy Statement | Terms of Service