I'm trying to do a SQL passthrough query with WF7.1.1. The query I have is as follows

ENGINE SQLJDBC SET DEFAULT_CONNECTION jepesUserCon
SQL SQLJDBC PREPARE SQLOUT FOR
SELECT
  POD.ULN,
  JRSR.SVC_NM               AS Service,
  POD.UNIT_TYPE,
  InitCap(JRUT.UNTYP_NM)    AS Unit_Type_Name,
  JRUT.UNTYP_ABRV_NM        AS Unit_Type_Abreviation,
  InitCap(JRITR1.INSTL_NM)  AS POD_Installation_Name,
  G1.INSTL_CD               AS POD_Installation_Code,
  InitCap(G1.GLC_NM)        AS POD_Location,
  InitCap(JRCR1.CNTY_NM)    AS POD_Country,
  POD.POD_GLC_CD,
  POD.POD_START,
  POD.POD_END,
  InitCap(JRITR2.INSTL_NM)  AS DEST_Installation_Name,
  G2.INSTL_CD               AS DEST_Installation_Code,
  InitCap(G2.GLC_NM)        AS DEST_Location,
  InitCap(JRCR2.CNTY_NM)    AS DEST_Country,
  DEST.DEST_GLC_CD,
  DEST.DEST_START,
  DEST.DEST_END
FROM
  (
    SELECT
	  ZE.ULN_ID      AS ULN,
	  ZE.OPLAN_ID    AS OPLAN_ID,
	  ZE.POD_GLC_CD,
	  ZE.UNIT_TYPE,
	  MIN(ZE.DAYS)   AS POD_START,
	  MAX(ZE.DAYS)   AS POD_END
    FROM
	  Z_POD ZE
    GROUP BY
	  ZE.ULN_ID,
	  ZE.OPLAN_ID,
	  ZE.POD_GLC_CD,
	  ZE.UNIT_TYPE
  ) POD,
  (
    SELECT
	  ZD.ULN_ID       AS ULN,
	  ZD.DEST_GLC_CD,
	  MIN(ZD.DAYS)    AS DEST_START,
	  MAX(ZD.DAYS)    AS DEST_END
    FROM
	  Z_DEST ZD
    GROUP BY
	  ZD.ULN_ID,
	  ZD.DEST_GLC_CD
  ) DEST,
  JEPES_REF.GEOLOCATION     G1,
  JEPES_REF.GEOLOCATION     G2,
  JEPES_REF.Instl_Ty_Ref    JRITR1,
  JEPES_REF.Instl_Ty_Ref    JRITR2,
  JEPES_REF.Country_Ref     JRCR1,
  JEPES_REF.Country_Ref     JRCR2,
  JEPES_REF.Unit_Type       JRUT,
  JEPES_REF.Service_Ref     JRSR
WHERE
      POD.ULN             = DEST.ULN
  AND G1.GLC_CD       (+) = POD.POD_GLC_CD
  AND JRITR1.INSTL_CD (+) = G1.INSTL_CD
  AND G2.GLC_CD       (+) = DEST.DEST_GLC_CD
  AND JRITR2.INSTL_CD (+) = G2.INSTL_CD
  AND JRCR1.CNTY_CD   (+) = G1.CNTY_CD
  AND JRCR2.CNTY_CD   (+) = G2.CNTY_CD
  AND JRUT.UNTYP_CD   (+) = POD.UNIT_TYPE
  AND JRUT.SVC_CD         = JRSR.SVC_CD
END

When I run the FEX, get the following error

quote:

(FOC1400) SQLCODE IS 1003 (HEX: 000003EB)
: (1003) [72000] ORA-01003: no statement parsed
L (FOC1405) SQL PREPARE ERROR.

Does anyone know what I'm doing wrong?

Thanks,
dan

Try this:

Right above the "END" insert
;
TABLE FILE SQLOUT
PRINT *
BY WhateverFieldYouWish NOPRINT
ON TABLE HOLD AS HOLD1
END
-RUN

At least that final piece works over here for SQL Pass-Through.

quote:

Try this:

Right above the "END" insert
;
TABLE FILE SQLOUT
PRINT *
BY WhateverFieldYouWish NOPRINT
ON TABLE HOLD AS HOLD1
END
-RUN

I tried that, and now my FEX looks like this:

