Using var like Row.MyVar

[Anonymous]

Hello,

I work with TOS for ESB 7.1.1,

I have a tjavaflex that retrieves all the column names of a view. I would like to retrieve the data contained in my view column by column but row5.Column throws an ERROR : Column cannot be resolved or is not a field. I need to do a dynamic Job because different view could be used. My column var takes the name of each of the columns.

If someone know if it's possible to use a var like row5.myVar ?

Thanks.

[lennelei]

Can't you update your view to increment the ID directly in the view?

[Anonymous]

No I can't because there is no ID from the view. The ID_Table will be  ID_MAX (with a : "SELECT  MAX(" +context.Table+"."+context.PrimaryKey+") FROM " + context.Table       -- and then I truncate the table) +1 . The project manager told me I can't add a column ID in my view ...

[lennelei]

In that case, can't you set a global variable :

id_max="SELECT  MAX(" +context.Table+"."+context.PrimaryKey+") FROM " + context.Table

Then use the query :

"INSERT INTO " + context.Table +"
"SELECT " + (String)globalMap.get("id_max") + ", v.* FROM myView v"

?

[Anonymous]

But the ID_Max :

(String)globalMap.get("id_max")

 will have the same value for each row rigth?

[lennelei]

I just take that code from your previous post :

The ID_Table will be  ID_MAX (with a : "SELECT  MAX(" +context.Table+"."+context.PrimaryKey+") FROM " + context.Table       -- and then I truncate the table) +1 

I don't understand from your previous posts how you may have a list of ID_Max different for each rows and then link that ID_Max value to the data you want to insert.

[Anonymous]

I have different ID for each row of my table. I just save th max ID before I truncate it because I need to Insert data from the view in my table. BUT I need to give ID for each row of view AND this ID need to be the ID max from my table +1.

For example :

- I have 3 rows in my table with data, so 3 ID : 1 ; 2 ; 3

ID | Val1 | Val2 etc

--------------------------------

1 | myData | myData

2 | myData | myData

3 | myData | myData

- I save with the query the ID max in my context var ID_MAX ( so 3)

- Then I truncate the table.

- And I insert data from my view : 4 rows + my ID_MAX incremented for each row.

- So I will have in my table:

ID |    Val1   | Val2 etc

--------------------------------

4  |dataView| dataView

5  |dataView| dataView

6  |dataView| dataView

7  |dataView| dataView

[lennelei]

Any reason for not using database sequence?

What if you do :

"INSERT INTO " + context.Table +"
"SELECT " + (String)globalMap.get("id_max") + " + ROW_NUMBER() over (ORDER BY <put whatever column you want to sort on>), v.* FROM myView v"

?

ROW_NUMBER or whatever your database uses is a function that will return a unique row number in the result set.

You'll probably have to specify an OVER(ORDER BY...) clause to use it: as you don't care about the order, you can use any column to sort on.

The code above should insert into your table the data from the view with an ID starting from your max id and incrementing the value by 1 on each row thanks to the + ROW_NUMBER()

[Anonymous]

I don't have the same column between View (39) and Table (43).

So i get this exception

java.sql.SQLSyntaxErrorException: ORA-00947: not enough values.

[lennelei]

I'm sorry but I won't be able to help you further without more details!

You wrote : "First I save my ID_Max from my table, then I truncate my table and then I insert data from a view into my table."

I presumed that your view have the same column than the table minus the ID ; if that's not the case, you should know how to map the columns from the view into the columns of the table.

I (or anybody else) won't be able to guess that

[Anonymous]

The problem is not to map these columns. I have to do this without knowing the structure of the tables. I just have the name of the database server and the tables / views. I know the name of column of table and view with a SYS View. With a tJava i know how many and which column from table is not in the view.

AND .... all of this have to work for different table and view.

I created arrayList for:

Name Column Table

Name Column View

Name Column Not in View

What I try is to hold data from my view, row by row in an arraylist in a tJavaFlex like :

But for the line String value = row.CODE , I want the value for each row, because I will have only CODE.

So I did that :

With a schema like :

I have 800 rows in my view, but I only get in my arrayList the first value of first column AND only 39 times (the number of columns of my view).

With a schema like (I add vColumn at the end):

I have 800 rows in my view, but I only get NULL value 39 times.

For the 39 problem it's because I use the wrong var so don't mind about it.