Unable to view data from OLDB stage for a user defined query
Posted: Fri Dec 21, 2012 2:56 am
Hi ,
I am using ODBC stage as input stage .
I am using mention below query as user defined query.
DECLARE @AGGR_DT AS DATETIME
SELECT @AGGR_DT=CONVERT(DATETIME,LTrim(RTrim(JOB_PARM_VAL))) FROM TEMP_JOB_CONFIG WHERE JOB_PARM_NM='AGGR_DT'
SELECT top 10 EXTRNL_SCTY_ID,STCK_EXCH_ID,SCTY_ID_TYP,LTrim(RTrim(RBCDS_ID)) as RBCDS_ID into #SCTY_XREF_TEMP8 FROM SCTY_XREF WHERE SCTY_ID_TYP='T' AND STRT_DT<=@AGGR_DT AND END_DT>=@AGGR_DT;
SELECT EXTRNL_SCTY_ID,STCK_EXCH_ID,SCTY_ID_TYP,LTrim(RTrim(RBCDS_ID)) as RBCDS_ID FROM #SCTY_XREF_TEMP8
When a try to view data its giving error as mention below.
GSAP_LOOKUP_CreateLkpForFormatJobs_change_1..ODBC_110.ToBuildSctyXrefLkp1: DSD.BCIOpenR call to function SQLExecDirect failed.
Statement was:DECLARE @AGGR_DT AS DATETIME
SELECT @AGGR_DT=CONVERT(DATETIME,LTrim(RTrim(JOB_PARM_VAL))) FROM TEMP_JOB_CONFIG WHERE JOB_PARM_NM='AGGR_DT'
SELECT top 10 EXTRNL_SCTY_ID,STCK_EXCH_ID,SCTY_ID_TYP,LTrim(RTrim(RBCDS_ID)) as RBCDS_ID into #SCTY_XREF_TEMP8 FROM SCTY_XREF WHERE SCTY_ID_TYP='T' AND STRT_DT<=@AGGR_DT AND END_DT>=@AGGR_DT;
SELECT EXTRNL_SCTY_ID,STCK_EXCH_ID,SCTY_ID_TYP,LTrim(RTrim(RBCDS_ID)) as RBCDS_ID FROM #SCTY_XREF_TEMP8
SQLSTATE=S0002, DBMS.CODE=208
[DataStage][SQL Client][ODBC][IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Invalid object name '#SCTY_XREF_TEMP8'.
SQLSTATE=37000, DBMS.CODE=8180
[DataStage][SQL Client][ODBC][IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
Please help me.This query is working fine when i run it on sqlserver.
I am using ODBC stage as input stage .
I am using mention below query as user defined query.
DECLARE @AGGR_DT AS DATETIME
SELECT @AGGR_DT=CONVERT(DATETIME,LTrim(RTrim(JOB_PARM_VAL))) FROM TEMP_JOB_CONFIG WHERE JOB_PARM_NM='AGGR_DT'
SELECT top 10 EXTRNL_SCTY_ID,STCK_EXCH_ID,SCTY_ID_TYP,LTrim(RTrim(RBCDS_ID)) as RBCDS_ID into #SCTY_XREF_TEMP8 FROM SCTY_XREF WHERE SCTY_ID_TYP='T' AND STRT_DT<=@AGGR_DT AND END_DT>=@AGGR_DT;
SELECT EXTRNL_SCTY_ID,STCK_EXCH_ID,SCTY_ID_TYP,LTrim(RTrim(RBCDS_ID)) as RBCDS_ID FROM #SCTY_XREF_TEMP8
When a try to view data its giving error as mention below.
GSAP_LOOKUP_CreateLkpForFormatJobs_change_1..ODBC_110.ToBuildSctyXrefLkp1: DSD.BCIOpenR call to function SQLExecDirect failed.
Statement was:DECLARE @AGGR_DT AS DATETIME
SELECT @AGGR_DT=CONVERT(DATETIME,LTrim(RTrim(JOB_PARM_VAL))) FROM TEMP_JOB_CONFIG WHERE JOB_PARM_NM='AGGR_DT'
SELECT top 10 EXTRNL_SCTY_ID,STCK_EXCH_ID,SCTY_ID_TYP,LTrim(RTrim(RBCDS_ID)) as RBCDS_ID into #SCTY_XREF_TEMP8 FROM SCTY_XREF WHERE SCTY_ID_TYP='T' AND STRT_DT<=@AGGR_DT AND END_DT>=@AGGR_DT;
SELECT EXTRNL_SCTY_ID,STCK_EXCH_ID,SCTY_ID_TYP,LTrim(RTrim(RBCDS_ID)) as RBCDS_ID FROM #SCTY_XREF_TEMP8
SQLSTATE=S0002, DBMS.CODE=208
[DataStage][SQL Client][ODBC][IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Invalid object name '#SCTY_XREF_TEMP8'.
SQLSTATE=37000, DBMS.CODE=8180
[DataStage][SQL Client][ODBC][IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
Please help me.This query is working fine when i run it on sqlserver.