Test Data Manager

Hi Team, I have created a sequence for one column and want to use the same seq. value for another  column along with some additional value in that row. i am not finding sequence current value function in tdm portal. I can not use that first column as the sequence is getting tied up with other value. Please help

Thank you.

Hi,

For using sequence numbers in the TDM portal,  please have a look at the documentation on @nextval.    If you create a sequence called myseq  then to initialize it,  you should be able to call @nextval(myseq) and use the myseq reference in your other columns.

Data Generation Functions and Parameters - CA Test Data Manager - 4.2 - CA Technologies Documentation 

NEXTVAL(SEQUENCE)

Return the next value in the specified sequence. The sequence is created if it does not exist. The sequence starts at 1.

Parameters: 

• SEQUENCE — name of a sequence

Return value: the next value in the sequence

Example: @nextval(mysequence)@

Example result: 1 

Thank you Joe for the feedback. But my requirement is different. I am not looking sequence nextval. Let me give you in details. I created a sequence for column1 and I used the sequence value in a format like  CUSTID_@nextval(seq)@.  e.g. the nextval is 1. I have to use this sequence value 1 in multiple column example column 4, value should be like "Customer 1 contains nine product" and column 6 value should be like "application1" etc. that mean I have to use only sequence value for multiple column for that row like CURRVAL(seq) in oracle and similarly the subsequent value will get change based on row by row. I tried this by storing value in a variable but it is not working.  using tdm 4.1

Hi Sushanta, please open a support case and we can troubleshoot this further one on one. You can do this by going to CA Support Online - CA Technologies or Contact CA Support - CA Technologies. 

You could also split the first column into the sequence and the string. And in the string grab the generated value from the sequence. That way you can re-use the sequence number in any other column where needed. This of course implies that you can add a field to the table. If that is not possible, you can re-use the sequence number, but it will involve a couple string function to extract the sequence number.

Br,

Peter

You could use the @nextval(seq)@ for the CUSTID column and for all other coumns you could fire a sql query to retieve the current sequence value. In Oracle you could use the below query

   select MY_SEQ_NAME.currval from DUAL;

Thanks,

Anil

Hi Anil,

      I am creating rule in TDM portal and my database is SQL server. The whole situation is because of that I am not finding the sequence's current value function. If possible can you give a example from sql server.

My requirement

Col1                      Col2                      Col3                 Col4                          Col5

cust_Nr_1_id         300                       cust1                 27/11/2017               datapoint1

cust_Nr_2_id         300                       cust2                 27/11/2017               datapoint2

cust_Nr_3_id         300                       cust3                 27/11/2017               datapoint3

The value 1,2,3  need to generate synthetically.

Thank you.

For sql server, you could retrieve the current value of the sequence using the query below:

SELECT current_value FROM sys.sequences WHERE name = 'MY_SEQ_NAME' ;

Thanks,

Anil

Hi Anil...Did this work for you? Because I was just trying it using below function and it is not working.

@execsql(R,select MY_SEQ_NAME.CURRVAL from DUAL)@     and it is throwing an error:  "Go back and edit the data."

Note: I have used 'R' since the db sequences are stored in gtrep repository database. Though I also tried using the profile option and got the same error.

Another point to note is that, when I am running the same query "select MY_SEQ_NAME.CURRVAL from DUAL" in SQL Developer or Test Data Repository SQL window, I am getting the below error

Error: ORA-08002: sequence KEY_SEQ.CURRVAL is not yet defined in this session.

Any suggestions on this?

PS: The solution which PeterDeZutter mentioned above did work for me.

Hi Ankur,

Before you can use the execsql, you will need to use @nextval(my_seq_name)@ at least once in a publish operation. Once published the sequence is created, it might be that sequence is also create when click the validate button.

Once that is done than you can use execsql to retrieve the current value of the sequence.

Best regards,

Peter

Hi Peter,

I did use @nextval(MY_SEQ_NAME)@  in column 1 and then used @execsql(R,select MY_SEQ_NAME.CURRVAL from DUAL)@     in column 2 and then only shared my results above.

If you can try it out using the above @execsql ()@ and share the result, it would be great. I think that it would not work and we would have to use strings functions to achieve this.

Regards,

Ankur

Hi Ankur,

The approach using nextval in column 1 and sql in column 2 is not going to work. The sequence used in column 1 will only be created upon commit of the record. Thus it is normal that column 2 is failing.

Could you try validating a couple of times the nextval in column1, and than check in a sql tool if the sequence has been created. If it was than column 2 should work also.

Another remark, why going back to the database for column 2, you could also used the generated value of column 1.

column_1= @nextval(MY_SEQ_NAME)@

column_2=^column_1^

That way you also have the current value of the sequence without the need to execute another sql statement.

Best regards,
Peter

Hi Peter,

I am just trying to find an optimum solution for the issue which NEW_ACCOUNT mentioned above:

Col1                      Col2                      Col3                 Col4                          Col5

cust_Nr_1_id         300                       cust1                 27/11/2017               datapoint1

cust_Nr_2_id         300                       cust2                 27/11/2017               datapoint2

cust_Nr_3_id         300                       cust3                 27/11/2017               datapoint3

The value 1,2,3  need to generate synthetically.

column_1= @nextval(MY_SEQ_NAME)@    ;  column_2=^column_1^   cannot be used in the scenarios mentioned by NEW_ACCOUNT and as you mentioned in your earlier post we would have to use string functions to achieve it.

To your other question, sequence is getting created in the database (checked using the sql tool), but still sql in column 2 is not working.

I am just curious to know how to make  @execsql(R,select MY_SEQ_NAME.CURRVAL from DUAL)@  work

Regards,

Ankur

Hi Ankur,

Nothing prevents you from generating the sequence value in a hidden column and reuse it for the other fields.

ColX nextval sequence

Col3 cust^ColX

Col5 datapoint^ColX

You can add a hidden field via the actions for registered objects, select the table right click and select maintain table.

Add the record, update the fields as needed and check that the exclude field is set to ALL:ALL.

Now the new field only needs the nextval sequence.

Best regards,

Peter