0

I'm using MySQL and H2 Database on my project. I've got a H2 database with URL jdbc:h2:mem:test. This is query what I try to run.

SELECT u.id, u.username, u.profileImageUrl, 
if ((SELECT 1 FROM subscribe WHERE fromUserId = 1 AND toUserId = u.id), 1, 0) subscribeState,
if ((1=u.id), 1, 0) equalUserState 
FROM walking_around_user u INNER JOIN subscribe s 
ON u.id = s.toUserId 
WHERE s.fromUserId = 2;

It's OK using MySQL, But when I run my project with H2 I got the following error:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "SELECT U.ID, U.USERNAME, U.PROFILEIMAGEURL, IF[*] ((SELECT 1 FROM SUBSCRIBE WHERE FROMUSERID = ? AND TOUSERID = U.ID), 1, 0) SUBSCRIBESTATE, IF ((? = U.ID), 1, 0) EQUALUSERSTATE FROM WALKING_AROUND_USER U INNER JOIN SUBSCRIBE S ON U.ID = S.TOUSERID WHERE S.FROMUSERID = ?"; expected "*, NOT, EXISTS, INTERSECTS, UNIQUE"; SQL statement:
SELECT u.id, u.username, u.profileImageUrl, if ((SELECT 1 FROM subscribe WHERE fromUserId = ? AND toUserId = u.id), 1, 0) subscribeState, if ((? = u.id), 1, 0) equalUserState FROM walking_around_user u INNER JOIN subscribe s ON u.id = s.toUserId WHERE s.fromUserId = ? [42001-200]

I wonder how to use "if" grammar with h2 database.

seominah
  • 1
  • 1
  • `if` is not part of the SQL standard, so you must set the mode to MySQL: `jdbc:h2:mem:testdb;mode=mysql`, but you could just use the completely portable `case` instead. – Bohemian Apr 15 '22 at 06:14
  • Thank you so much. I have a question. how can I use the completely portabel case? – seominah Apr 15 '22 at 06:48
  • The first one doesn't need if or case: `(SELECT count(*) FROM subscribe WHERE fromUserId = 1 AND toUserId = u.id) subscribeState`. The second one: `case when 1 = u.id then 1 else 0 end equalUserState`, but in MySQL you can just do: `(1 = u.id) equalUserState` because in MySQL true is 1 and false is 0. – Bohemian Apr 15 '22 at 08:32
  • Wow. That is more simple and readability. Thanks for your help!! – seominah Apr 16 '22 at 13:01

0 Answers0