select one record for the first matching condition only in four where

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

Comments

  • 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 ');



  • I tried Value Match CASE Expression idea based query, it's retrieving all of the matching conditions.I need only one record from first matching condition.

    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 stop
    FROM temp
    )
    WHERE stop = 'stop';





  • Query should select only one record based on the existing data. It has 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 ');

    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.


  • This worked for me.

    select d from (
    select d ,
    CASE WHEN b= @b AND c= @c
    THEN 1
    WHEN b= @b AND c= 'none'
    THEN 2
    WHEN b= 'none' AND c= @c
    THEN 3
    WHEN b= 'none' AND c= 'none'
    THEN 4
    END priority
    from temp
    where a=@a
    ORDER BY priority
    )
    where ROWNUM = 1;
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion