| Author |
Message
|
| polydegmon |
 Posted: Tue Mar 12, 2013 9:27 am Post subject: Issue updating a collection of rows in a database table |
 |
|
Novice
Joined: 07 Oct 2011
Posts: 11
|
I'm trying to update a collection of rows in a database table using the code below, however it's not working and causes the debugger to terminate once I hit this line.
| Code: |
UPDATE Database.SchemaName.TableName AS PAY
SET MY_IND = 0,
CS_LST_UPDT_DT = CURRENT_TIMESTAMP
WHERE PAY.TRKG_ID IN (Environment.Variables.Banking.BankingToBeRemoved[])
|
The Environment.Variables.Banking.BankingToBeRemoved[] arrays setup is shown below
| Code: |
Environment.Variables.Banking.BankingToBeRemoved[1].ReferenceID = '1';
Environment.Variables.Banking.BankingToBeRemoved[2].ReferenceID = '2';
Environment.Variables.Banking.BankingToBeRemoved[3].ReferenceID = '3';
Environment.Variables.Banking.BankingToBeRemoved[4].ReferenceID = '4';
|
I've tested the use of the IN keyword with the code below and it is working
| Code: |
UPDATE Database.SchemaName.TableName AS PAY
SET MY_IND = 0,
CS_LST_UPDT_DT = CURRENT_TIMESTAMP
WHERE PAY.TRKG_ID IN ('1', '2', '3', '4')
|
Has anyone any ideas on this, any help would be great, thanks. |
|
| Back to top |
|
 |
| Vitor |
| Posted: Tue Mar 12, 2013 10:32 am Post subject: Re: Issue updating a collection of rows in a database table |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| polydegmon wrote: |
| I'm trying to update a collection of rows in a database table using the code below, however it's not working and causes the debugger to terminate once I hit this line. |
What does the user trace say is happening? The trace will contain the abend details.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Mar 12, 2013 10:46 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Code: |
| ('1', '2', '3', '4') |
is a list with 4 elements.
| Code: |
| (Environment.Variables.Banking.BankingToBeRemoved[]) |
is a list with one element that happens to be an array with 4 children. |
|
| Back to top |
|
|
| polydegmon |
| Posted: Wed Mar 13, 2013 5:44 am Post subject: |
|
|
Novice
Joined: 07 Oct 2011
Posts: 11
|
Hi mqjeff, I've also tried the following format, however it is still causing the debugger to terminate.
| Code: |
SET Environment.Variables.Banking.BankingToBeRemoved.ReferenceID[1] = '1';
SET Environment.Variables.Banking.BankingToBeRemoved.ReferenceID[2] = '2';
UPDATE Database.SchemaName.TableName AS PAY
SET MY_IND = 0,
CS_LST_UPDT_DT = CURRENT_TIMESTAMP
WHERE PAY.TRKG_ID IN (Environment.Variables.Banking.BankingToBeRemoved.ReferenceID[]) |
Can you tell me how I can use the IN keyword with a list of environment values? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Mar 13, 2013 6:08 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You need to transform your array of child elements into a list of values.
If I were to do this without reviewing the documentation on ESQL list and array values, I'd use an ESQL select to do this.
I recommend you review the documentation on ESQL list and array values. |
|
| Back to top |
|
|
| polydegmon |
| Posted: Thu Mar 14, 2013 6:38 am Post subject: |
|
|
Novice
Joined: 07 Oct 2011
Posts: 11
|
Hi mqjeff, I've done some testing with the IN keyword and it seems to work with any type of list or environment reference, until you try to pass it over to a database.
Even creating the list of values in the form '1', '2' as a string, when it's passed to the database it doesn't work. It seems to pass what ever is after the IN keyword to the database without first evaluating it.....it seems to be passing the variable name rather than the contents of the variable.
Has anyone found a way around this issue? |
|
| Back to top |
|
|
|
|
