ORA-00904 error

[rsunny]

Hi everyone,

I am trying to implement scd2 with the surrogatekey .i am getting an error while executing
CopyOfjs_scd1_SK..xfm1: |CopyOfjs_scd1_SK..ODBC_47.update: DSD.BCIOpenW call to SQLPrepare failed.
SELECT "sid","storeid","name","city","state","cid","startdate","enddate" FROM CDLEXIS1
SQLSTATE=S0022, DBMS.CODE=904
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-00904: "enddate": invalid identifier|

can anybody tell me what is this error for?

thanks in advance

[chulett]

It simply means that there is no column named "enddate" in your table.

[rsunny]

It simply means that there is no column named "enddate" in your table.

I see enddate in my table which is a reference table but i dont see enddate in my source table.so please can you tell me do i need to have enddate in my source table too.

thanks in advance

[ray.wurlod]

Yes, if you're going to try to select it by name from source.

No if you're going to select it as a constant from source or generate its value within the job. But this second approach can not be done in the Oracle stage - it would need to be done on the output of a Transformer stage.

[rsunny]

thanks everyone for the reply, but please can any one tell me how to find a solution for this as my source dont have start and end date and my reference has both startdate and enddate.now i am trying to implement scd type2 and i tried to implement the logic but not working as i am finding the same error.does my approach wrong?can anyone please suggest me how to find a solution for this error.

[chulett]

You can't select a column that doesn't exist, so you'll need to fix that in your source. For the reference, it sounds like you need to do a "range lookup" where your source date is between those two reference dates.