August 2, 2006 at 9:21 am
The following bit of code in the Select statement works fine:
(
MAX(R.Number_of_records)+1)
But I would like to change it to read
(MAX(R.Number_of_records)+1)
except put 1 where there is no records (rows) in the table.
August 2, 2006 at 9:31 am
I believe your question is if there are no records in the table, the query should return 1 instead of NULL.
Change the statement as
ISNULL(MAX(R.Number_od_Records),0) + 1
August 2, 2006 at 9:34 am
You might be on the right lines but it does not look right to me. Can you post a detailed explanation of the code please.
August 2, 2006 at 9:35 am
The following will return 1 or 0 when R.Number_of_records = 0, which is what I think you are trying to do:
((SIGN(R.Number_of_records) - 1) * -1)
August 2, 2006 at 9:38 am
MAX treats null values as zero so there is no need to use ISNULL. The MAX(bunch_of_nulls) + 1 is equal to 1. I think the problem here is that you need to post your whole select statement so we can see what's going on. The more detail you give, the better help you'll get.
August 2, 2006 at 9:39 am
No. I mean where there is no max value because there are no records in the table. When this is true I want it to return the value 1 and use this value '1' in the select statement.
August 2, 2006 at 9:42 am
Thanks John. I'll take your word for it.
August 2, 2006 at 9:50 am
"I mean where there is no max value because there are no records in the table. "
Are you talking about an OUTER JOIN then? How do you know how many non-existant rows to display as '1'?
Post your SELECT statement, DDL, sample data or something so that everyone has an idea of what you're after.
August 2, 2006 at 9:57 am
No it is just a simple insert statement.
August 2, 2006 at 10:04 am
Max will return null if there are no records in the table. please check...
August 2, 2006 at 10:11 am
John,
I have just tested your assumption and I don't think that you are correct. Unless I have made another mistake in my changes of code.
August 2, 2006 at 12:56 pm
If this is for an INSERT as stated, then it's obviously an attempt to generate an ascending sequence number, and Max() + 1 will only be safe if you keep the database in single-user mode. Use an Identity column.
August 3, 2006 at 2:37 am
MAX and NULL does not work as someone stated, run this:
DROP
TABLE #t
CREATE
TABLE #t
(
value
int NULL
)
SELECT
MAX(value) FROM #t
INSERT
#t(value)
SELECT
NULL
SELECT
MAX(value) FROM #t
and you will get NULL and NULL. At least I did.
I recommend that you use COALESCE rather than ISNULL since COALESCE is T-SQL 92 standard, not Microsoft T-SQL standard.
August 3, 2006 at 7:06 am
Yes, I did make an incorrect statement. My bad....I was thinking about the SUM function when I made my post. You are correct, MAX treats NULLs as unknowns as it should. (SUM treats them as zero's).
August 4, 2006 at 12:57 am
Mark,
I think you should give us more information if you want to receive useful answer. At the moment, everybody is groping in the dark, not knowing what you need and why.
What is table R? What contains column Number_of_records? Is it a physical table, or is it just derived table where Number_of_records is row count calculated using COUNT?
"If there are no records" in which table? Table R, or some other table, about which the info of number of records is stored in R? Please post the entire code, not only one line.
What will you do with the calculated value? If it is action that can be replaced by using identity column (as PW remarked), by all means use IDENTITY and skip any attempts on row number generation in the code.
Unless you explain more about the situation, I think that the first solution ISNULL(MAX(R.Number_od_Records),0) + 1 is correct and should work.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply