RE: SQL Statement Priority

From: Cristian Chelemen <cristian_chelemen_at_yahoo.com>
Date: Thu, 24 Jan 2008 06:16:44 -0800 (PST)
Message-ID: <759477.27322.qm@web34203.mail.mud.yahoo.com>

Thank you, Mark, for the suggestion!
  Of course, the result of this query is the one that I expected. (Personally I thought of using "where exists" for this query.) But, as you can see there are three queries, not to mention that isn't an elegant solution in developing.    

  Cristian

"Mark W. Farnham" <mwf_at_rsiz.com> wrote:

        v\:* {behavior:url(#default#VML);}  o\:* {behavior:url(#default#VML);}  w\:* {behavior:url(#default#VML);}  .shape {behavior:url(#default#VML);}                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_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 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.        

---

Never miss a thing. Make Yahoo your homepage.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 24 2008 - 08:16:44 CST