Home » SQL & PL/SQL » SQL & PL/SQL » Finding exact row in case of matching (Oracle 10g)
icon2.gif  Finding exact row in case of matching [message #667770] Mon, 15 January 2018 01:09 Go to next message
maanosajnabi
Messages: 3
Registered: December 2017
Junior Member
I have a table having Unit and Price Column, which looks like

Unit Price
Null 500
CARDIO 800


I want to select once Price, if paramter is passed CARDIO, it should return 800 price and in case of any other parameter it should return 500.

I am applying following query and it returns both rows in case of CARDIO parameter.

select * from cattry t where nvl(t.unit, '&p') = '&p';

can anyone help me in this regard.
thanks
Re: Finding exact row in case of matching [message #667771 is a reply to message #667770] Mon, 15 January 2018 01:29 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
please use query:


select   * 
from     cattry t 
where    nvl(t.unit,'xx') = nvl('&p','xx')

Re: Finding exact row in case of matching [message #667772 is a reply to message #667771] Mon, 15 January 2018 01:49 Go to previous messageGo to next message
quirks
Messages: 81
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 Go to previous messageGo to next message
cookiemonster
Messages: 13340
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 #668353 is a reply to message #667775] Mon, 19 February 2018 05:41 Go to previous messageGo to next message
browncat
Messages: 9
Registered: May 2015
Junior Member
The query can be.....
SELECT * FROM cattry WHERE unit = 'CARDIO';
Re: Finding exact row in case of matching [message #668355 is a reply to message #668353] Mon, 19 February 2018 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 65980
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 05 May 2015 10:49
@browncat,

Welcome to the forum, it is nice to see you want to help but test your solutions before posting them.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Why all your answers are just silly?

[Updated on: Mon, 19 February 2018 06:02]

Report message to a moderator

Re: Finding exact row in case of matching [message #668357 is a reply to message #668355] Mon, 19 February 2018 06:22 Go to previous message
quirks
Messages: 81
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. Wink

[Updated on: Mon, 19 February 2018 06:30]

Report message to a moderator

Previous Topic: Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results i
Next Topic: NVL Vs OR condition
Goto Forum:
  


Current Time: Mon Nov 19 15:40:32 CST 2018