Empty Parameter

[Fumi K]

Hi,
I am very new to the product. Just installed 4.18 and wondering if there is a way to skip any empty parameters if user did not input.

For example:
Parameter: order_id, customer_id

select order_id, customer_id from orders
where order_id = ''
and customer_id =''
limit 50;
--> show 50 rows from orders table.

This will be very useful when user does not know order_id# or customer_id#.
Thanks,

[Timothy Anyona]

I would recommend using dynamic sql and use groovy for your sql source. You can check the passed parameters and construct your sql as appropriate e.g.

def value1 = order_id.passedParameterValues
def cond1
if(value1==null || value1[0].equals('')){
    cond1='1=1'
} else {
    cond1='id=#id#'
}

def value2 = customer_id.passedParameterValues
def cond2
if(value2==null || value2[0].equals('')){
    cond2='1=1'
} else {
    cond2='id=#id#'
}

def sql
sql = """select order_id, customer_id from orders 
where $cond1
and $cond2
limit 50"""

[Fumi K]

Thank you!