Complicated query

[Guest]

I am attempting to devlop a lsit for a mailing. I have many different
criteria that I need to use. This is the SQL query as I have it- it isn't
working...
SELECT iMIS_Name.FULL_NAME, iMIS_Name_Address.ADDRESS_1,
iMIS_Name_Address.CITY, iMIS_Name_Address.STATE_PROVINCE,
iMIS_Name_Address.ZIP, iMIS_Name.MEMBER_TYPE
FROM iMIS_Name INNER JOIN iMIS_Name_Address ON iMIS_Name.MAIL_ADDRESS_NUM =
iMIS_Name_Address.ADDRESS_NUM
WHERE (((iMIS_Name.FULL_NAME) Is Not Null) AND
((iMIS_Name_Address.ADDRESS_1) Is Not Null) AND ((iMIS_Name.MEMBER_TYPE)="O"
Or (iMIS_Name.MEMBER_TYPE)="I" Or (iMIS_Name.MEMBER_TYPE)="AIJ" Or
(iMIS_Name.MEMBER_TYPE)="AI" Or (iMIS_Name.MEMBER_TYPE)="AO") AND
((iMIS_Name.COUNTRY) Is Null)) OR (((iMIS_Name.FULL_NAME) Is Not Null) AND
((iMIS_Name_Address.ADDRESS_1) Is Not Null) AND
((iMIS_Name.MEMBER_TYPE)="HI") AND ((iMIS_Name.PAID_THRU)>=#12/31/2003#) AND
((iMIS_Name.COUNTRY) Is Null)) OR (((iMIS_Name.FULL_NAME) Is Not Null) AND
((iMIS_Name_Address.ADDRESS_1) Is Not Null) AND
((iMIS_Name_Address.STATE_PROVINCE)="NY" Or
(iMIS_Name_Address.STATE_PROVINCE)="VA" Or
(iMIS_Name_Address.STATE_PROVINCE)="PA") AND ((iMIS_Name.MEMBER_TYPE)="NO" Or
(iMIS_Name.MEMBER_TYPE)="NI") AND ((iMIS_Name.PAID_THRU)>=#1/1/2002#) AND
((iMIS_Name.COUNTRY) Is Null)) OR (((iMIS_Name.FULL_NAME) Is Not Null) AND
((iMIS_Name_Address.ADDRESS_1) Is Not Null) AND
((iMIS_Name_Address.STATE_PROVINCE)="OH" Or
(iMIS_Name_Address.STATE_PROVINCE)="MD" Or
(iMIS_Name_Address.STATE_PROVINCE)="NJ") AND ((iMIS_Name.MEMBER_TYPE)="NO" Or
(iMIS_Name.MEMBER_TYPE)="NI") AND ((iMIS_Name.PAID_THRU)>=#1/1/2002#) AND
((iMIS_Name.COUNTRY) Is Null)) OR (((iMIS_Name.MEMBER_TYPE)="HO") AND
((iMIS_Name.PAID_THRU)>=#12/31/2000#) AND ((iMIS_Name.COUNTRY) Is Null))
ORDER BY iMIS_Name_Address.ZIP, iMIS_Name.LAST_NAME;

 

[Duane Hookom]

You could simplify syntax like
((iMIS_Name_Address.STATE_PROVINCE)="NY" Or
(iMIS_Name_Address.STATE_PROVINCE)="VA" Or
(iMIS_Name_Address.STATE_PROVINCE)="PA")
with
iMIS_Name_Address.STATE_PROVINCE IN ("NY","VA","PA")
You could also do this with MEMBER_TYPE.
I suggest you try this and then come back with the much simpler SQL if it
doesn't work.

 

[Guest]

FOr all records I need to have compete name and addresses AND thta don'nt
have a country lsited (aka US members)
I need all of our members ("O","I",etc.) from all states
**in addtion**
I need "HO" that have been last updated 12/31/00, all "HI" that have been
last updated 12/31/03, and "NO" and"NI" from MD,NJ,NY,VA,OH and PA that have
been last updated 1/01/02

 

[John Vinson]

I need all of our members ("O","I",etc.) from all states
**in addtion**
I need "HO" that have been last updated 12/31/00, all "HI" that have been
last updated 12/31/03, and "NO" and"NI" from MD,NJ,NY,VA,OH and PA that have
been last updated 1/01/02

You may want - just for understandability! - to create three queries
for these three different conditions. You can use a UNION query to
combine the results of the three subsets.

John W. Vinson[MVP]

 

[Duane Hookom]

I would model all of this in tables. I don't like seeing hard-coded values
in criteria like this. What happens when your business rules change to add
or remove a state or member type? Do you go back into your queries to change
the sql? YUK!

At the very least, you could wrap all of this "decision tree" logic into a
user defined-function that accepts arguments of the state and type (possibly
other fields) and returns a true or false. You can easily maintain the
function when your business rules change.