CODEWITHSUNDEEP
sqlplsql
user2963075
user2963075

Reputation: 23

How to add if-then-else conditions in a select query?

I would like to apply the following conditions in a select query.

IF  (TYPE = MBUSA or SDSA or MDSA OR PC) and (FINAL_STATUS = ACTIVE OR EXECUTED) 
then GET ID and Parent_ID
ELSEIF (TYPE = MSA) and (FINAL_STATUS = ACTIVE OR EXECUTED)
then  GET ID---(USING the ID from the else if condition AS FILTER, 
IF TYPE = (DSA OR BUSA) AND (FINAL_STATUS = ACTIVE OR EXECUTED) 
then GET ID and Parent_ID)

Do I need to write a procedure?

Following is the database structure:

http://i.imgur.com/L1VqOE3.png

Upvotes: 1

Views: 77146

Answers (2)

A B
A B

Reputation: 4158

You can use CASE to implement IF-THEN-ELSE in PL/SQL - this is just an example:

    select case field1 WHEN '1' THEN 'VALUE1'
                       WHEN '2' THEN 'VALUE2'
                       ELSE 'VALUEOTHER'
                       END,
           case field2 WHEN '1' THEN 'VALUE1'
                       WHEN '2' THEN 'VALUE2'
                       ELSE 'VALUEOTHER'
                       END                  
    from (                   

    select '1' "FIELD1",
           '2' "FIELD2"
    from dual
    );

You cannot use the CASE statement on multiple columns; however, if you need to compare multiple fields in the same CASE statement, you could use a workaround like this:

    select case field1 || '_' || field2
                       WHEN '1_1' THEN 'FIELD1-IS-VALUE1 AND FIELD2-IS-VALUE1'
                       WHEN '1_2' THEN 'FIELD1-IS-VALUE1 AND FIELD2-IS-VALUE2'
                       WHEN '2_1' THEN 'FIELD1-IS-VALUE2 AND FIELD2-IS-VALUE1'
                       WHEN '2_2' THEN 'FIELD1-IS-VALUE2 AND FIELD2-IS-VALUE2'
                       ELSE 'VALUEOTHER'
                       END
    from (                   

    select '1' "FIELD1",
           '2' "FIELD2"
    from dual
    );

If the SQL statement becomes difficult to read it may be better to use views with the SQL statement or use PL/SQL.

References:

https://dba.stackexchange.com/questions/18239/using-same-case-when-conditions-for-multiple-query-columns

Upvotes: 0

talegna
talegna

Reputation: 2403

The CASE statement can be used as an equivalent of an IF.. THEN... ELSE... statement. Not quite sure how you are wanting to select them as your question is unclear so here's a couple of suggestions.

To select a single column with a string representation of the conditional formatting:

SELECT
    CASE
        WHEN (TYPE IN ('MBUSA','SDSA','MDSA','PC') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED')) 
            THEN ID + ' ' + P_ID
        WHEN ((TYPE = 'MSA') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED'))
            THEN ID
        WHEN (TYPE IN ('DSA','BUSA') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED')) 
            THEN ID + ' ' + P_ID
        ELSE ''
    END AS columnName
FROM
    tableName

To select each into separate columns

SELECT
    CASE
        WHEN (TYPE IN ('MBUSA','SDSA','MDSA','PC') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED')) 
            THEN ID
        WHEN ((TYPE = 'MSA') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED'))
            THEN ID
        WHEN (TYPE IN ('DSA','BUSA') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED')) 
            THEN ID
        ELSE ''
    END AS columnOneName
    CASE
        WHEN (TYPE IN ('MBUSA','SDSA','MDSA','PC') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED')) 
            THEN P_ID
        WHEN ((TYPE = 'MSA') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED'))
            THEN NULL
        WHEN (TYPE IN ('DSA','BUSA') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED')) 
            THEN  P_ID
        ELSE ''
    END AS columnTwoName
FROM
    tableName

Though looking at the above (I've probably transcribed your logic incorrectly) there are three case statements when you could get away with 2:

CASE
    WHEN (TYPE IN ('MBUSA','SDSA','MDSA','PC','DSA','BUSA') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED')) 
        THEN ID + ' ' + P_ID
    WHEN ((TYPE = 'MSA') AND (FINAL_STATUS IN ('ACTIVE','EXECUTED'))
        THEN ID
    ELSE ''
END AS columnName

I know you've put a screenshot up now, but an SQL Fiddle would probably be more helpful or more detail so that table names etc. can be easily

Useful Links:

http://technet.microsoft.com/en-us/library/ms181765.aspx

SQL Case Statement Syntax?

http://blog.sqlauthority.com/2007/04/14/sql-server-case-statementexpression-examples-and-explanation/

Upvotes: 5

Related Questions