-* File Time_In_Transit.fex
ENGINE SQLJDBC SET DEFAULT_CONNECTION jepesUserCon
SQL SQLJDBC PREPARE SQLOUT FOR
SELECT
  POD.ULN,
  JRSR.SVC_NM               AS Service,
  POD.UNIT_TYPE,
  InitCap(JRUT.UNTYP_NM)    AS Unit_Type_Name,
  JRUT.UNTYP_ABRV_NM        AS Unit_Type_Abreviation,
  InitCap(JRITR1.INSTL_NM)  AS POD_Installation_Name,
  G1.INSTL_CD               AS POD_Installation_Code,
  InitCap(G1.GLC_NM)        AS POD_Location,
  InitCap(JRCR1.CNTY_NM)    AS POD_Country,
  POD.POD_GLC_CD,
  POD.POD_START,
  POD.POD_END,
  InitCap(JRITR2.INSTL_NM)  AS DEST_Installation_Name,
  G2.INSTL_CD               AS DEST_Installation_Code,
  InitCap(G2.GLC_NM)        AS DEST_Location,
  InitCap(JRCR2.CNTY_NM)    AS DEST_Country,
  DEST.DEST_GLC_CD,
  DEST.DEST_START,
  DEST.DEST_END
FROM
  (
    SELECT
	  ZE.ULN_ID      AS ULN,
	  ZE.OPLAN_ID    AS OPLAN_ID,
	  ZE.POD_GLC_CD,
	  ZE.UNIT_TYPE,
	  MIN(ZE.DAYS)   AS POD_START,
	  MAX(ZE.DAYS)   AS POD_END
    FROM
	  Z_POD ZE
    GROUP BY
	  ZE.ULN_ID,
	  ZE.OPLAN_ID,
	  ZE.POD_GLC_CD,
	  ZE.UNIT_TYPE
  ) POD,
  (
    SELECT
	  ZD.ULN_ID       AS ULN,
	  ZD.DEST_GLC_CD,
	  MIN(ZD.DAYS)    AS DEST_START,
	  MAX(ZD.DAYS)    AS DEST_END
    FROM
	  Z_DEST ZD
    GROUP BY
	  ZD.ULN_ID,
	  ZD.DEST_GLC_CD
  ) DEST,
  JEPES_REF.GEOLOCATION     G1,
  JEPES_REF.GEOLOCATION     G2,
  JEPES_REF.Instl_Ty_Ref    JRITR1,
  JEPES_REF.Instl_Ty_Ref    JRITR2,
  JEPES_REF.Country_Ref     JRCR1,
  JEPES_REF.Country_Ref     JRCR2,
  JEPES_REF.Unit_Type       JRUT,
  JEPES_REF.Service_Ref     JRSR
WHERE
      POD.ULN             = DEST.ULN
  AND G1.GLC_CD       (+) = POD.POD_GLC_CD
  AND JRITR1.INSTL_CD (+) = G1.INSTL_CD
  AND G2.GLC_CD       (+) = DEST.DEST_GLC_CD
  AND JRITR2.INSTL_CD (+) = G2.INSTL_CD
  AND JRCR1.CNTY_CD   (+) = G1.CNTY_CD
  AND JRCR2.CNTY_CD   (+) = G2.CNTY_CD
  AND JRUT.UNTYP_CD   (+) = POD.UNIT_TYPE
  AND JRUT.SVC_CD         = JRSR.SVC_CD
;
TABLE FILE SQLOUT
PRINT *
BY ULN NOPRINT
ON TABLE HOLD AS HOLD1
END
-RUN
END

But I still get the

quote:

(FOC1400) SQLCODE IS 1003 (HEX: 000003EB)
: (1003) [72000] ORA-01003: no statement parsed
L (FOC1405) SQL PREPARE ERROR.

error.

Does anyone have any ideas as to what I'm doing wrong?

Since you are doing a PREPARE use a viewname other than SQLOUT.

You can look at http://techsupport.informationbuilders.com/ibase/master...9db2um3.htm#09DB2UM6

SQL [target_db] PREPARE view_name FOR
SELECT....[;]
END
where:

target_db
Indicates the target RDBMS. Acceptable values are DB2 or SQLDS. Omit if you previously issued the SET SQLENGINE command.

view_name
Names the Master File (FOCUS view). The name can be eight characters long and must conform to FOCUS naming conventions for Master Files.

SELECT...
Is any SELECT statement.



If you do a SELECT without the PREPARE the name of the output is SQLOUT.

Also, is there any reason you aren't using
SQL SQLORA ...
against ORACLE?