CODEWITHSUNDEEP
sqloracle-databasevariablesnull
dee
dee

Reputation: 609

How can I ensure a null is assigned to a variable when using MAX in Oracle?

I have another question dealing with Nulls in Oracle.

I have a small table that is used as a session table. Each row is a specific session. If a session is finished successfully the final column indicates a version number or null if the session is dropped.

This column can be null and I want to select either the max version number or null into a sessionNumber variable . This was the origanal I had set up

SELECT MAX (VerNumber) INTO sessionNumber
FROM Table_A
WHERE sessionDate = -- some date
AND VerNumber IS NOT NULL;

this returns one row when the col is null

I tried using NVL like this

SELECT NVL(MAX (VerNumber),NULL) INTO sessionNumber
FROM Table_A
WHERE sessionDate = -- some date
AND VerNumber IS NOT NULL;

To hopefully clear up confusion, the NOT NULL condition exists to prevent extra sessions by the same user for the same date. TBH in this situation even if the NOT NULL condition is removed I still get a (empty) row returned. I think there is something I'm missing with Nulls and aggregate functions.

but it doesn't work. I've also seen where I could use an exception but that seems like a convoluted fix.

Any guidance is greatly appreciated.

Thanks.

Further clarification as requested.

My table has four columns user, date, state, and verNumber

user and date are identifier columns ( not officially a PK but you can look at them that way).

state indicates if session was completed or not verNumber indicates the the number of times that a session for that date was completed and can be null if no session for that date was completed.

I have a variable sessionNumber that I would like to assign either the MAX(VerNumber) available for that date or null when the date is unavailable or the value in VerNumber is null.

sample rows in Table_A

USER    | DATE       | STATE | VERNUMBER
'AName' | 2012-06-25 | 'YES' | 1
'CName' | 2012-06-25 | 'YES' | 2
'BName' | 2012-06-26 | 'NO'  | --NULL

so for date 06-25 I would expect 2 to be the value and for 06-26 I would expect null.

Upvotes: 0

Views: 2222

Answers (2)

Charleh
Charleh

Reputation: 14012

Is this close to what you want?

http://sqlfiddle.com/#!4/ca465/1

Barking up wrong tree?

Edit: forget that last one

...does this look right to you!!?

http://sqlfiddle.com/#!4/44e50/21

I'm thinking maybe you want a single entry for each date though with the session which was last (no matter the user) - either way this gives you some hints

Edit: Here's that query:

http://sqlfiddle.com/#!4/4163d/1

Which I think mirrors your output!

For everyones benefit (and in case SQL fiddle ever explodes!), the final query I did was:

SELECT Table_A.* FROM 
(
    SELECT
        SessionDate, 
        MAX(CASE WHEN VerNumber IS NULL THEN 'A' ELSE VerNumber END) as Ver
    FROM Table_A
    GROUP BY SessionDate
) TD
INNER JOIN Table_A 
ON NVL(Table_A.VerNumber, 'A') = TD.Ver 
AND Table_A.SessionDate = TD.SessionDate

So basically just used a CASE to get the MAX of an expression on the VerNumber column but use an alpha character to ensure that the NULLs in that column were selected by the MAX. The outer query joins to the inner on an expression using NVL() which allows the NULL to be joined to the 'A' in the inner query. Not sure if collation would cause issues here (does collation ever change the sort order of alpha vs numeric??)

Upvotes: 2

DCookie
DCookie

Reputation: 43523

If you use an aggregate function alone in a select you'll always get a row. If you want no rows returned, you need to add another column and use GROUP BY and filter on that other column, something along the lines of:

SELECT VerNumber, MAX (VerNumber) 
  INTO sessionNumber 
  FROM Table_A 
 WHERE sessionDate = -- some date 
   AND VerNumber IS NOT NULL
 GROUP BY VerNumber;

I don't know how your data is structured, so this may not be the exact solution. I'm also a bit confused, like the other who have commented, on exactly what you're trying to do.

Bear in mind, however, that if you do this you WILL need an exception handler, because you'll get a NO DATA FOUND exception.

Upvotes: 1

Related Questions