Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2007
    Posts
    8

    Unanswered: select the record only for the first matching condition in four where conditions

    I need your suggestion to write one tricky SQL query to select only one record from database on the following condition.I explained simple table structure below.I have a table temp with four columns a,b,c,d in it.

    I have to select column d from this temp table based on the following four conditions.If it matches any condition, It should skip other conditions, that's the tricky thing.

    Conditions order is like shown below.

    1) a='argument1' and b='argument2' and c='argument3'(If it matches this condition, it should stop selecting below 3 conditions)

    2) a='argument1' and b='argument2' and c='none'(If it matches this condition, it should stop selecting below 2 conditions)

    3) a='argument1' and b='none' and c='argument3'(If it matches this condition, it should stop selecting below condition)

    4) a='argument1' and b='none' and c='none'(this is last condition)

    If I use OR operator , it matches all of those other conditions too.I never wrote query like this before.

    I greatly appreciate if somebody sheds light on me to start writing this query with a simple suggestion.

    Thanks,
    GD

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What do you mean with "stop selecting ..."?

    You are aware how SQL works? A condition in the WHERE clause is applied independently to each and every row from the table (base table, view, temp table, ...) in the FROM clause. Thus, "OR"-ing your 4 conditions seems to be perfectly fine to me.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    GD

    Code:
    select   d
    from     my_temp_table
    where   ( a='argument1' and b='argument2' and c='argument3' )
            or ( a='argument1' and b='argument2' and c='none' )
            or ( a='argument1' and b='none' and c='argument3' )
            or ( a='argument1' and b='none' and c='none' )
    limit 1;
    This code should work for MySQL but if it's Sybase/MS SQL Server you may need to use set rowcount 1. I believe it does what you're asking and it isn't too difficult

    Mike

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "stop evaluating" sounds like the logical cascade inside a CASE expression
    Code:
    select d from (
    select d
         , CASE WHEN a='argument1' and b='argument2' and c='argument3'
                THEN 'stop'
                WHEN a='argument1' and b='argument2' and c='none'
                THEN 'stop'
                WHEN a='argument1' and b='none' and c='argument3'
                THEN 'stop'
                WHEN a='argument1' and b='none' and c='none'
                THEN 'stop'
                ELSE 'keep going'
           END as stop
      from daTable
    ) as data
    where stop = 'keep going'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2007
    Posts
    8
    I appreciate your quick response.I am using oracle9i.
    your Value Match CASE Expression idea help me to start thinking about logical cascade but these two queries are not working correctly.I would really appreciate if you check the query again.

    Thanks a lot.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i cannot check my query because (1) you did not supply table structure, sample rows, and expected outcome, and (2) i don't do oracle any more

    moving thread to oracle forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2007
    Posts
    8
    I'm getting this error for the r937 'q query.
    Error: java.sql.SQLException: ORA-00907: missing right parenthesis , SQL State: 42000, Error Code: 907

    I will appreciate if any body can help me.

  8. #8
    Join Date
    Jun 2007
    Posts
    8
    Table script and data:

    CREATE TABLE temp
    (
    a NUMBER(22),
    b CHAR(50),
    c CHAR(25),
    d CHAR(10)
    );


    INSERT INTO temp (a,b,c,d) VALUES (1600,'Bonds ','none ','012000000 ');
    INSERT INTO temp (a,b,c,d) VALUES (1600,'none ','Subsidiary ','199999900 ');
    INSERT INTO temp (a,b,c,d) VALUES (1600,'Bonds ','Investee ','199999900 ');
    INSERT INTO temp (a,b,c,d) VALUES (1600,'none ','none ','199999900 ');

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by ganeshdesai
    I'm getting this error for the r937 'q query (...) ORA-00907: missing right parenthesis
    Oh, come on! And you don't know how to solve a missing parenthesis error?!? You only need to slightly modify Rudy's query and it'll work.

    BTW, don't use CHAR unless necessary - change it to VARCHAR2.

    (I have also changed column a's datatype to a character; it was easier for me to do that instead of modifying a query)
    Code:
    SQL> CREATE TABLE temp
      2  (
      3  a VARCHAR2(22),
      4  b VARCHAR(50),
      5  c VARCHAR(25),
      6  d VARCHAR(10)
      7  );
    
    Table created.
    
    SQL>
    SQL>
    SQL> INSERT INTO temp (a,b,c,d) VALUES (1600,'Bonds ','none ','012000000 ');
    
    1 row created.
    
    SQL> INSERT INTO temp (a,b,c,d) VALUES (1600,'none ','Subsidiary ','199999900 ')
    ;
    
    1 row created.
    
    SQL> INSERT INTO temp (a,b,c,d) VALUES (1600,'Bonds ','Investee ','199999900 ');
    
    
    1 row created.
    
    SQL> INSERT INTO temp (a,b,c,d) VALUES (1600,'none ','none ','199999900 ');
    
    1 row created.
    
    SQL>
    SQL> SELECT d FROM (
      2  SELECT d
      3       , CASE WHEN a='argument1' AND b='argument2' AND c='argument3'
      4              THEN 'stop'
      5              WHEN a='argument1' AND b='argument2' AND c='none'
      6              THEN 'stop'
      7              WHEN a='argument1' AND b='none' AND c='argument3'
      8              THEN 'stop'
      9              WHEN a='argument1' AND b='none' AND c='none'
     10              THEN 'stop'
     11              ELSE 'keep going'
     12         END stop
     13    FROM temp
     14  )
     15  WHERE stop = 'keep going';
    
    D
    ----------
    012000000
    199999900
    199999900
    199999900

  10. #10
    Join Date
    Jun 2007
    Posts
    8
    I knew about common missing parenthesis error, I got the same result after modifying it but this is not what i expected from my query.

    I have to get only one record based on first matching condition.This is retrieving all of the records.

  11. #11
    Join Date
    Jun 2007
    Posts
    8
    I have different d values in this data script.
    INSERT INTO temp (a,b,c,d) VALUES (1500,'Bonds ','none ','013000000 ');
    INSERT INTO temp (a,b,c,d) VALUES (1500,'none ','Subsidiary ','199999800 ');
    INSERT INTO temp (a,b,c,d) VALUES (1500,'Bonds ','Investee ','199999700 ');
    INSERT INTO temp (a,b,c,d) VALUES (1500,'none ','none ','199999600 ');

  12. #12
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Please, post the output you expect based on the sample data given.

  13. #13
    Join Date
    Jun 2007
    Posts
    8
    Query should select only one record based on the existing data.

    If I pass 1500,'Bonds ','Investee ' as a,b,c values it should match first condition and need to result only one record with d value as 199999700 .

    If I pass 1500,'Bonds ','ANYVALUE ' , this should match a='1500' and b='Bonds ' and c='none' where clause and need to result d as 013000000.

    If I pass 1500,'ANYVALUE ','ANYVALUE ' , this should match a='1500' and b='none' and c='none' where clause and need to result d as 199999600.

    Thanks.

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I think I get it. You want to select only 1 record - preferably using the 1st condition; if none found get 1 record using 2nd condition, etc...

    So that could be done like this:

    Code:
    SELECT ... FROM
    ( SELECT CASE WHEN b='argument2' AND c='argument3'
                  THEN 1
                  WHEN b='argument2' AND c='none'
                  THEN 2
                  WHEN AND b='none' AND c='argument3'
                  THEN 3
                  WHEN b='none' AND c='none'
                  THEN 4
                  END priority,
             ...
      FROM   my_table
      WHERE  a = 'argument1'
      ORDER BY priority
    )
    WHERE ROWNUM = 1

  15. #15
    Join Date
    Jun 2007
    Posts
    8
    Thank, I appreciate it. It works perfectly.I got another solutions which is also working using decode function.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •