http://coldfusion.com logo
#cfml-general
Title
# cfml-general
m

mauzer

03/23/2022, 11:10 AM
What is the right <cfqueryparam> syntax when your evaluating a null ? SO for example. I have this query below and need to wrap a <cfqueryparam> statement around it: <CFQUERY NAME="application.argocond" DATASOURCE="newservinform"> Select ID_argo_cond, chiavi from tbl_argo_cond where chiavi <> NULL or chiavi <> '' order by chiavi </CFQUERY>
t

thisOldDave

03/23/2022, 11:58 AM
you cant compare to null in sql, null means
I don't exist
you have to use
IS
or
IS NOT
or the equivalent function
Copy code
<CFQUERY NAME="application.argocond" DATASOURCE="newservinform">
	Select ID_argo_cond, chiavi from tbl_argo_cond
	where chiavi IS NOT NULL AND chiavi != ''
	order by chiavi
</CFQUERY>
a

Adam Cameron

03/23/2022, 12:56 PM
If it's a literal
NULL
in your SQL statement, you don't need to parameterise it. Params are for when you have data to pass to the DB that need to be applied to the statement. In this case the statement seems to just be a literal string (ie: it is literally
where chiavi <> NULL or chiavi <> ''
), so there aren't any params.
w

websolete

03/23/2022, 1:15 PM
i also think you don't actually want an OR clause here if i'm understanding the goal. you'd want a
WHERE chiavi IS NOT NULL AND chiavi <> ''
but it could be improved imo by using
WHERE LEN( IFNULL(chiavi,'') ) > 0
(replace IFNULL with ISNULL depending on your db flavor)
⬆️ 2
having said that, if there is an index on chiavi i'd probably stick with
WHERE chiavi IS NOT NULL AND chiavi <> ''
depending on the number of rows in the table
d

David Buck

03/23/2022, 9:16 PM
If websolete is right that your goal is to exclude both NULLs and empty strings, then you don't really need an explicit NULL filter in your query. The expression
chiavi != ''
requires
chiavi
to have a value (to compare to empty string), so it implicitly filters out rows where
chiavi
has no value (i.e. is NULL). So the following should suffice:
Copy code
SELECT ID_argo_cond, chiavi
FROM tbl_argo_cond
WHERE chiavi != ''
ORDER BY chiavi
That said, an argument could be made for including the NULL filter anyway for the sake of clarity.
3 Views