CASE Statement is not working Derived table

[Former Member]

Hi All,

in the bello SQL Statement case statement is not working in derived table. I am new to creation of derived table if any body knows plz kinldy help me out on this.

SELECT x.market, x.droprate as med1

FROM

(select upper(market_name) as market, fulldate as date_value,

(sum([Dy_LOT_DROPS_N][Dy_OB_HO_DROPS][Dy_NonRF_Drop]))/

nullif(sum(CASE WHEN (month(BBHDLY.FullDate)}>= 6 and { year(BBHDLY.FullDate)} = 2011) or {fn year(IDENSLABBHDLY.FullDate)} > 2011

THEN BBHDLY.Dy_Calls - BBHDLY.Dy_HO_CHAN_ALLOC ELSE BBHDLY.Dy_Calls END),0)*100 as droprate

from BBHDLY sla

inner join Dim mkt

on sla.bts_name = mkt.bts_name and sla.SectorID = mkt.Sector_Id

where fulldate >= GETDATE()-46

group by market_name, fulldate) x,

(select market_name as market, fulldate as date_value,

(sum([Dy_LOT_DROPS_N][Dy_OB_HO_DROPS][Dy_NonRF_Drop]))/

nullif(sum(CASE WHEN ({fn month(BBHDLY.FullDate)}>= 6 and {fn year(BBHDLY.FullDate)} = 2011) or {fn year(BBHDLY.FullDate)} >

2011 THEN BBHDLY.Dy_Calls - BBHDLY.Dy_HO_CHAN_ALLOC ELSE BBHDLY.Dy_Calls END),0)*100 as droprate

from BBHDLY sla

inner join Dim mkt

on sla.bts_name = mkt.bts_name and sla.SectorID = mkt.Sector_Id

where fulldate >=GETDATE()-46

group by market_name, fulldate) y

where x.market = y.market

GROUP BY x.droprate, x.market

HAVING

SUM(CASE WHEN y.droprate <= x.droprate

THEN 1 ELSE 0 END)>=(COUNT(*)+1)/2 AND

SUM(CASE WHEN y.droprate >= x.droprate

THEN 1 ELSE 0 END)>=(COUNT(*)/2)+1

Thanks

[Former Member · Accepted solution]

It looks like SQL Server or Sybase given that you're using getdate().

As such, Vinesh's comment to use decode is wrong - decode is Oracle syntax.

Looking at your statement again, I've noticed the following:

you have no { to match the first } - not sure why you're using them anyway.

you haven't given x.market a name - use x.market as market instead

use coalesce instead of nullif if you're on SQL Server.

[Former Member]

use DECODE instead of CASE.

Regards,

Vinesh

[Former Member]

Does the SQL work in a native SQL client?

What RDBMS are you using?