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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
as proposed
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.