SQL Statement Priority

From: Cristian Chelemen <cristian_chelemen_at_yahoo.com>
Date: Thu, 24 Jan 2008 04:46:17 -0800 (PST)
Message-ID: <91836.31150.qm@web34206.mail.mud.yahoo.com>

   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 them fast with Yahoo! Search.
--

http://www.freelists.org/webpage/oracle-l Received on Thu Jan 24 2008 - 06:46:17 CST