| Two MAX Functions in a Single SELECT [message #685398] |
Thu, 23 December 2021 18:01  |
 |
whdyck
Messages: 25
Registered: May 2017
|
Junior Member |
|
|
If I run the following code:
SELECT WO.fldWoID, WO.fldUnitNo
FROM usrWo.tblWoMstOrderMaster WO
WHERE WO.fldWoID = 1850230;
1850230, NULL
But if I run this:
SELECT MAX(WO.fldWoID), MAX(WO.fldUnitNo)
FROM usrWo.tblWoMstOrderMaster WO
WHERE WO.fldWoID = 1850230;
NULL, NULL
I would have expected 1850230, NULL for the second query.
fldWoID is NUMBER(11), and fldUnitNo is VARCHAR2(20).
Can anyone explain why?
Thanks.
Wayne
|
|
|
|
| Re: Two MAX Functions in a Single SELECT [message #685400 is a reply to message #685398] |
Fri, 24 December 2021 00:41  |
 |
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Michel Cadot wrote on Mon, 09 March 2020 19:03
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
...
SQL> create table t (c1 number, c2 number);
Table created.
SQL> insert into t values(1850230, NULL);
1 row created.
SQL> commit;
Commit complete.
SQL> select c1, c2 from t where c1=1850230;
C1 C2
---------- ----------
1850230
1 row selected.
SQL> select max(c1), max(c2) from t where c1=1850230;
MAX(C1) MAX(C2)
---------- ----------
1850230
1 row selected.
|
|
|
|
| Re: Two MAX Functions in a Single SELECT [message #685403 is a reply to message #685400] |
Fri, 24 December 2021 11:03 |
|
whdyck
Messages: 25
Registered: May 2017
|
Junior Member |
|
|
The anomaly arises after adding an index.
Here's how to reproduce the problem:
CREATE TABLE TestMax(
nTestMaxID NUMBER, cTestName VARCHAR2(255));
CREATE INDEX IDXTestMax ON TestMax
(nTestMaxID, cTestName);
INSERT INTO TestMax(nTestMaxID, cTestName)
VALUES (1, NULL);
COMMIT;
SELECT nTestMaxID, cTestName FROM TestMax WHERE nTestMaxID = 1;
SELECT MAX(nTestMaxID)
, MAX(cTestName)
FROM TestMax
WHERE nTestMaxID = 1;
Result: NULL, NULL
But if you now drop the index, the results will change:
DROP INDEX IDXTestMax;
SELECT MAX(nTestMaxID)
, MAX(cTestName)
FROM TestMax
WHERE nTestMaxID = 1;
Result: 1, NULL
Why should an index change the result set?
Wayne
|
|
|
|
|
|
|
|
| Re: Two MAX Functions in a Single SELECT [message #685410 is a reply to message #685405] |
Tue, 28 December 2021 16:34 |
|
whdyck
Messages: 25
Registered: May 2017
|
Junior Member |
|
|
In the past, when I've wanted to capture values into local variables, I've used MAX and INTO to avoid testing for error ORA-01403 ("no data found"). Now I'll have to re-evaluate that practice.
If I prepare the table as before, with an index:
CREATE TABLE TestMax
(
nTestMaxID NUMBER
, cTestName VARCHAR2(255)
);
CREATE INDEX IDXTestMax
ON TestMax(nTestMaxID
, cTestName);
INSERT INTO TestMax(nTestMaxID
, cTestName)
VALUES (1
, NULL);
COMMIT;
I can run this successfully:
SET SERVEROUTPUT ON
DECLARE
cMaxTestName TestMax.cTestName%TYPE;
BEGIN
SELECT MAX(cTestName)
INTO cMaxTestName
FROM TestMax
WHERE nTestMaxID = 1;
DBMS_OUTPUT.PUT_LINE('cMaxTestName: ' || cMaxTestName);
END;
cMaxTestName:
Even if the WHERE clause hits no records, it still stores a NULL in cMaxTestName:
SET SERVEROUTPUT ON
DECLARE
cMaxTestName TestMax.cTestName%TYPE;
BEGIN
SELECT MAX(cTestName)
INTO cMaxTestName
FROM TestMax
WHERE nTestMaxID = 2;
DBMS_OUTPUT.PUT_LINE('cMaxTestName: ' || cMaxTestName);
END;
cMaxTestName:
However, if I want to store two such MAX values using a single SELECT, and if I use a dummy GROUP BY as you suggested, it works only when the WHERE clause has a record to aggregate:
SET SERVEROUTPUT ON
DECLARE
nMaxTestMaxID TestMax.nTestMaxID%TYPE;
cMaxTestName TestMax.cTestName%TYPE;
BEGIN
SELECT MAX(nTestMaxID)
, MAX(cTestName)
INTO nMaxTestMaxID
, cMaxTestName
FROM TestMax
WHERE nTestMaxID = 1
GROUP BY NULL;
DBMS_OUTPUT.PUT_LINE('nMaxTestMaxID: ' || nMaxTestMaxID);
DBMS_OUTPUT.PUT_LINE('cMaxTestName: ' ||cMaxTestName);
END;
nMaxTestMaxID: 1
cMaxTestName:
However, if the WHERE clause has no record to aggregate, I'll get error ORA-01403 ("no data found") with the GROUP BY clause:
SET SERVEROUTPUT ON
DECLARE
nMaxTestMaxID TestMax.nTestMaxID%TYPE;
cMaxTestName TestMax.cTestName%TYPE;
BEGIN
SELECT MAX(nTestMaxID)
, MAX(cTestName)
INTO nMaxTestMaxID
, cMaxTestName
FROM TestMax
WHERE nTestMaxID = 2
GROUP BY NULL;
DBMS_OUTPUT.PUT_LINE('nMaxTestMaxID: ' || nMaxTestMaxID);
DBMS_OUTPUT.PUT_LINE('cMaxTestName: ' ||cMaxTestName);
END;
Question: Is there another workaround that would allow me to SELECT multiple values INTO local variables, even if the WHERE clause has no records to aggregate (and would then store NULL values in the locals)? If not, I guess I'll have to run two SELECTs: one to identify whether the WHERE clause hits a record and another to run only if the first SELECT returned a value.
Thanks.
Wayne
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Two MAX Functions in a Single SELECT [message #685429 is a reply to message #685427] |
Wed, 05 January 2022 07:33 |
Frank
Messages: 7897
Registered: March 2000
|
Senior Member |
|
|
To me it sounds like you run the risk of hiding errors if you (have to) use max if you only want to return a single value. Without knowing the details of your specific situation, it sounds like slapping distinct on every query, rather then fixing the query, fixing the datamodel or fixing the data.
Initially you mentioned that you do it to not having to catch no_data_found, however with Michel's approach you now have both. Might as well lose the max then.
Just my opinion though.
|
|
|
|
) 1 Vote
] 
Search
Help
Members
Register
Login
Home
