Case expressions may only be nested to level 10 Iif-CodePudding

I'm facing an issue where i'm trying to create column using name but I'm having more thant 20 flow but I can case only 10 here my request:

    select Missions, Sum(Morning) Morning, Sum(PM) PM, Sum(Night) Night, count(*) Total
from [dbo].[VIEW_JOBS_FINISHED_ALL]
cross apply (values (Iif(QUELLE in ('Réception_14','Réception_21'),'M1',
                        Iif(QUELLE in ('Réception_17','Réception_16'),'M2',
                            Iif(QUELLE in ('Réception_13','Réception_19'),'M3',
                                Iif(QUELLE in ('Réception_15','Réception_25'),'M4',
                                    Iif(QUELLE in ('Réception_15','Réception_25'),'M5',
                                        Iif(QUELLE in ('Réception_15','Réception_25'),'M6',
                                            Iif(QUELLE in ('Réception_15','Réception_25'),'M7',
                                                Iif(QUELLE in ('Réception_15','Réception_25'),'M8',
                                                    Iif(QUELLE in ('Réception_15','Réception_25'),'M9',
                                                        Iif(QUELLE in ('Réception_15','Réception_25'),'M10',
                                                            Iif(QUELLE in ('Réception_15','Réception_25'),'M11','M28')))))))))))))f(Missions)
cross apply (values ( [START_DATE] ))v(T)
cross apply
(   
    values (convert(datetime, convert(date, getdate())),
            convert(datetime, convert(date, getdate() - 1)))
) dates (today, yesterday)
cross apply
(
    values (dateadd(hour, 6, yesterday), 
            dateadd(hour, 14, yesterday), 
            dateadd(hour, 21, yesterday), 
            dateadd(hour, 6, today))
) dt (y6, y11, y22, t6)
cross apply (
    select
        case when T >= y6 and T < y11 then 1 else 0 end Morning,
        case when T >=y11 and T < y22 then 1 else 0 end PM,
        case when T >=y22 and T < t6 then 1 else 0 end Night
)c
group by Missions

CodePudding user response：

Sounds like you really need a join. You can use a real table, or you can use a virtual VALUES table.

LEFT JOIN (VALUES
  ('Réception_14','M1'),
  ('Réception_21','M1'),
  ('Réception_17','M2'),
  ('Réception_16','M2'),
  ('Réception_13','M3'),
  ('Réception_19','M3'),
  ('Réception_15','M4'),
  ('Réception_25','M4')
) f(Quelle, Missions) ON VIEW_JOBS_FINISHED_ALL.QUELLE = f.Quelle

I suggest you always use short meaningful table aliases

CodePudding user response：

That cross apply can be changed to an APPLY with a CASE WHEN

outer apply (
  select 
  case
  when QUELLE IN ('Réception_13', 'Réception_19') then 'M3'
  when QUELLE IN ('Réception_14', 'Réception_21') then 'M1'
  when QUELLE IN ('Réception_15', 'Réception_25') then 'M4'
  when QUELLE IN ('Réception_16', 'Réception_17') then 'M2'
  else 'M8'
  end
) f(Missions)

Or an APPLY with a select from values.

outer apply (
  select top 1 Missions
  from (values
  ('Réception_13','M3'),
  ('Réception_14','M1'), 
  ('Réception_15','M4'),
  ('Réception_16','M2'),
  ('Réception_17','M2'),
  ('Réception_19','M3'),
  ('Réception_21','M1'),
  ('Réception_25','M4'), 
  (null, 'M8')
  ) v(Réception, Missions)
  where (Réception = QUELLE or Réception is null) 
  order by Réception desc
) f(Missions)