How to Execute Different Select Statements Based on a Case

How to use CASE Statement for Multiple Select Statements in SQL

You are quite close:

SELECT (CASE rp.PaymentType
            WHEN  'Sale'
            THEN (SELECT v.Name FROM Vendor v WHERE v.VendorID = rp.AccountID)
            WHEN  'Purchase'
            THEN (SELECT c.Name FROM Customers c WHERE c.CustID = rp.AccountID)
        END)
FROM ReceivePayment rp;

You just need parentheses around the subqueries. Note: You need to be sure that the subqueries only return 0 or 1 row, or you will get an error.

Note that I also added table aliases to simplify the query.

create select statement based on case statement in sql

In T-Sql (MS SQL Server) you can dynamically compose your SQL statement and then use the sp_executesql method to execute it. Based on your case statement you can build a SELECT statement using a variable and then use sp_executesql to run it. The one thing to be aware of is that you need to make sure you clean any input you get from users if you are using text directly from users to prevent SQL injection attacks.

For example,

DECLARE @sql NVARCHAR(200)

SELECT @sql = CASE @tableToUse
    WHEN 'Table1' THEN 'SELECT * FROM Table1'
    WHEN 'Table2' THEN 'SELECT * FROM Table2'
    ELSE 'Table3' THEN 'SELECT * FROM Table2'
    END

EXECUTE sp_executesql @sql

You should be able to do something similar in other versions of SQL.

Using a case column within another case in select clause

The simplest way is to use a subquery that returns the column discount_rule:

select t.client, t.discount, t.discount_rule,
  case 
    when discount < discount_rule then 1 
    else 0 
  end status
from (
  select client, discount,
    case 
      when sales_avg > 10000 then 30 
      when sales_avg > 5000 then 20 
      else 0 
    end discount_rule
  from sales
) t

CASE WITH MULTIPLE SELECT STATEMENTS

I can speculate that the code that you intend is:

select . . . ,
       coalesce(u1.onput_id, u.onput_id) as onput_id,
       coalesce(u2.onput_id, u1.onput_id, u.onput_id) as onput_id2
from unit u left join
     unit u1
     on u.unit_id_turnup = u1.id left join
     unit u2
     on u1.unit_id_turnup = u2.id

Without sample data and a clear explanation, this is really just a guess. However, this appears to be what you are attempting.

Two select statement with case

You can make use of a simple if else if and execute both the queries.

   IF(@value=1)
    BEGIN
      SELECT  count (CASE WHEN col1 = 9 THEN 1 ELSE 0 END  )AS "matches" ,
                  CAST( CASE WHEN col1 = 9 THEN 1 ELSE 0 END  AS VARCHAR(10)) + ' / 1' AS "match by"
                  FrOM table a
                  where ( SELECT  CASE WHEN col1 = 9 THEN 1 ELSE 0 END  AS "matches"  )  >= 1
                  group by  ( CASE WHEN col1 = 9 THEN 1 ELSE 0 END ) 
                  )
    END
    ELSE IF(@value=2)
    BEGIN 
       SELECT  count (CASE WHEN col1 = 9 THEN 1 ELSE 0 END    +  
                    CASE WHEN col2 = 10 THEN 1 ELSE 0 END  
                    )AS "matches" ,
                    CAST( CASE WHEN col1 = 9 THEN 1 ELSE 0 END 
                    +  CASE WHEN col2 = 10 THEN 1 ELSE 0 END AS VARCHAR(10)) + '/ 2'  AS "match by"
                    FrOM table a
                    where ( SELECT  CASE WHEN col1 = 9 THEN 1 ELSE 0 END + 
                     CASE WHEN col2 = 10 THEN 1 ELSE 0 END AS "NUM_OF_MATCHES"  )  >= 1
                    group by  ( CASE WHEN col1 = 9 THEN 1 ELSE 0 END+ 
                     CASE WHEN col2 = 10 THEN 1 ELSE 0 END ) 
    END

Can I use CASE of IF statements to conditionally run different SELECT statements?

As long as the two queries return the same columns, you can write a query for this.

In your example:

SELECT invoiceid
FROM datamanagement_sys
WHERE invoiceid = COALESCE(:variablefromlogi, 1111);

More general:

SELECT invoiceid
FROM datamanagement_sys
WHERE invoiceid = 1111 AND :variablefromlogi IS NULL
UNION ALL
SELECT invoiceid
FROM datamanagement_sys
WHERE invoiceid = :variablefromlogi;

As you can see, you can just glue different queries (that return the same columns) together with UNION ALL and you use your variable to have only one of the queries return values.

Executing different prepared statement based on conditions of case when clause

I've found the answer for this, which includes the use of procedure

DELIMITER $$

CREATE PROCEDURE latmed()
BEGIN 
IF (SELECT COUNT(*) FROM STATION)%2=0 THEN
    SET @foo1 = (SELECT ROUND(COUNT(*)/2) FROM STATION);
    SET @foo2 = (SELECT ROUND((COUNT(*)+2)/2) FROM STATION);
    PREPARE STMT2 FROM 'SELECT ROUND((((SELECT ROUND(LAT_N,4) FROM STATION ORDER BY LAT_N LIMIT ?,1) + (SELECT ROUND(LAT_N,4) FROM STATION ORDER BY LAT_N LIMIT ?,1))/2),4)';
    EXECUTE STMT2 USING @foo1, @foo2;
ELSE
    SET @foo = (SELECT ROUND((COUNT(*)-1)/2) FROM STATION);
    PREPARE STMT1 FROM 'SELECT ROUND(LAT_N,4) FROM STATION ORDER BY LAT_N LIMIT ?,1';
    EXECUTE STMT1 USING @foo;
END IF;
END$$

DELIMITER ;
CALL latmed()

I think it's a bit long so if anyone has a shorter code can answer my question. Thanks

---

Related Topics