mercredi 25 octobre 2017

Use IF ELSE statement in Common Table Expression in T-Sql

I have a CTE created and I am trying to delete tables using the following. Note that CTE expressions work fine but IF ELSE statements does not.

IF 
with FinalSales (time, terminal_id, count) as
(
    select time, terminal_id, count(*)
    from Final_Sales
    group by time, terminal_id
    having count(*) = 1             -- condition here
)

delete Sales
from FinalSales
inner join Sales
    on Sales.[time] = FinalSales.[time] 
    and Sales.terminal_id = FinalSales.terminal_id

ELSE IF 

  with FinalSales (time, terminal_id, count) as
  (
    select time, terminal_id, count(*)
    from Final_Sales
    group by time, terminal_id
    having count(*) > 1              -- Condition here
  )
  delete Sales
  from FinalSales
  inner join Sales
      on Sales.[time] = FinalSales.[time] 
      and Sales.terminal_id = FinalSales.terminal_id

   delete #temp1
   from FinalSales
   inner join #temp1
        on #temp1.[time] = FinalSales.[time] 
        and #temp1.terminal_id = FinalSales.terminal_id

In the first IF statement, when count(*) = 1 then I delete rows in one table, and then in ELSEIF statement when count > 1 I want to delete rows in two tables. However, my approach does not work.

How can I wrap it into a IF ELSE statement? OR is there any other ways to do this?

Whole query in simple structure,

if count(*) == 1:
   delete Sales
else if count(*) > 1:
   delete Sales, #temp1

something like this. How can I do it in t-sql?

Any ideas would be appreciated.

Aucun commentaire:

Enregistrer un commentaire