Re: "In" is not support multi columns query in H2

[Thomas Mueller]

Hi,

Yes, this is not supported. Couldn't you use a join?

Regards,

Thomas

On Mon, Nov 19, 2012 at 10:38 AM, bluerainfj <fanj...@vip.qq.com> wrote:

execute SQL:  SELECT * FROM ZZJG1_3  WHERE  (DXMC,F_OBJNAME)  IN  (SELECT DXMC,F_OBJNAME FROM ZZJG1)


ERROR Message:

Subquery is not a single column query; SQL statement:
SELECT * FROM ZZJG1_3  WHERE  (DXMC,F_OBJNAME)  IN  (SELECT DXMC,F_OBJNAME FROM ZZJG1_3) [90052-168] 90052/90052 (Help)

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/pRstnnRFM4kJ.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Lukas Eder]

The combined use of row value expressions and semi joins was recently discussed here on the user group:

https://groups.google.com/d/topic/h2-database/XlRLHjytKiE/discussion

You could try using this expression:

SELECT * FROM ZZJG1_3  WHERE  (DXMC,F_OBJNAME)  IN  (SELECT (DXMC,F_OBJNAME) FROM ZZJG1) 

What looks like row value expressions in other databases are in fact array expressions in H2

Cheers

Lukas

[bluerainfj]

hi:

  thanks for you reply,

the sql  "SELECT * FROM ZZJG1_3  WHERE  (DXMC,F_OBJNAME)  IN  (SELECT (DXMC,F_OBJNAME) FROM ZZJG1) " can exexute at oracle.

在 2012年11月20日星期二UTC+8下午3时25分42秒，Lukas Eder写道：

[bluerainfj]

hi:

  thanks for you reply,

the sql  "SELECT * FROM ZZJG1_3  WHERE  (DXMC,F_OBJNAME)  IN  (SELECT (DXMC,F_OBJNAME) FROM ZZJG1) " can exexute at oracle.

在 2012年11月20日星期二UTC+8下午3时25分42秒，Lukas Eder写道：

The combined use of row value expressions and semi joins was recently discussed here on the user group: