Select Inside Case Then

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;

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 inside Case When logic

This is what we thought of and could test successfully, although would have prefered not to use...

create table EDI.TEST (
    "SHIP_ID" VARCHAR(30) NOT NULL,
    "SHIP_EVNT_CD" VARCHAR(2),
    "AF_FLG" VARCHAR(1) DEFAULT 0
);

And then the trigger

CREATE TRIGGER Flags
after INSERT ON EDI.TEST
referencing new as n
FOR EACH ROW
WHEN (n.ship_id in (select ship_id from EDI.TEST where ship_evnt_cd = 'AF'))
UPDATE EDI.TEST SET af_flg = 1

Now I need to do this for other 3 columns, so I wonder if I can use the same trigger for multiple column updates wtih different WHEN statement (like a huge CASE WHEN) but well, offtopic...

How to write a select inside case statement

You can do this with a case. I think the following is the logic you want:

(case when Invoice_DeliveryType <> 'USPS' then ''
      when exists (Select 1
                   from dbo.Client c
                   Where c.Client_ID = SUBSTRING(i.Invoice_ID, 1, 6) and
                         c.emailaddr is not null
                  )
      then 'Y'
      else 'N'
 end)

select inside a case statement not works

You can't use scalar subqueries directly in PL/SQL code, like you have shown. (Of course, you knew that already.) You must select the value INTO a variable, and then use it.

ALSO: You have no case statements in your code. You have a case expression. It just won't work quite the way you wrote it.

One alternative is to use a case expression within the SQL SELECT ... INTO statement, as David Goldman has shown in his Answer.

However, if the whole point of your exercise was to practice case expressions as used in PL/SQL, not inside a SQL statement, you would need to SELECT ... INTO a variable you declare in your code, and then use that variable in the case expression. Something like this:

DECLARE
  LV_VAR VARCHAR2(4000);
BEGIN
  SELECT 1 INTO LV_VAR FROM DUAL;
  LV_VAR:= CASE 
                WHEN 1=1 THEN 
                  LV_VAR
                ELSE
                  0
           END;
  DBMS_OUTPUT.PUT_LINE(LV_VAR);

END;

As you can see, I did something that is done frequently in procedural language code: Instead of declaring and using TWO variables, I only declared one. I populated it with the result of the SELECT ... INTO query. Then I assigned to it again in the case expression: in one case I assign it to itself and in the other I assign to it the value 0.

How to write select queries inside case condition?

Use regular AND/OR instead:

select *
from table
where (to_number(to_char(trunc(sysdate, 'hh'), 'h24')) not in (00, 01, 02)
       and datetime > sysdate - 2)
   or datetime > sysdate - 1       -- i.e. "in (00, 01, 02)"

(Most products find AND/OR easier to optimize than case expressions.)

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

---

Related Topics