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 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?

You may want to use lag() window function. Details here: postgresql.org/docs/current/static/functions-window.html — Ihor Romanchenko
– Ihor Romanchenko, Commented May 28, 2014 at 12:46
SQL is the wrong tool for suppressing repeated values in a column. That's what report writers are for. (You have a presentation-level problem, not a database problem.) — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented May 28, 2014 at 12:54

apomene · Accepted Answer · 2014-05-28 12:56:44Z

7

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;

answered May 28, 2014 at 12:56

apomene

14.4k10 gold badges51 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:17:42Z

3

Use the window function lag() to get values from the previous row.
Use NULL for "empty" values in a numeric column - like @apomene already suggested.
The empty string '' can only be used in character types like text or varchar.
Use NULLIF() in this particular case to substitute the NULL value.

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;

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered May 28, 2014 at 17:29

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Error: invalid input syntax for integer: ""

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related