CODEWITHSUNDEEP
sqlpostgresqlnullstringwindow-functions
Trisna
Trisna

Reputation: 385

Error: invalid input syntax for integer: ""

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?

Upvotes: 9

Views: 58185

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656754

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;

Upvotes: 3

apomene
apomene

Reputation: 14389

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;

Upvotes: 7

Related Questions