Fizz buzz SQL logic error

Question

-1 to 100 in SQL and display 'Fizz' if the number is divisible by 3 'Buzz' if divisible by 5 and 'FizzBuzz' if divisible by both. below is my code

select 
Case when   Remainder (rownum,3)=0 then 'Fizz'
     when Remainder (rownum,5)=0 then 'Buzz'
     when (remainder (rownum,3)=0 and remainder(ROWNUM,5) = 0) then 'FizzBuzz'
     else rownum end 
from DUAL
Connect by level  <=100;

It gives me error - ORA-00932: inconsistent datatypes: expected CHAR got NUMBER 00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause:
*Action: Error at Line: 5 Column: 18

Answer 1

It's in your else statement. Rownum is a NUMBER whereas Fizz, Buzz, and FizzBuzz are CHAR.

Answer 2

You need to convert ROWNUM to a string in your ELSE case by either:

• CAST( ROWNUM AS VARCHAR2(3) )
• ''||ROWNUM
• TO_CHAR( ROWNUM )

You also need the case when it is divisible by both 3 and 5 to be at the top of the list (otherwise the preceding cases will take precedence). Like this:

SELECT CASE 
       WHEN remainder (rownum,15)=0 THEN 'FizzBuzz'
       WHEN Remainder (rownum,3)=0  THEN 'Fizz'
       WHEN Remainder (rownum,5)=0  THEN 'Buzz'
       ELSE TO_CHAR(rownum)
       END 
FROM   DUAL
CONNECT BY LEVEL <= 100;

However, you can shorten the code a lot (taken from an answer I posted for Code Golf):

SELECT NVL(
         DECODE(MOD(LEVEL,3),0,'Fizz')||DECODE(MOD(LEVEL,5),0,'Buzz'),
         ''||LEVEL
       )
FROM DUAL
CONNECT BY LEVEL<101

Answer 3

select 
Case when   Remainder (rownum,3)=0 then 'Fizz'
     when Remainder (rownum,5)=0 then 'Buzz'
     when (remainder (rownum,3)=0 and remainder(ROWNUM,5) = 0) then 'FizzBuzz'
     else ''||rownum end 
from DUAL
Connect by level  <=100;

or

select 
Case when   Remainder (rownum,3)=0 then 'Fizz'
     when Remainder (rownum,5)=0 then 'Buzz'
     when (remainder (rownum,3)=0 and remainder(ROWNUM,5) = 0) then 'FizzBuzz'
     else to_char(rownum) end 
from DUAL
Connect by level  <=100;