Error: invalid input syntax for integer: ""
48,715
Solution 1
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;
Solution 2
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 liketext
orvarchar
.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;
Comments
-
Trisna almost 2 years
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?