February 3, 2012 at 10:21 am
Greetings experts,
This is the error I am getting:
Subqueries are not allowed in this context. Only scalar expressions are allowed.
It has to do with ((select isNull(max(UserSequence), 0) +
When I run the following query:
INSERT INTO EMP (UserSequence, employee_id, charity_code, check_amt, chcknum, one_time, bi_weekly, cash, donate_choice, date_stamp) VALUES ((select isNull(max(UserSequence), 0) + 1, '0000025700','AC418','333','','0','0','','Yes','2/3/2012 12:15:20 PM');
Any ideas how to resolve this?
Thanks alot in advance
February 3, 2012 at 10:28 am
Well your subquery by itself is not valid.
(select isNull(max(UserSequence), 0)
From where? What is UserSequence?
It is hard to tell exactly what you are trying to do but you probably need a variable for this.
declare @UserSequence somedatatype
select @UserSequence = isNull(max(UserSequence), 0) + 1
from SomeTable
INSERT INTO EMP (UserSequence, employee_id, charity_code, check_amt, chcknum, one_time, bi_weekly, cash, donate_choice, date_stamp) VALUES (@UserSequence, '0000025700','AC418','333','','0','0','','Yes','2/3/2012 12:15:20 PM');
That will get around your insert issue but since this looks a rownumber why not just use identity? I can tell you from experience that this type of thing is going to cause issues at some point. How are you planning on handling concurrency issues? Rolling your own identity function like this is just too difficult to get it right.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 3, 2012 at 10:37 am
Thank you very much Sean for the prompt response.
First, I had done it the way you just did it but my boss raised the same concurrency issue that you just alluded to.
The suggestion was to do it the way I did.
The reason we are doing this is that we have a front end app that allows some selected administrators to have the ability to delete records.
Some times, some records are deleted inadvertently.
To save us and them some grief we decided to archive deleted records.
So, once a record is deleted, it goes into archive table.
Then we built a frontend app to restore the deleted records just in case they were deleted in error.
Problem is that some records associated with ID (identity column) can be duplicated.
For instance, if we have an identit column with value of 1 and we have a user with id of 1234, the user can submit up 5 records belonging to that ID at once. Something like:
Id User
1 1234
2 1234
3 1234
Etc
Now if we wish to restore this, in the past, only the first record is restored.
That's why we decided to work around the Identity column.
February 3, 2012 at 11:49 am
Well since this is relatively quick you could just put it inside a transaction. At least that should prevent getting duplicates, unless there are dirty reads.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 3, 2012 at 12:03 pm
Thanks for all your help - much appreciated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply