Check condition in WHERE clause

Question

I have below dynamic WHERE condition in XML mapping which is working fine:

WHERE
    IncomingFlightId=#{flightId}
    <if test="screenFunction == 'MAIL'.toString()">
        and ContentCode = 'M'
    </if>
    <if test="screenFunction == 'CARGO'.toString()">
        and ContentCode Not IN('M')
    </if>
    order by ContentCode ASC

I'm trying to run below query in a IDE but unfortunately its not working.

Can anybody please explain what i'm doing wrong?

WHERE
   IncomingFlightId = 2568648 
   AND (IF 'MAIL' = 'MAIL'
   BEGIN
        SELECT 'and ContentCode = "M"'
   END ELSE BEGIN
        SELECT 'and ContentCode Not IN("M")'
   END)
   order by ContentCode ASC

Caius Jard · Accepted Answer · 2018-10-11T12:44:01.380

You can't use IF in straight up SQL statement, use CASE WHEN test THEN returniftrue ELSE valueiffalse END instead (if you have to use conditional logic)

That said, it's probably avoidable if you do something like this:

WHERE
  (somecolumn = 'MAIL' AND ContentCode = 'M') OR
  (somecolumn <> 'MAIL' and ContentCode <> 'M')

Example of conditional logic in a straight SQL:

SELECT * FROM table
WHERE
  CASE WHEN col > 0 THEN 1 ELSE 0 END = 1

Case when runs a test and returns a value. You always have to compare the return value to something else. You can't do something that doesn't return a value.

It's kinda dumb here though, because anything you can express in the truth of a case when, can be more simply and readably expressed in the truth of a where clause directly..

SELECT * FROM table
WHERE
  CASE WHEN type = 'x'
    THEN (SELECT count(*) FROM x) 
    ELSE (SELECT count(*) FROM y) 
  END = 1

Versus

SELECT * FROM table
WHERE
  (type = 'x' AND (SELECT count(*) FROM x) = 1) OR
   type <> 'x' AND (SELECT count(*) FROM y) = 1)

It's useful for things like this though:

SELECT 
  bustourname, 
  SUM(CASE WHEN age > 60 THEN 1 ELSE 0 END) as count_of_old_people
FROM table 
GROUP BY bustourname

If you're looking to write a stored procedure that conditionally builds an SQL, then sure, you can do that...

DECLARE @sql VARCAHR(max) = 'SELECT * FROM TABLE WHERE';
IF blah SET @sql = CONCAT(@sql, 'somecolumn = 1')
IF otherblah SET @sql = CONCAT(@sql, 'othercolumn = 1')
EXEC @sql...

But this is only in a stored procedure or procedure-like sql script where it builds a string that looks like an SQL, and then executes it dynamically. You cannot use IF in a plain SELECT statement

`where IncomingFlightId = 2568648 AND (ISNULL(somecolumn , '') = '' or ((somecolumn = 'MAIL' AND ContentCode = 'C') OR (somecolumn <> 'MAIL' and ContentCode <> 'C')))` — Santosh Jadi, Oct 12 '18 at 04:59
It would probably always pay to use brackets when mixing AND and or. Your where clause can be written as: `WHERE (IncomingFlightId = 2568648 AND (somecolumn IS NULL OR somecolumn = '')) or ((somecolumn = 'MAIL' AND ContentCode = 'C') OR (somecolumn <> 'MAIL' and ContentCode <> 'C')))` - note the ` ... IS NULL` check. It's probably always better to do this than use ISNULL() function to turn a null into a value - few reasons, ISNULL is DB specific whereas `x IS NULL` is standrd SQL. Also, using a function on a column can sometimes mean that an index on that column can no longer be used. — Caius Jard, Oct 12 '18 at 17:52

score 1 · Answer 2 · answered Oct 11 '18 at 15:26

You are running the query which (beside it is syntactically incorrect SQL) has nothing to do with query generated and used by mybatis.

You need to understand how if in mybatis mapper works. if element evaluates before the query is executed at the stage of generation of the SQL query text. If the value of the test is true the content of if element is included into the resulting query. In your case depending on the screenFunction parameter passed to mybatis mapper method one of three conditions are generated.

If value of screenFunction is MAIL then:

WHERE
IncomingFlightId=#{flightId}
    and ContentCode = 'M'
order by ContentCode ASC

If value of screenFunction is CARGO then:

WHERE
IncomingFlightId=#{flightId}
    and ContentCode Not IN('M')
order by ContentCode ASC

Otherwise (if value of screenFunction is not MAIL and is not CARGO):

WHERE
IncomingFlightId=#{flightId}
order by ContentCode ASC

Only after the query text is generated it is executed via JDBC against the database.

So if you want to run the query manually you need to try one of these queries.

One thing that you may want to do to make it easier is to enable logging of SQL queries and parameters passed to them so you can more easily rerun them.

Check condition in WHERE clause

2 Answers2