SELECT DISTINCT CASE

[bedzinsa]

When running this query: SELECT DISTINCT (CASE WHEN senderId = 3851 THEN recipientId ELSE senderId END) , * FROMmessage WHERE (senderId= 3851 ORrecipientId= 3851 ) GROUP BY CASE WHEN senderId = 3851 THEN recipientId ELSE senderId END ORDER BY CASE WHEN senderId = 3851 THEN recipientId ELSE senderId END, datetime(dateTime) DESC

Error occurs: java.lang.IllegalArgumentException: Unknown column name '(CASE WHEN senderId = 3851 THEN recipientId ELSE senderId END)' in table message

As far as I am aware query is fine and sqlite can manage distinction and cases. _The code i am running: _

`String orderCase = "CASE WHEN " + Message.SENDER_ID + " = " + userId +
                        " THEN " + Message.RECIPIENT_ID + " ELSE " + Message.SENDER_ID + " END";
                Dao<Message, Long> dao = getDao(Message.class);
                QueryBuilder<Message, Long> queryBuilder = dao.queryBuilder();
                queryBuilder.distinct().selectRaw(orderCase + ", *");
                Where<Message, Long> where = queryBuilder.where();
                where.or(
                        where.eq(Message.SENDER_ID, userId),
                        where.eq(Message.RECIPIENT_ID, userId));
                queryBuilder.setWhere(where);
                queryBuilder
                        .groupByRaw(orderCase)
                        .orderByRaw(orderCase + ", datetime(" + Message.DATE_TIME + ") DESC");

                Log.e(TAG, "Query: " + queryBuilder.prepareStatementString());
                GenericRawResults<Message> rawResults = dao.queryRaw(
                        queryBuilder.prepareStatementString(), dao.getRawRowMapper());
                try {
                    return rawResults.getResults();
                    //return queryBuilder.query();
                } finally {
                    rawResults.close();
                }`

Is this a bug?

[j256]

Can you post the full exception? Can you also figure out what query ORMLite is generating?

[kpgalligan]

I feel like this is in the sqlite layer, not ormlite, but would need more context.