RE: SQL Statement Priority

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <cristian_chelemen@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 24 Jan 2008 08:44:13 -0500

Do you mean like

 

select *

from p_emb_op_types

where op = 'ISSUE' and inst_id = '&param'

union

select *

from p_emb_op_types

where op = 'ISSUE' and inst_id = '*'

   and '&param' not in (select inst_id from p_emb_op_types where op =
'ISSUE')

 

?

 

  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Cristian Chelemen
Sent: Thursday, January 24, 2008 7:46 AM
To: oracle-l@xxxxxxxxxxxxx
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 NUMBER
NOT 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 &param exists in inst_id column where op = 'ISSUE'

                      THEN &param

                      ELSE '*'

                      END IF;)

 

And I'll give you some examples of what it'll do:

if &param = '2' then retrieve '2', 'ISSUE', 70 line

if &param = '3' then retrieve '*', 'ISSUE', 60 line

if &param = '*' 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.

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 01-2008