|
|
Re: Finding exact row in case of matching [message #667772 is a reply to message #667771] |
Mon, 15 January 2018 01:49 |
|
quirks
Messages: 82 Registered: October 2014
|
Member |
|
|
You could try this:WITH
TEST_TAB(UNIT, PRICE) AS
(SELECT NULL, 500
FROM DUAL
UNION ALL
SELECT 'CARDIO', 800 FROM DUAL),
TEST_TAB_RANK AS
(SELECT UNIT, PRICE, CASE WHEN UNIT = '&P' THEN 0 WHEN UNIT IS NULL THEN 1 ELSE 2 END FETCH_ME
FROM TEST_TAB),
PREPARE_FILTER AS
(SELECT UNIT, PRICE, CASE WHEN FETCH_ME = MIN(FETCH_ME) OVER (PARTITION BY NULL) THEN 'Y' ELSE 'N' END FILTER
FROM TEST_TAB_RANK)
SELECT UNIT, PRICE
FROM PREPARE_FILTER
WHERE FILTER = 'Y'
|
|
|
Re: Finding exact row in case of matching [message #667775 is a reply to message #667772] |
Mon, 15 January 2018 03:14 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Or this:
WITH
TEST_TAB(UNIT, PRICE) AS
(SELECT NULL, 500
FROM DUAL
UNION ALL
SELECT 'CARDIO', 800 FROM DUAL)
select unit, price
from (
SELECT unit, price, 1 as id
from test_tab
where unit = '&p'
union all
select unit, price, 2 as id
from test_tab
where unit is null
order by id
)
where rownum = 1;
|
|
|
|
|
Re: Finding exact row in case of matching [message #668357 is a reply to message #668355] |
Mon, 19 February 2018 06:22 |
|
quirks
Messages: 82 Registered: October 2014
|
Member |
|
|
How about:
WITH
TEST_TAB(UNIT, PRICE) AS
(SELECT NULL, 500 FROM DUAL
UNION ALL
SELECT 'CARDIO', 800 FROM DUAL)
SELECT *
FROM TEST_TAB
WHERE CASE
WHEN '&P' = 'CARDIO' THEN
CASE WHEN UNIT = 'CARDIO' THEN 1 ELSE 0 END
ELSE
CASE WHEN UNIT = 'CARDIO' THEN 0 ELSE 1 END
END = 1
You might replace the last two 'CARDIO's in the two sub CASEs wit '&P' as well.
[Updated on: Mon, 19 February 2018 06:30] Report message to a moderator
|
|
|