Queries with NULL values 1

[iao]

I am trying to run a query to cross-check the values a user entered against the database. I want to do this so that before the user adds a record, the record must not already exist. This is for a list of street names.

There are four items the user might select (3 are required). These are PREFIX, NAME, TYPE, and SUFFIX. For example, for the street named "Park Ave NE", "Park" is the NAME, "Ave" is the TYPE, and "NE" is the SUFFIX (there is no PREFIX entered in this example).

If I run the following query:

<CFQUERY NAME=&quot;qCheckSt&quot;>
SELECT NAME, PREFIX, SUFFIX, TYPE
FROM NAMES
WHERE NAME = '#Form.Name#'
AND PREFIX = '#Form.Prefix#'
AND TYPE = '#Form.Type#'
AND SUFFIX = '#Form.Suffix#'
</CFQUERY>

I will get a &quot;returned 0 records&quot; even if this exists. The problem is with the PREFIX. If I run the above query but take out the statement &quot;AND PREFIX = '#Form.Prefix#'&quot;, I get a &quot;returned 1 record&quot;.

It's when a value is null that I am having the problem. I tried doing it both ways. Meaning, that if the user doesn't select a PREFIX, the value NULL will be checked. This didn't work, so I took out the value NULL and it still didn't work.

Is there something simple that I am not doing correctly?

 

[tleish]

I think when checking for a null value, you need to use &quot;IS NULL&quot;:

So if &quot;Park&quot; is the NAME, &quot;Ave&quot; is the TYPE, and &quot;NE&quot; is the SUFFIX (there is no PREFIX entered in this example)
the query should resolve to this.

SELECT NAME, PREFIX, SUFFIX, TYPE
FROM NAMES
WHERE NAME = 'Park'
AND PREFIX IS NULL
AND TYPE = 'Ave'
AND SUFFIX = 'NE'

You'd need to use an ,CFIF/ELSE to figure out the logic. How the logic works depends on what form elements you are using.

Another note, if you want to jsut check for the values entered, and not check for a null value, then the above query would look like this:

SELECT NAME, PREFIX, SUFFIX, TYPE
FROM NAMES
WHERE NAME = 'Park'
AND PREFIX IS NULL
AND TYPE = 'Ave'
AND SUFFIX = 'NE'

Again, you'd need to use an ,CFIF/ELSE to figure out when to inlclude the individual values to check against. - tleish

 

[iao]

Ok. That did indeed work. Even though I will now have to write some logic to determine what field was null, you answered my question.

Thanks!