SQL Filter is NULL

[dfricke10]

I have a list control of products. I'm passing the variables for the filter through the URL with arguments.

Here's my issue.
The List Control takes the variables from the URL and creates arguments. The arguments filter the products based on Category and SubCategory. Here's the twist. Not every product has a SubCategory. How do I, or what should I do, to be able to filter this list if there is no SubCategorySelect variable in the URL?

I've tried a default value of 0, and well, it's looking for the ID of 0, can't find it so the list is empty.

How can I filter if there is no SubCategory variable? Is there an IF/THEN I can put somewhere?

[Pat Bremkamp]

One way might be to use the ifnull() function. If it is null, then use true to show the category or false to not use it. Another way is to add a SubCategory value that is equal to the category value so every category has a least one subcategory.

[dfricke10]

Not sure if I explained correctly. Let's try this.

Every product has a Category and a SubCategory. Sometimes I want to display all the products in a specific Category and ignoring the subcategory (kinda a List All type of thing). Then, some other time I want to display a specific Category AND drilling down to a specific SubCategory, like 'Womens' and 'Jeans'.

I'm doing this with filters after the list is drawn. BUT, it's slower. It draws the list, then it renders the list again with the filter. I want to figure out how to do is with SQL arguments and just have the list control go to the server once. So, I figured, use variables passed in the URL, and create Arguments to filter the list. Therein lies the issue. if any of the filters are blank, the list has no products.

[dfricke10]

I guess the simple way to explain is, If there is no URL variable, ignore the WHERE part in the SQL statement. So, if :SUBCAT argument is NULL, or doesn't exist, remove it from the SQL WHERE. So, I assume there might be a WHERE IF(:SUBCAT > 0, trueresult, makeitgoaway) or something like that.

[madtowng]

How about select coalesce(subcat,'') as `Subcat` ?

[dfricke10]

I can do this...

Inventory_New.web_CategorySelect = ISNULL(:CATNUM, Inventory_New.web_CategorySelect)  AND  Inventory_New.web_SubCategorySelect = ISNULL(:SUBCAT, Inventory_New.web_SubCategorySelect)

but that's looking for NULL fields. I need to have it take out the argument WERE part if the page variable doesn't exist. The arguments are derived from a page URL variable. If that doesn't exist in the URL, I need to remove the WHERE part that has that argument.

[dfricke10]

Here is what worked.

WHERE Inventory_New.web_Active = 1
AND Inventory_New.web_ActiveOnWeb = 1
AND Inventory_New.web_isSold = 0
AND ( Inventory_New.web_CategorySelect = :CATNUM
OR ISNULL(:CATNUM, '') = '' )
AND ( Inventory_New.web_SubCategorySelect = :SUBCAT
OR ISNULL(:SUBCAT, '') = '' )

I swear I tried a million of these ISNULL formats. Oh, well. it works!

And for some reason, Portable SQL says there's a WHERE clause issue, but it still finds the records. I went to native mode and it works fine with no errors.

[madtowng]

I'm sorry....There is another option
you can use Case When in the where section .
This works much like the select/case xbasic function.

select * 
Inventory_new
where CASE
when :SUBCAT is null or :CATNUM is null then
1=1
when Inventory_New.web_CategorySelect = :CATNUM then
<filter options here>
when Inventory_New.web_SubCategorySelect = :SUBCAT then
<filter options here>
end ;