RE: SQL Statement Priority

From: Cristian Chelemen <>
Date: Thu, 24 Jan 2008 06:16:44 -0800 (PST)
Message-ID: <>

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.    


"Mark W. Farnham" <> 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: [] On Behalf Of Cristian Chelemen Sent: Thursday, January 24, 2008 7:46 AM To:
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.      


    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.      



  Looking for last minute shopping deals? Find them fast with Yahoo! Search.        

Never miss a thing. Make Yahoo your homepage.
Received on Thu Jan 24 2008 - 08:16:44 CST

Original text of this message