Howdy, Stranger!

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

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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

gdesaigdesai Posts: 5Member
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

  • gdesaigdesai Posts: 5Member
    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 ');



  • gdesaigdesai Posts: 5Member
    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';





  • gdesaigdesai Posts: 5Member
    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.


  • gdesaigdesai Posts: 5Member
    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.