SQL Query Help

19 May 2006 2:26 PM

schoultzy

Hello Everyone,

This is probably a simple fix so be kind when/if you reply. The query
below retrieves information for individuals based on a column named
ATTRIB_DEF, and assorted other columns; however, the ATTRIB_DEF column
is important one. Currently the query gets all individuals that have
an ATTRIB_DEF that contains the string 'AC1' at the beginning of the
entry. I want the query to do the opposite. I want all of the
individuals who do not have an ATTRIB_DEF of 'AC1'. The simple fix
would be to change the LIKE to NOT LIKE, however, the problem is that
there are multiple ATTRIB_DEF entries for a single individual. In
other words, one person can have several rows in the table and the only
difference in the rows would be the value of ATTRIB_DEF. Simply
changing the LIKE to NOT LIKE will return, along with those individuals
without an ATTRIB_DEF of 'AC1', all the individuals with an ATTRIB_DEF
of 'AC1' who also have ATTRIB_DEF entries that are not 'AC1'. I want
all the individuals who do not have an ATTRIB_DEF of 'AC1' in any of
their rows. I hope that I was clear enough. Thanks for you help in
advance. The SQL Query follows:

SELECT table_detail.table_desc,
         name_master.preferred_name,
         name_master.last_name,
         address_master.addr_line_1,
         address_master.addr_line_2,
         address_master.addr_line_3,
         address_master.city,
         address_master.state,
         address_master.zip,
         table_country.table_desc,
         hdx_table_suffix_v.table_desc,
         name_master.id_num
    FROM address_master,
         attribute_trans,
         name_master,
         table_country,
         table_detail,
         hdx_table_suffix_v,
         candidate,
         candidacy
   WHERE ( address_master.country *= table_country.table_value ) and
         ( name_master.prefix *= table_detail.table_value ) and
         ( name_master.suffix *= hdx_table_suffix_v.table_value ) and
         ( attribute_trans.id_num = name_master.id_num ) and
         ( name_master.id_num = address_master.id_num ) and
         ( name_master.current_address = address_master.addr_cde ) and

         ( name_master.id_num = candidate.id_num ) and
         ( candidacy.id_num = candidate.id_num ) and
         ( candidate.cur_yr = candidacy.yr_cde ) and
         ( candidate.cur_trm = candidacy.trm_cde ) and
         ( ( table_detail.column_name = 'prefix' ) AND
         ( attribute_trans.attrib_begin_dte is NULL ) AND

         ( attribute_trans.attrib_def like 'AC1%' ) AND

         (name_master.stop_all_mail is NULL OR
         name_master.stop_all_mail = 'N') AND
         candidate.cur_yr = '2007' AND
         candidate.cur_stage = '02' AND
         (candidacy.candidacy_type = 'F' OR
         candidacy.candidacy_type = 'U') AND
         candidate.udef_1a_1 <> '' ) and
    candidacy.cur_candidacy = 'Y'
ORDER BY attribute_trans.attrib_cde ASC,
         address_master.zip ASC,
         name_master.last_name ASC

19 May 2006 2:57 PM

Alejandro Mesa

schoultzy,

I would start by changing it to use ANSI style join.

AMB

Show quote

"schoultzy" wrote:

> Hello Everyone,
>
> This is probably a simple fix so be kind when/if you reply. The query
> below retrieves information for individuals based on a column named
> ATTRIB_DEF, and assorted other columns; however, the ATTRIB_DEF column
> is important one. Currently the query gets all individuals that have
> an ATTRIB_DEF that contains the string 'AC1' at the beginning of the
> entry. I want the query to do the opposite. I want all of the
> individuals who do not have an ATTRIB_DEF of 'AC1'. The simple fix
> would be to change the LIKE to NOT LIKE, however, the problem is that
> there are multiple ATTRIB_DEF entries for a single individual. In
> other words, one person can have several rows in the table and the only
> difference in the rows would be the value of ATTRIB_DEF. Simply
> changing the LIKE to NOT LIKE will return, along with those individuals
> without an ATTRIB_DEF of 'AC1', all the individuals with an ATTRIB_DEF
> of 'AC1' who also have ATTRIB_DEF entries that are not 'AC1'. I want
> all the individuals who do not have an ATTRIB_DEF of 'AC1' in any of
> their rows. I hope that I was clear enough. Thanks for you help in
> advance. The SQL Query follows:
>
> SELECT table_detail.table_desc,
>          name_master.preferred_name,
>          name_master.last_name,
>          address_master.addr_line_1,
>          address_master.addr_line_2,
>          address_master.addr_line_3,
>          address_master.city,
>          address_master.state,
>          address_master.zip,
>          table_country.table_desc,
>          hdx_table_suffix_v.table_desc,
>          name_master.id_num
>     FROM address_master,
>          attribute_trans,
>          name_master,
>          table_country,
>          table_detail,
>          hdx_table_suffix_v,
>          candidate,
>          candidacy
>    WHERE ( address_master.country *= table_country.table_value ) and
>          ( name_master.prefix *= table_detail.table_value ) and
>          ( name_master.suffix *= hdx_table_suffix_v.table_value ) and
>          ( attribute_trans.id_num = name_master.id_num ) and
>          ( name_master.id_num = address_master.id_num ) and
>          ( name_master.current_address = address_master.addr_cde ) and
>
>          ( name_master.id_num = candidate.id_num ) and
>          ( candidacy.id_num = candidate.id_num ) and
>          ( candidate.cur_yr = candidacy.yr_cde ) and
>          ( candidate.cur_trm = candidacy.trm_cde ) and
>          ( ( table_detail.column_name = 'prefix' ) AND
>          ( attribute_trans.attrib_begin_dte is NULL ) AND
>
>          ( attribute_trans.attrib_def like 'AC1%' ) AND
>
>          (name_master.stop_all_mail is NULL OR
>          name_master.stop_all_mail = 'N') AND
>          candidate.cur_yr = '2007' AND
>          candidate.cur_stage = '02' AND
>          (candidacy.candidacy_type = 'F' OR
>          candidacy.candidacy_type = 'U') AND
>          candidate.udef_1a_1 <> '' ) and
>     candidacy.cur_candidacy = 'Y'
> ORDER BY attribute_trans.attrib_cde ASC,
>          address_master.zip ASC,
>          name_master.last_name ASC
>
>

19 May 2006 3:38 PM

schoultzy

Is it possible to complete the task without doing that?

19 May 2006 4:05 PM

--CELKO--

This whole thing just feels wrong.

You are using the old style, proprietary outer join *= syntax and need
to correct that. It sounds like you are mixing data and metadata when
you have names like ""attribute definition"; I surely hope not!! You
have names like"Address_Master " to let us know that this is a
hierarchical file system in disguise? And you have avoided using
temporal data types for temporal data, like this was a Cobol file and
not SQL.

19 May 2006 4:23 PM

schoultzy

Thank you for your replies. Yes, I know that the SQL statement has
many flaws. I was not the one who wrote it and it is old. It does,
however, execute in SQL Server. I suppose my question is whether or
not this statement can be easily changed to accomplish my goal or if it
needs to be trashed. I was hoping for the former, but as replies come
in, I am seeing that the latter is probably more likely. Maybe someone
will see a way to fix the problem without a total re-write.

19 May 2006 4:26 PM

Alexander Kuznetsov

something like that:

and not exists(select 1 from attribute_trans where attrib_def like
'AC1%' and attrib_begin_dte is NULL and attribute_trans.id_num =
name_master.id_num )