Create SAP B1 Query with Optional User Input Fields

[Former Member]

Hi All... any help is greatly appreciated, I am new to this forum and hope to contribute in the near future once I become more of an expert with B1.

I have a query I need to build that will search about 10 UDF's. The problem is, I don't know how to make the user input fields optional within the query. Currently, I have the following queries as testing searching 2 UDF"s:

SELECT T0.[ItemCode], T0.[ItemName], T0.[OnHand] FROM OITM T0 WHERE T0.[U_Quality] = [%0] OR T0.[U_LengthFT] =[%1]

This query works when leaving one of the user input fields blank. However, the values it provides are wrong because it is an OR statement which will show item codes with a certain quality OR a certain size.

The following query is the same as above but switched the OR to AND:

SELECT T0.[ItemCode], T0.[ItemName], T0.[OnHand] FROM OITM T0 WHERE T0.[U_Quality] = [%0] AND T0.[U_LengthFT] =[%1]

This query provides me with the correct values but will not work if one of the user defined fields is not filled out.

How do I go about getting the results I want and not having the user fill out all the UDF's for the query to execute properly?

[Former Member · Accepted solution]

Hi,

Welcome you post on the forum.

Try this:

SELECT T0.ItemCode, T0.ItemName, T0.OnHand 
FROM OITM T0 
WHERE (T0.U_Quality = [%0] OR T0.U_Quality = '') AND (T0.U_LengthFT =[%1] OR T0.U_LengthFT = '')

Thanks,

Gordon

[Former Member · Accepted solution]

This makes sense on paper. When i try it in SAP B1 and fill only one of the user input fields, i still receive a "No data was found as a result of the search criteria."

Anything else I could try?

[Former Member · Accepted solution]

Try:

SELECT T0.ItemCode, T0.ItemName, T0.OnHand 
FROM OITM T0 
WHERE (T0.U_Quality = [%0] OR T0.U_Quality is NULL) AND (T0.U_LengthFT =[%1] OR T0.U_LengthFT is NULL)

[Former Member · Accepted solution]

Thanks again for your help Gordon:

I found the "is NULL" option online as well just before you mentioned it and it did not work also!

There must be a way?

[Former Member · Accepted solution]

Try again:

SELECT T0.ItemCode, T0.ItemName, T0.OnHand 
FROM OITM T0 
WHERE (T0.U_Quality = [%0] OR isNULL(T0.U_Quality,'') ='') AND (T0.U_LengthFT =[%1] OR isNULL(T0.U_LengthFT,'')='')

[Former Member · Accepted solution]

It is still not working...

"No data was found as a result of the selection criteria" message comes up.

It seems that if you put "AND" anywhere in the SQL statement, you must specify a value for the user inputted field. Although we are stating in the query that the fields have the option of being inputted OR empty, it is still expecting some type of value.

If there is anything else I can try please let me know.

[Former Member · Accepted solution]

The null is always problematic because you can not compare it with anything. It is depending on your SQL server too. What is your server version?

[Former Member · Accepted solution]

My SQL version is 2008 small business... any additional information is appreciated.

[Former Member · Accepted solution]

I think I figured it out

SELECT T0.ItemCode, T0.ItemName, T0.OnHand

FROM OITM T0

WHERE (T0.U_Quality = '[%0]' OR '[$0]' = '0') AND (T0.U_LengthFT ='[%1]' OR '[%1]' = '0')

SAP must be defaulting empty user input field values to "0"

I tried the above query and it worked!!!

Is there anything you can add if I am missing something?

Thanks, Alec.

[Former Member · Accepted solution]

This applies to your SQL version. I don't have the same environment to test.

Please close your thread.