Problem with a sequencer

mac4rfree85 · Post by **mac4rfree85** » Mon Jan 10, 2011 8:38 am

Hi Guys,

I am trying to create a surrogate key using the Oracle sequencer. I have given the following statement as Userdefined SQL.

Code: Select all

Insert into Table (colnam1,colnam2,,,,) values (schema.sequencename.nextval,?,?,,,,);

But it is throwing me an error saying as "invalid variable/number". I am attaching the screenshot of the error too.

Can somebody let me know as what i am doing wrong..

Thanks for your time and help..

Regards,
Magesh

chulett · Post by **chulett** » Mon Jan 10, 2011 10:02 am

You have a mismatch between the number of columns in the stage and the number of "?" bind variables. Remove "column1" from the stage since the stage will not be populating it.

mac4rfree85 · Post by **mac4rfree85** » Mon Jan 10, 2011 10:52 am

chulett, the query i posted is a similiar one.it is a very long query.. i double checked it.. the number of columns and the '?' are matching..

chulett · Post by **chulett** » Mon Jan 10, 2011 11:34 am

They can't be or you wouldn't be receiving this message. Riddle me this: do you have "colnam1" on the Columns tab of the stage?

dsx999 · Post by **dsx999** » Mon Jan 10, 2011 11:53 am

Run your SQL query directly in the database environment first. It will tell you if there is an error in your query.

mac4rfree85 · Post by **mac4rfree85** » Tue Jan 11, 2011 12:49 am

@chullet, in the column tabs i have 42 columns and in the query also i have 42 columns mentioned.
I cross checked the query, i have 41 '?' and 1 sequencename.Nextval...
So, the query is correct.
@dsx999, it is a insert query,, so unable to run it as i did not have a test data with me..

One more thing, in the transformer (previous stage to the table), i am hardcoding first column ,where the sequence next value needs to be passed, as ' ' as it is a not null column.. Is it causing some problem?

chulett · Post by **chulett** » Tue Jan 11, 2011 7:45 am

mac4rfree85 wrote:@chullet, in the column tabs i have 42 columns and in the query also i have 42 columns mentioned.
I cross checked the query, i have 41 '?' and 1 sequencename.Nextval...
So, the query is correct.

No, this proves that the query is incorrect and is not set up as I said it must be. You have 42 columns in the stage and 41 '?' in the SQL - that is causing the error as they must match up one for one as I noted in my first reply.

One more time - remove the first column from the target stage. You cannot include it as the stage is not binding a value into it from the incoming link.

mac4rfree85 · Post by **mac4rfree85** » Wed Jan 12, 2011 3:13 am

@chulett,,

Thanks removing the column for which i need to populate the value did the trick.. Now i am able to insert to the table successfully.

But i need to do a upsert instead of only insert statement.
When i give the update statement along with the insert statement separated by ';', it is throwing me a SIGBUS error. How to do a upsert in such a scenario.

Update will be done on the primary key of the column. The surrogate key which i am creating from the sequence will be present only in the Insert statement.

The query i am using is as below

Update schema.table set col2=?,col3=?,col4=?,col5=?,col6=?,col7=?,col=8,col9=? where col1=?;
insert into schema.table values (sequencename.nextval,?,?,?,?,?,?,?,?,?)

Basically, if i give only the insert , it is working properly but if i give the update also, then it is giving SIGBUS error

chulett · Post by **chulett** » Wed Jan 12, 2011 7:40 am

You can't in this particular scenario. Use two targets for this, one for the insert and one for the update. Yes, that does mean you need to determine which is which rather than relying on the crutch of the database doing that for you but that should be a very simple lookup.

mac4rfree85 · Post by **mac4rfree85** » Thu Jan 13, 2011 4:16 am

chulett wrote:You can't in this particular scenario. Use two targets for this, one for the insert and one for the update. Yes, that does mean you need to determine which is which rather than relying on the crutch of the database doing that for you but that should be a very simple lookup.

Ya chulett, Finally that was i did.. but i wanted to be sure that in no way we can do that.. i thought i might have done something wrong..
It was very nice of you to helped me... you are a Saviour..

chulett · Post by **chulett** » Thu Jan 13, 2011 9:02 am

No problem. Did want to mention you should look at an auto-generated upsert for the stage sometime to see why your custom one didn't work. The metadata needs to line up between the two sql statements, meaning all columns must be mentioned in both and that was something you couldn't do.