HELP WITH DW MX 2004 QUERY (REC0RDSET)...

Have tried several ways of including IS NOT NULL and IS NULL into this syntax, but all has failed.

this should be not depending to the tool you're using to edit the codes.

what server side programming are you using here?

try check your data source and make sure there is Address which is null or not null?

I have now incorporated:

IS NOT NULL in the WHERE clause but am getting this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE ADDRESS IS NOT NULL AND LASTNAME LIKE '%arupoadkyrta@nikorw.fi%' OR FIRST' at line 1

This error occurs both when using "()" and and when not using them.

Without the WHERE ADDRESS IS NOT NULL clause in the query, the query outputs fine -- just that the records without an ADDRESS are included in the output.  This is undesirable.

it should be easy... use brackets for the conditions? what if you're trying these?

SELECT LASTNAME, FIRSTNAME, MAIDENAME, ADDRESS, CITY, ZIP, EMAIL, YEAR, DECEASED
FROM xx###_180101
WHERE ADDRESS IS NOT NULL AND ( LASTNAME LIKE '%varLname%' OR  FIRSTNAME LIKE '%varFname%' OR MAIDENAME LIKE '%varMname%' OR CITY LIKE '%varCity%' OR ZIP LIKE '%varZip%' OR EMAIL LIKE '%varEmail%' OR YEAR LIKE '%varYear%' OR DECEASED LIKE '%varDeceased%' OR ALUMNI_ID  LIKE '%varAlmnID%' )

OR

SELECT LASTNAME, FIRSTNAME, MAIDENAME, ADDRESS, CITY, ZIP, EMAIL, YEAR, DECEASED
FROM xx###_180101
WHERE ADDRESS IS NULL AND ( LASTNAME LIKE '%varLname%' OR  FIRSTNAME LIKE '%varFname%' OR MAIDENAME LIKE '%varMname%' OR CITY LIKE '%varCity%' OR ZIP LIKE '%varZip%' OR EMAIL LIKE '%varEmail%' OR YEAR LIKE '%varYear%' OR DECEASED LIKE '%varDeceased%' OR ALUMNI_ID  LIKE '%varAlmnID%' )

do you get any error?

Previously, I have tried this -- using parenthesis, and not using parenthesis.  In both cases there are errors.

But, I'll try again.  It will be some hours before I can test this today.

Thanks for trying to help me resolve this issue.  As you say, it should be easy.  But something is not right.

As you say, it should be easy.  But something is not right.

yes, you can try debugging using less conditions, and then add on the criterion when necessary.

also check your data make sure you got something to test with.

This statement should work:  

SELECT *
FROM db_180101 WHERE ADDRESS IS NOT NULL
ORDER BY LASTNAME

But this query does not remove records that do not have data in the  ADDRESS field.

One more try:

SELECT FIRSTNAME, LASTNAME, ADDRESS, YEAR
FROM db_180101 WHERE ADDRESS IS NOT NULL AND YEAR = '64'
ORDER BY LASTNAME

This also does not work; the NULL ADDRESS records are included in the printout.

Either there is an anomoly in DW, or the database field, which is marked NULL on every row, falsely functions as proper data in the field.  These are my hunches.

SELECT *
FROM db_180101 WHERE ADDRESS IS NOT NULL
ORDER BY LASTNAME

But this query does not remove records that do not have data in the  ADDRESS field.

can you differentiate your data in Address field with null and is not null (including empty)?

do some tests like below:

SELECT count(*) cnt FROM db_180101 WHERE ADDRESS IS NULL;
SELECT count(*) cnt FROM db_180101 WHERE ADDRESS IS NOT NULL;
SELECT count(*) cnt FROM db_180101 WHERE ADDRESS = '';
SELECT count(*) cnt FROM db_180101 WHERE trim(ADDRESS) = '';

Select allOpen in new window

what are you getting here?

I changed the ADDRESS field in the database to NOT NULL.  This did not change anything.  In other words, the ADDRESS NULL records still print.

After midnight now, so stopping until tomorrow, when I will try the test you sent.

I have done the four tests.  Here are the results:

#1     cnt   is  0  (i.e. zero)
#2     cnt   is  1717   (i.e. the total number of records in the database)
#3     cnt   is  1062   (i.e. the number of empty ADDRESS records)
#4     cnt   is  1062   (i.e. even the trimmed  ADDRESS field shows the empty records)

What are your conclusions?

A check on ADDRESS IS NOT NULL should show 1717 - 1062 = 655

Revisiting #2.  The total number of records is 1717.  This inludes empty ADDRESS fields (1062).  This means that test #2 counted all records including those where the ADDRESS FIELD IS NULL, which also means that the WHERE ADDRESS IS NOT NULL records were still counted in spite of this command not to count them.  If I'm right on that, it's a curious matter!

Thank you so much for staying with me on this.  But, the darkness thickens.

It seemed that this had most promise: WHERE IFNULL(ADDRESS, '') <> ''   When I tested this in the actual DW MX 2004 query, records with ADDRESS NULL still printed.

So, I went straight to the database and input this same where clause, WHERE IFNULL(ADDRESS, '') <> '' AND YEAR = "64";

There, going directly onto the database, this clause worked 100%.

DW MX 2004 has it's problems -- it is 13 years old.  Perhaps we have identified an issue.

Is there any hope!?

WHERE IFNULL(ADDRESS, '') <> ''   When I tested this in the actual DW MX 2004 query, records with ADDRESS NULL still printed.

what's the server side programming language are you using? can you "echo" or "response.write" the SQL?

I don't think it's DW MX issue (but need to know what SQL did DW generated) since the codes are using at Server Side not in local DW.

ADDRESS NULL still printed

on a separate note, try this and see what you get:

SELECT LASTNAME, FIRSTNAME, MAIDENAME, ADDRESS, CITY, ZIP, EMAIL, YEAR, DECEASED
FROM xx###_180101
WHERE IFNULL(ADDRESS, '') <> '' 

Select allOpen in new window

I don't think you will get something with Address = empty here. Pls note that strictly speaking NULL and empty are different stuffs.

DW MX 2004 has it's problems -- it is 13 years old.  Perhaps we have identified an issue.

yea, old tool could be buggy and probably to start using a newer tool when it's feasible

Again, it works on the server, but not in DW.

I have the entire Adobe CS6 package which includes DW.  But I have not learned it yet.

Wonder if you'd be interested to help me finish this project.  There are a few additional issues besides this particular query to deal with?

If so, maybe we can talk about it.

It seems you have the skills needed.

knowing that SQL statement is working fine and the issue is from the data.

let us know if you need further clarification else we may proceed to close this question

Hi Ryan,

Yes, the SQL issue is solved.  I have now turned to the matter of properly handling pagination of printouts.

So, with respect to the SQL issue, that "thread" can be closed.

Again, I'm very grateful for your help.

Curt L. Gustafsson

coool and the "thread" i meant was the series of discussions relating to this issue that we had went through. cheers

Ryan provided alternatives until the problem was solved.  I am grateful.

as proposed