Hi All, how can we add the condition based where conditions in SQL? I tested with below sample query but It was throwing an error. Any Ideas? Thanks!

`SELECT
    Transaction.ID,
    Transaction.trandate as InvoiceDate,
    Transaction.tranid,
    Transaction.foreigntotal as total,
    BUILTIN.DF(Transaction.entity) as Customer
    FROM
    Transaction
    WHERE
    ( (Transaction.Type = 'CustInvc' ) OR  (Transaction.Type = 'CashSale') )
    CASE WHEN  2 > 1 THEN 
    Transaction.ID = 93732
    END;
    AND
    Transaction.approvalstatus = 1
    ORDER BY
    Transaction.Trandate ASC,
    Transaction.ID ASC`;

@Charan what's the situation where you're wanting a case statement in the where clause? It's possible to do, but I've seen it more common to do

Transaction.ID = CASE...END

and the case statement returns an ID based on conditions. Also, there are two semicolons in that example, don't think you want that. I've never built a case statement that conditionally appends part of the where clause, not sure that's possible -- I have done that in a scripting language to dynamically build the query string, however. But, more context on what type of condition you're wanting to use. It's also nice to get to a point of success -- so if you removed from the start of the case statement through the end, the new query should work, then add one condition at a time to ensure you still have a valid query.

Thanks @Taylor for the explanation. Basically, I want to add WHERE conditions based on the javascript values which means, If the parameter value is not empty then I need to add the condition. Something I want to achieve the below line in SQL

So are you constructing the query string in javascript? Node perhaps?

Yes that's correct. I'm constructing It in JS.

So you could use js conditions to append any relevant parts of the where clause

Can we do that using SQL Case statements? Is that possible?

I think there would be a way to use and/or conditions within the query to make this happen too. but I don't think the case statement can return an entire expression like

Transaction.ID = 1234

I think it has to return a value, and that value can be used in an expression.

Thanks Taylor!

Happy to help, don't have the time to fiddle with the exact sql that may work for you, but it'd be a fun one to figure out -- I bet you can get it sorted, and if not, just have that last ID criteria appended as a string in your js - that's easy enough to do --

var query = select ...;

then in the js

if(value) { query += ' and Transaction.ID = ' + value; }

- something along those lines.