RE: SQL Statement Priority
Date: Thu, 24 Jan 2008 08:44:13 -0500
Message-ID: <013701c85e8f$35d3b3e0$1100a8c0@rsiz.com>
Do you mean like
select *
from p_emb_op_types
where op = 'ISSUE' and inst_id = '¶m'
union
select *
from p_emb_op_types
where op = 'ISSUE' and inst_id = '*'
and '¶m' not in (select inst_id from p_emb_op_types where op = 'ISSUE') ?
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Cristian Chelemen
Sent: Thursday, January 24, 2008 7:46 AM
To: oracle-l_at_freelists.org
Subject: SQL Statement Priority
Hi ALL!
I have a pretty nagging thing that bothers me for 2 days now and I want to find out your oppinion about it.
Prerequisites:
CREATE TABLE p_emb_op_types(inst_id VARCHAR2(10 BYTE) NOT NULL,
op VARCHAR2(20 BYTE) NOT NULL, status NUMBERNOT NULL); CREATE UNIQUE INDEX uk_emb_op_types ON p_emb_op_types (inst_id, op, status);
INSERT INTO p_emb_op_types (inst_id, op, status) VALUES ('*', 'ISSUE', 60); INSERT INTO p_emb_op_types (inst_id, op, status) VALUES ('2', 'ISSUE', 70); COMMIT; I want to build a SQL statement (will be part of a cursor) and I do not want to create a function for this... below italic pseudocode
SELECT * FROM p_emb_op_types
WHERE op = 'ISSUE'
AND inst_id = (IF ¶m exists in inst_id column where op = 'ISSUE'
THEN ¶m ELSE '*' END IF;)
And I'll give you some examples of what it'll do:
if ¶m = '2' then retrieve '2', 'ISSUE', 70 line
if ¶m = '3' then retrieve '*', 'ISSUE', 60 line
if ¶m = '*' then retrieve '*', 'ISSUE', 60 line
I hope that I made myself clear on this issue.
Cristian
Looking for last minute shopping deals? Find <http://us.rd.yahoo.com/evt=51734/*http:/tools.search.yahoo.com/newsearch/ca tegory.php?category=shopping> them fast with Yahoo! Search.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 24 2008 - 07:44:13 CST