Update table inside for loop

Question

I am trying to update a table inside a for loop.  When the next iteration comes in I am using an If statement to open the same data source under a different name and then update the first table.  The problem is Jmp keeps adding a number to the first table's name so rather than getting 8 updates, I get 8 separate tables.  NR is my number of records that needs pulled and added to each table.  V is the record I want to obtain and add to the existing table.

For( i = 1, i <= NR, i++,

V = Vlo[i];

Vlo_Temp = Open Database(

"DSN=EZ Mes;Description=EZ;UID=;Trusted_Connection=Yes;APP=JMP;WSID=RLEEPER;DATABASE=EZ_DB_LOADER",

"SELECT * FROM dbo.tbl_NVR WHERE NV_VALUETYPE = 'DateTime' AND UID_NOW = '" || V || "'",

"Vlo_Temp"

);

If( i > 1,

V_Temp = Open Database(

"DSN=EZ Mes;Description=EZ;UID=;Trusted_Connection=Yes;APP=JMP;WSID=RLEEPER;DATABASE=EZ_DB_LOADER",

"SELECT * FROM dbo.tbl_NVR WHERE NV_VALUETYPE = 'DateTime' AND UID_NOW = '" || V || "'",

"V_Temp"

);

Vlo_Temp = Vlo_Temp << Update( With( Data Table( "V_Temp" ) ), Output Table Name ("Vlo_Temp") );

V_Temp << Close Window;

);

Help?

rleeper · Answer

Wiebepo,

Thank you for the interesting code.  I didn't know I could do a concatitems inside a SQL query.

I fixed my problem by removing the first query from the loop and running the next query without the If statement, then everything worked.

wiebepo · Answer

Unless there is more to it, I agree with PMroz. I suspect a for loop is not necessary. Consider the following code, which should get all the values desired in one query.

Vlo_Temp = Open Database(

"DSN=EZ Mes;Description=EZ;UID=;Trusted_Connection=Yes;APP=JMP;WSID=RLEEPER;DATABASE=EZ_DB_LOADER",

"SELECT * FROM dbo.tbl_NVR WHERE NV_VALUETYPE = 'DateTime' AND UID_NOW IN ('" || concatitems(vlo[1::NR],"', '") || "')",

"Vlo_Temp"

);

pmroz · Answer

Can you just read in the table dbo.tbl_NVR all at once without looping over it?

If that is not possible then try closing the table using

close(v_temp, nosave);

instead of using << close window.