Avoiding the need to repeat clauses in MySQL

[m.k.ball]

Thanks Rich - that's great. Before I found this group, I thought I had
a reasonable understanding of SQL (well, MySQL's implementation of it,
at least) but the truth is there are great chunks that I have no
knowledge of. I've read three or four books about MySQL and PHP all of
which gave (in retrospect) very basic examples of the use of SQL, and
focussed on features such as MySQL's date and time handling functions
that are well covered in PHP, so I came to the conclusion that
post-query processing with PHP was the way to go. I'm wishing now that
I had just sat and read the MySQL manual!

I'm constructing a query that searches through a list of people. Each
person has a firstname, lastname and businessname. Ordering is by
businessname, unless there is no record for businessname, in which case
the entry is ordered by lastname. Rich Ryan and Bill Karwin explain how
to do this in the following post

http://groups.google.co.uk/group/mai...dbea1fcccb53d0)

I'm also using the solution that Rich proposes in the previous post in
this thread to move 'The ' at the start of a business name to the end
of the name

and adding additional search clauses, for example, ordering by results
that start with a certain letter (the SQL below looks for results
starting with 't').

I initially thought that I would be able to use the first query below,
but MySQL responds that it does not recognise 'b' in the WHERE clause,
so I'm having to repeat the 'anti-The' CASE in every WHERE and ORDER BY
clause. Is there any way I can change this to avoid having to repeat
the CASE phrase?

If I use 'b' (as in the first SQL example below) in a simple ORDER BY
clause it works fine - the minute I use 'b' within a clause such as

COALESCE(
IF ( b = '', NULL , b ), CONCAT( ppl.ppl_lastnam e, ppl.ppl_firstna me )
)

it no longer recognises 'b'.

Michael

Table: aa_people
Fields: ppl_id, ppl_salutation, ppl_firstname, ppl_lastname, ppl_dept,
ppl_email, ppl_businessnam e

----------------------------------------------------------------------------------------------------------

SQL query1:
SELECT ppl.ppl_id, ppl.ppl_salutat ion, ppl.ppl_firstna me,
ppl.ppl_lastnam e, ppl.ppl_dept, ppl.ppl_email,

CASE WHEN SUBSTRING( ppl.ppl_busines sname, 1, 4 ) = "The "
THEN CONCAT( SUBSTRING( ppl.ppl_busines sname, 5, LENGTH(
ppl.ppl_busines sname ) -4 ) , ", The" )
ELSE ppl.ppl_busines sname
END

AS b

FROM aa_people ppl
WHERE 1

AND (
( b LIKE "t%" )
OR ( (b = "") && ( ppl.ppl_lastnam e LIKE "t%" ) )
)
ORDER BY
COALESCE(
IF ( b = '', NULL , b ), CONCAT( ppl.ppl_lastnam e, ppl.ppl_firstna me )
)
----------------------------------------------------------------------------------------------------------
SQL query2:
SELECT ppl.ppl_id, ppl.ppl_salutat ion, ppl.ppl_firstna me,
ppl.ppl_lastnam e, ppl.ppl_dept, ppl.ppl_email,

CASE WHEN SUBSTRING( ppl.ppl_busines sname, 1, 4 ) = "The "
THEN CONCAT( SUBSTRING( ppl.ppl_busines sname, 5, LENGTH(
ppl.ppl_busines sname ) -4 ) , ", The" )
ELSE ppl.ppl_busines sname
END

AS ppl_businessnam e

FROM aa_people ppl
WHERE 1
AND (
(

CASE WHEN SUBSTRING( ppl.ppl_busines sname, 1, 4 ) = "The "
THEN CONCAT( SUBSTRING( ppl.ppl_busines sname, 5, LENGTH(
ppl.ppl_busines sname ) -4 ) , ", The" )
ELSE ppl.ppl_busines sname
END

LIKE "t%"
)

OR
( (ppl_businessna me = "") && ( ppl.ppl_lastnam e LIKE "t%" ) )

)

ORDER BY

COALESCE(

IF (
ppl_businessnam e = '', NULL ,

CASE WHEN SUBSTRING( ppl.ppl_busines sname, 1, 4 ) = "The "
THEN CONCAT( SUBSTRING( ppl.ppl_busines sname, 5, LENGTH(
ppl.ppl_busines sname ) -4 ) , ", The" )
ELSE ppl.ppl_busines sname
END

),

CONCAT( ppl.ppl_lastnam e, ppl.ppl_firstna me )

)

[m.k.ball]

Sorry - I posted this as a new message, when actually it's a
continuation of the thread at

http://groups.google.co.uk/group/mai...94624b44297f05

Michael

[Bill Karwin]

<m.******@btint ernet.com> wrote in message
news:11******** *************@g 49g2000cwa.goog legroups.com...

I initially thought that I would be able to use the first query below,
but MySQL responds that it does not recognise 'b' in the WHERE clause,

Correct. It is unfortunate -- but it is documented -- that one cannot use
column aliases in the WHERE clause. Apparently this is in accordance with
the SQL standard.

http://dev.mysql.com/doc/refman/5.0/en/select.html says:
A select_expr can be given an alias using AS alias_name. The alias is used
as the
expression's column name and can be used in GROUP BY, ORDER BY, or HAVING
clauses.
... It is not allowable to use a column alias in a WHERE clause, because
the column value
might not yet be determined when the WHERE clause is executed.
See Section A.5.4, "Problems with Column Aliases".

Also see http://dev.mysql.com/doc/refman/5.0/...ith-alias.html for
further explanation for why this restriction exists.

However, a user's comment on the latter page shows that you can use column
aliases in HAVING, GROUP BY, and ORDER BY clauses. The suggestion is that
in some cases, you can achieve the same end result by putting references to
your column alias in one or more of these clauses.

Regards,
Bill K.