New post
Question
· Jan 31, 2017

SQL with 2 columns in where condition

#Caché

I'm having trouble creating a view in cache with query like:

 

SELECT column1, column2, column 3
    FROM  table
    WHERE
        (column1, columnn 2) IN (SELECT c1, c2 FROM table2 GROUP BY c1)
    ORDER BY column1

 

I think the problem is in where clause, because it cannot take two columns .

 

the exact error: [SQLCODE: <-1>:<Invalid SQL statement>] [%msg: < ) expected, , found^ (column1,>]

 

Any idea on how to solve this?

 

Thank you.

Discussion (3)0
Log in or sign up to continue
Alexander Koblov · Jan 31, 2017

First of all, this is bad sample of GROUP BY query, in my opinion:

SELECT c1, c2 FROM table2 GROUP BY c1

You are grouping by c1, but also selecting c2. GROUP BY collects all rows with the same c1 into one row. Each group of row with the same c1 might have different values of c2. What particular value of c2 you want to get?

Generally, if you query contains GROUP BY, SELECT should contain either expressions from GROUP BY, or aggregate functions. For example, following query is OK:

SELECT c1, max(c2) FROM table2 GROUP BY c1

Having said all that, you might try to concatenate column1 and columnn2

SELECT column1, column2, column 3
FROM  table
WHERE
    column1 || '===' || columnn2 IN (SELECT c1 || '===' || c2 FROM table2 GROUP BY c1)
ORDER BY column1

provided that values in these columns do not contain '==='

Another possibility is:

SELECT column1, column2, column 3
FROM  table T1
WHERE EXISTS (SELECT 1 FROM table2 T2
               WHERE T2.c1 = T1.column1
                 AND T2.c2 = T1.column2)
ORDER BY column1

I think GROUP BY is not necessary in second case.

0 0