SQL Syntax error when using cases inside a SELECT

Question

I am trying to have an if else condition in this select statement. I am either passing a customerID or a accountID into this mapper and thus I need to check which one is being passed. I had the first case where there was no customerId (thus accountId is passed), and so the inner join is performed. The second one is when there is no accountID (thus customerId was passed). Then at the end of the cases, I want to order it by descending dates.

    SELECT i.invoice_id, i.account_id, total_amount_due, due_date, bp.eff_date, bp.end_date, total_amount_due
        (
        CASE
            WHEN customerId = null
                THEN INNER JOIN server.bill_periods bp ON bp.invoice_id = i.invoice_id
                     WHERE account_id=#{accountId}

            ELSE INNER JOIN server.bill_periods bp ON bp.invoice_id = i.invoice_id
                 INNER JOIN server.cust_acct_rel car ON car.account_id = i.account_id
                    WHERE car.customer_id=#{customerId}
        END)         
    ORDER BY end_date DESC

However, when I run this, I get this error.

org.apache.ibatis.exceptions.PersistenceException: 
Error querying database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN server.bill_periods bp ON bp.invoice_id = i.invoice_id WHERE ' at line 5

Does anyone know what I am doing wrong here?

Your code makes no sense. For instance there is no `FROM` clause. — Gordon Linoff, May 29 '17 at 21:03
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, May 29 '17 at 21:17
probably a new construct of joining tables over case statements — RoMEoMusTDiE, May 29 '17 at 22:11

score 0 · Answer 1 · answered May 29 '17 at 22:16

0

use IFNULL

select ...,..., IFNULL(customerID,accountID) newid
from 
    table1 
     left outer join table2
order by date desc

answered May 29 '17 at 22:16

RoMEoMusTDiE

4,739
1
17
26

SQL Syntax error when using cases inside a SELECT

1 Answers1