Select Using 'Case' in SQL

SELECT using 'CASE' in SQL

This is just the syntax of the case statement, it looks like this.

SELECT 
  CASE 
    WHEN FRUIT = 'A' THEN 'APPLE' 
    WHEN FRUIT = 'B' THEN 'BANANA'     
  END AS FRUIT
FROM FRUIT_TABLE;

As a reminder remember; no assignment is performed the value becomes the column contents. (If you wanted to assign that to a variable you would put it before the CASE statement).

Select inside CASE THEN

Subqueries need parentheses:

SELECT (CASE WHEN ISNULL(s.rate, 0) = 0 
             THEN (SELECT TOP 1 pr.rate
                   FROM ProjectRates pr
                   WHERE (pr.projectID = p.ID) AND (pr.effectiveDate < GETDATE())
                   ORDER BY pr.effectiveDate DESC
                  )
             ELSE (SELECT TOP 1 sr.rate
                   FROM ShiftRates sr
                   WHERE (sr.shiftID = s.ID) AND (sr.effectiveDate < GETDATE()) 
                   ORDER BY pr.effectiveDate DESC                
                  ) --s.rate 
        END) AS rate
FROM Projects p INNER JOIN
     Shifts s 
     ON p.ID = s.projectID
WHERE p.ID = @projectID;

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

how to use CASE statement in MYSQL to select a row from results based on condition?

You description is really confusing.
Anyways, I have attempted to rewrite the query.
Instead of using

AND s2.rid IN (
                    SELECT  s1.rid

I have used a self join with the same stop table and alias it as s1 and used the join condition as s2.rid = s1.rid and s1.place = 'place1'

$sql="SELECT s2.stopno, s2.rid, b.busno, s2.pid, b.buskey, r.path,
        IF(s1.pid < s2.pid, IF(r.path=0, s2.rid, null), IF(r.path=1, s2.rid,null))
    FROM `stop` s2, bus b, `route` r, place p, `stop` s1
    WHERE s2.rid = r.rid
    AND r.bid = b.bid
    AND s2.pid = p.pid
    AND s2.rid = s1.rid and s1.place = 'place1'
    AND p.place = 'place2'"

You can also simplify this further instead of using multiple IF or CASE statements.

s1.pid < s2.pid THEN (SELECT S2.rid WHERE r.path = 0) ELSE (SELECT S2.rid WHERE r.path=1 )

You can define it using r.path in (0,1)

IF(s1.pid < s2.pid), IF(r.path in (0,1),s2.rid, NULL), NULL)

case statement in select query in sql

You were very close:

INSERT INTO HumanResources.departmentcopy(DepartmentID, GroupName, Name, temp)
SELECT  DepartmentID,
        GroupName,
        Name,
        CASE WHEN DepartmentID = 1 AND Name = 'Engineering and Research'
        THEN 'sucessful' ELSE 'unsucessful' END
FROM HumanResources.department

Using Case statement in Where clause in Oracle SQL

Your question is a bit ambiguous. I have assumed that country is an attribute in the table employees of data type VARCHAR.

SELECT * FROM employees
WHERE 
    (emp_id = v_emp_id AND country = 'USA') 
    OR (emp_id <= v_emp_id AND country != 'USA')

You might want to take a look at WHERE, OR and AND.

Quoting the OR page linked above:

If you use multiple logical operators in a statement, Oracle evaluates the OR operators after the NOT and AND operators. However, you can change the order of evaluation by using parentheses.

SQL Server, Select statement inside a 'case'

You can try like this..

(CASE when (SELECT COUNT(*) FROM tbl_CoTeacher COTH WHERE    COTH.CLAS_METG_TIME_PK=VSCS.CLAS_METG_TIME_PK
        AND ISNULL(COTH.DELT_FLAG,0)=0) > 0 THEN 1 ELSE 0 END) AS HASCOTEACH

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.

---

Related Topics