Error: invalid input syntax for integer: ""

Trisna picture Trisna · May 28, 2014 · Viewed 36.1k times · Source

I have this table tbl_buku:

id_buku  judul_buku   tahun_buku
1          Bioogi          2010
2          Fisika          2010
3          Informatika     2012
4          Kimia           2012

I use query like this, but I am getting an error:

select case when t1.tahun_buku=t2.tahun_buku then ''
            else t1.tahun_buku end tahun_buku,t1.judul_buku
from tbl_buku t1 left join tbl_buku t2
on t1.id_buku-1=t2.id_buku;

I want to show table like this:

tahun_buku     judul_buku
2010             Biologi
                 Fisika
2012             Informatika
                 Kimia

How to achieve this?

Answer

apomene picture apomene · May 28, 2014

I think the problem in your query is that tahun_buku is of datatype int and you are trying to select an empty string ('').

You have to workarounds:

Change tahun_buku to be varchar (2010,2012..will be consider as strings I dont know if it is ok)

Set:

select case when t1.tahun_buku=t2.tahun_buku then null else t1.tahun_buku end tahun_buku,t1.judul_buku
from tbl_buku t1 left join tbl_buku t2
on t1.id_buku-1=t2.id_buku;