December 27, 2005, 03:00 PM
Dan MorphisSQL Pass-Through help
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
December 27, 2005, 03:44 PM
S.J. KadishTry 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.
December 27, 2005, 05:22 PM
Dan Morphisquote:
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?
December 27, 2005, 07:40 PM
PiipsterSince you are doing a PREPARE use a viewname other than SQLOUT.
You can look at
http://techsupport.informationbuilders.com/ibase/master...9db2um3.htm#09DB2UM6SQL [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?