Error: Invalid input syntax for integer: ""

Question

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 this query, but I get an error message:

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 the table as follows:

tahun_buku     judul_buku
2010             Biologi
                 Fisika
2012             Informatika
                 Kimia

How to do it?

Answer 1

I think the problem in your request is what it tahun_bukuhas datatype int, and you are trying to select empty string( '').

You have workarounds:

Change tahun_bukuto varchar(2010,2012 .. will be considered as strings that I don’t know if this is normal)

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;

Answer 2

• lag(), .

• NULL "" - @apomene .
  '' , text varchar.

• NULLIF() , NULL.

SELECT NULLIF(lag(tahun_buku) OVER (ORDER BY tahun_buku, judul_buku)
            , tahun_buku) AS tahun_buku
     , judul_buku
FROM   tbl_buku
ORDER  BY tahun_buku, judul_buku;