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.
Unable to view data from OLDB stage for a user defined query
Moderators: chulett, rschirm, roy
It seems you have more than one statement, which is problematic. And the fact that it 'runs fine' outside of DataStage doesn't mean a whole lot if it is not properly constructed for what it is being used for inside DataStage.
However, the error is Invalid object name '#SCTY_XREF_TEMP8' so I'd suggest you deal with that first. What it is supposed to be?
However, the error is Invalid object name '#SCTY_XREF_TEMP8' so I'd suggest you deal with that first. What it is supposed to be?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: