CODEWITHSUNDEEP
databasesqlite
Eli Tamondong
Eli Tamondong

Reputation: 51

SQLite condition

I just want to convert this function query from navicat to sqlite query,

 Select * from tbl_sample where ID = 1 And IF (
        RECEIPT_MODE = 'MANUAL',
        DATE(a.MANUAL_COLLECTION_DATE) = DATE('2016-08-10 04:18:00'),
        DATE(TPAY_ISSUED_DATE) = DATE('2016-08-10 04:18:00')
    )

Upvotes: 1

Views: 35

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

Try this query:

SELECT *
FROM tbl_sample
WHERE ID = 1 AND
(
    (RECEIPT_MODE = 'MANUAL' AND
     DATE(a.MANUAL_COLLECTION_DATE) = DATE('2016-08-10 04:18:00')) OR
    (RECEIPT_MODE <> 'MANUAL' AND
     DATE(TPAY_ISSUED_DATE) = DATE('2016-08-10 04:18:00'))
)

Use the DNF to CNF rewrite rule to reduce the level of parens/nesting:

SELECT *
FROM tbl_sample
WHERE ID = 1 
AND ( RECEIPT_MODE <> 'MANUAL' OR
      DATE(a.MANUAL_COLLECTION_DATE) = DATE('2016-08-10 04:18:00') )
AND ( RECEIPT_MODE = 'MANUAL' OR 
      DATE(TPAY_ISSUED_DATE) = DATE('2016-08-10 04:18:00') )

Upvotes: 1

CL.
CL.

Reputation: 180060

The standard SQL mechanism for that IF would be a CASE expression:

Select * from tbl_sample where ID = 1 And CASE
       WHEN RECEIPT_MODE = 'MANUAL'
       THEN DATE(a.MANUAL_COLLECTION_DATE) = DATE('2016-08-10 04:18:00')
       ELSE DATE(TPAY_ISSUED_DATE) = DATE('2016-08-10 04:18:00')
   END

Upvotes: 0

Related Questions