Case in Select Statement

SQL CASE WHEN IN (SELECT...) THEN

As error indicates, Databricks does not support subqueries using IN or EXISTS in CASE statements. As an alternative, consider outer joining each view to master contract table:

SELECT m.contractid,
       CASE 
          WHEN cncl.contractid IS NOT NULL 
          THEN 'contract canceled'
          WHEN dt.contractid IS NOT NULL 
          THEN 'contract date changed'
       END AS contract_status
       
FROM master_contracts m     
LEFT JOIN (
    SELECT DISTINCT contractid
    FROM contract_ids_canceled
)  cncl 
  ON m.contractid = cncl.contractid
LEFT JOIN (
    SELECT DISTINCT contractid
    FROM contract_ids_changed_date
) dt
  ON m.contractid = dt.contractid

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

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.

Is it possible to put a select statement in a case statement?

I have found the solution to my specific problem. it was just my where clause causing added rows.

But to answer the original question of can you put a subquery select statement in a CASE. YES you can do it.

CASE
   WHEN condition
   THEN (select column1, from tbl join tbl2 on tbl1.column = tbl2.column)

   ELSE 
    (whatever)

END

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).

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)

---

Related Topics