SELECT CASE to ExecuteScalar()

Question

I'm trying to insert new records into a table. The first field in the table is an supposed to be an autonumber, even though it isn't at the moment, and I am trying to run ExecuteScalar() to retrieve the MAX of that field (ELECT_BONUS_DFRL_SEQ_ID, if it is NULL or 0, I would like to make it 1, else make it MAX(e.ELECT_BONUS_DFRL_SEQ_ID) + 1.

I am not really familiar with SQL all that well, and I've done my best to try and format it correctly but I can tell I am off on something.

SELECT CASE 
        WHEN e.ELECT_BONUS_DFRL_SEQ_ID IS NULL OR MAX(e.ELECT_BONUS_DFRL_SEQ_ID) < 1
        THEN e.BONUS_DFRL_SEQ_ID = '1'
        ELSE MAX(e.ELECT_BONUS_DFRL_SEQ_ID) + 1
    END AS ELECT_BONUS_DFRL_SEQ_ID
FROM ELECT_BONUS_DFRL AS e
GROUP BY e.ELECT_BONUS_DFRL_SEQ_ID;

SQLFiddle - It keeps saying there is an error at the = in my THEN statement.

Now, I thought about creating a variable like @NextID and using that to hold the value of 1 or the MAX + 1, but I'm not sure if that would interfere with my ExecuteScalar() in my program.

I also am not sure if it's allowed to assign values to the columns like that, and by that I mean: THEN e.BONUS_DFRL_SEQ_ID = '1'

The code in my program is :

    Dim sql As String = "SELECT MAX(BONUS_SEQ_ID) FROM ELECT_BONUS_DFRL_SEQ_ID;"
    Dim maxCmd As New SqlCommand(sql, conn)
    nextID = Convert.ToInt32(maxCmd.ExecuteScalar())

I am hoping nextID will hold the next number in the sequence.

Example:

+------------------+--------------+
|ELECT_BONUS_SEQ_ID|  Percentage  |
+------------------+--------------+
|        1         |      100     |
+------------------+--------------+
|     nextID       |              |  <- new record to insert

PS - If you're looking at the SQLFiddle and scratching your head at the Data types, all of the varchar fields will be encrypted data, and the guy who set up the DDL didn't make the SEQ_ID an INT & IDENTITY so it doesn't autoincrement. (this will be resolved in the future, I am hoping.)

If anyone has any ideas, please let me know. Also, if you care to shed some light as to why it wouldn't work for the ways I tried please do!

I'm a little confused what you are trying to do, Are you trying to retrieve the next available incrementing number ? so you can then use it in an insert ? — Andre Pageot, Mar 14 '14 at 15:57
Yeah, exactly. Sorry I am somewhat bad at explaining things, haha. — Mark C., Mar 14 '14 at 16:57
in that case here is the answer you are looking for http://stackoverflow.com/questions/4853361/sql-server-insert-next-available-int — Andre Pageot, Mar 15 '14 at 21:03

score 1 · Accepted Answer · answered Mar 14 '14 at 14:59

1

It looks like you're trying to update the source data within a SELECT which is not possible. Did you mean this?

SELECT CASE 
        WHEN e.ELECT_BONUS_DFRL_SEQ_ID IS NULL OR MAX(e.ELECT_BONUS_DFRL_SEQ_ID) < 1
        THEN 1
        ELSE MAX(e.ELECT_BONUS_DFRL_SEQ_ID) + 1
    END AS ELECT_BONUS_DFRL_SEQ_ID
FROM ELECT_BONUS_DFRL AS e
GROUP BY e.ELECT_BONUS_DFRL_SEQ_ID;

If you are trying to update the source data then you need to do the UPDATE in one statement and the SELECT in another.

answered Mar 14 '14 at 14:59

D Stanley

149,601
11
178
240

I think your first sentence sums up what I was trying to do. Damn. Know any work-arounds? The problem is that I am not trying to UPDATE, per se, I am trying to hold a value to use in a query. I hope that makes sense. – Mark C. Mar 14 '14 at 15:01
@Newbie then will the query I posted work? Also be aware that `ExecuteScalar` only returns _one value_ if you're grouping by `ELECT_BONUS_DFRL_SEQ_ID` then you may get multiple values back and any values after the first one will be ignored. – D Stanley Mar 14 '14 at 16:28
I'm not sure what your query does, exactly. So if it's Null or < 1 then 1. What is the `THEN 1` doing here? It means it's true, correct? If that's the case then what is the `ELSE` doing? – Mark C. Mar 14 '14 at 16:57
If the condition is true that column value will be `1`, otherwise it will be `MAX(e.ELECT_BONUS_DFRL_SEQ_ID) + 1` – D Stanley Mar 14 '14 at 17:01
I just realized that you're calling `MAX` on a column but grouping by that column, which doesn't seem right. Do you need the `GROUP BY`? Or do you need to group on a different column? – D Stanley Mar 14 '14 at 17:02
So, that's exactly what I was trying to do. The problem is, nothing is returned when the query is ran (even though it is executed). I expected to see a '1' under `e.ELECT_BONUS_DFRL_SEQ_ID` but I got nothing. – Mark C. Mar 14 '14 at 17:03
The `GROUP BY` was required by SQL I believe – Mark C. Mar 14 '14 at 17:05
You should be able to leave it off. It will just find the max of all records. – D Stanley Mar 14 '14 at 22:09

SELECT CASE to ExecuteScalar()

1 Answers1