Query with "dynamic" WHERE (1 Viewer)

[tmyers]

Hello again!
I am working on a Python application and have a query interacting with an Access 2016 backend that I can't seem to get right. For reference, here is the function in its entirety:

    def query_order_data(self, pm_id):
        if not self.connection_manager.conn:
            self.connection_manager.connect()

        sql = '''SELECT tblCustomer.CustomerName, tblJob.JobID, tblJob.JobName, tblJob.IsActive, tblJob.ID_PM, tblOrders.OrderNumber, tblOrders.Status, tblOrders.LastDataFetch
                FROM (tblCustomer INNER JOIN tblJob ON tblCustomer.CustomerID = tblJob.ID_Customer) LEFT JOIN tblOrders on tblJob.JobID = tblOrders.ID_Job
                WHERE tblJob.IsActive = True
                AND (tblJob.ID_PM = ? or ? IS NULL)
        '''

        try:
            cursor = self.connection_manager.conn.cursor()
            cursor.execute(sql, (pm_id, pm_id))

            returned_orders = cursor.fetchall()

            order_list = [Orders(*order) for order in returned_orders] # Convert tuple to list of objects

            return order_list
       
        except Exception as e:
            print(e)

The query portion:

        sql = '''SELECT tblCustomer.CustomerName, tblJob.JobID, tblJob.JobName, tblJob.IsActive, tblJob.ID_PM, tblOrders.OrderNumber, tblOrders.Status, tblOrders.LastDataFetch
                FROM (tblCustomer INNER JOIN tblJob ON tblCustomer.CustomerID = tblJob.ID_Customer) LEFT JOIN tblOrders on tblJob.JobID = tblOrders.ID_Job
                WHERE tblJob.IsActive = True
                AND (tblJob.ID_PM = ? or ? IS NULL)
        '''

What I am trying to accomplish is if pm_id is None/Null, return ALL records. However if pm_id has a value, return records where ID_PM match pm_id

Any chance one of you experts knows what I am doing wrong? I have never done a "dynamic" query like this before.

Edit:
I can seem to only ever get the query to return all records, filtered records or no records but not a combination of them.

 

[The_Doc_Man]

You are building a query dynamically. If that is for Access SQL then before you finish that statement, you would need to substitute for the question marks. I believe that it would be perfectly OK for you to have something valid that is not a constant in place of them. BUT your variable is a passed parameter that will be a constant. It is my thought that you have overstepped in this one by building something too early and "painting yourself into a corner."

You can do an IF statement to make the whole sub-clause AND (tblJob.ID_PM = ... as the last thing you add to that string. The WHERE cannot be built to be that dynamic, but the sub-clause can be.

If NOT( pm_id IS NULL ) Then
    SQL = SQL & " AND (tblJob.ID_PM = '" & pm_id & "')"
End If

The whole thing to understand about WHERE clauses is that you must supply something that will evaluate to TRUE or FALSE. But by default, if you put nothing at all in that place, it does not evaluate as false. I.e. an implied TRUE. So... if your input parameter is NULL, you don't even need the AND sub-clause. Do nothing. SQL doesn't have to evaluate the state of pm_id since it IS a parameter.

Stated another way, why make SQL evaluate something that you can evaluate before SQL ever has to?

 

[tmyers]

I never thought to do it that way. That actually makes it so much simpler to work with.