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

Cannot find a way to implement WHERE ADDRESS IS NOT NULL, or even ADDRESS IS NULL.  Here is the recordset:

SELECT LASTNAME, FIRSTNAME, MAIDENAME, ADDRESS, CITY, ZIP, EMAIL, YEAR, DECEASED
FROM xx###_180101
WHERE 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%'

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

Yes I know, DW MX is old.  But so is the server.  This is not the problem.
Curt GustafssonAsked:
Who is Participating?
 
Ryan ChongConnect With a Mentor Commented:
What are your conclusions?
A check on ADDRESS IS NOT NULL should show 1717 - 1062 = 655
and based on the query results:

total records with address = null : 0
total records with address <> null : 1717
total records : 1717
total records with address = '' : 1062

so based on these:

WHERE ADDRESS IS NOT NULL ...

Open in new window

will  select all 1717 records

WHERE ADDRESS IS NULL ...

Open in new window

will select 0 record

if the Address field cannot be null, then apply these conditions instead:

SELECT LASTNAME, FIRSTNAME, MAIDENAME, ADDRESS, CITY, ZIP, EMAIL, YEAR, DECEASED
FROM xx###_180101
WHERE ADDRESS <> '' 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 = '' 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%' )

if the Address field can be null, then apply the conditions stated in ID: 42444523:

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%' )

if you want to check the length of Address field, regardless it's null OR '' (empty), then try:

SELECT LASTNAME, FIRSTNAME, MAIDENAME, ADDRESS, CITY, ZIP, EMAIL, YEAR, DECEASED
FROM xx###_180101
WHERE IFNULL(ADDRESS, '') <> '' 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 IFNULL(ADDRESS, '') = '' 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%' )
0
 
Ryan ChongCommented:
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?
0
 
Curt GustafssonAuthor Commented:
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.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Ryan ChongCommented:
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?
0
 
Curt GustafssonAuthor Commented:
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.
0
 
Ryan ChongCommented:
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.
0
 
Curt GustafssonAuthor Commented:
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.
0
 
Ryan ChongCommented:
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) = '';

Open in new window


what are you getting here?
0
 
Curt GustafssonAuthor Commented:
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.
0
 
Curt GustafssonAuthor Commented:
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!
0
 
Curt GustafssonAuthor Commented:
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!?
0
 
Ryan ChongCommented:
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, '') <> '' 

Open 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
0
 
Curt GustafssonAuthor Commented:
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.
0
 
Ryan ChongCommented:
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
0
 
Curt GustafssonAuthor Commented:
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
0
 
Ryan ChongCommented:
coool and the "thread" i meant was the series of discussions relating to this issue that we had went through. cheers
0
 
Curt GustafssonAuthor Commented:
Ryan provided alternatives until the problem was solved.  I am grateful.
0
 
Ryan ChongCommented:
as proposed
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.