Re: WHERE with CASE colums?

[Yves Goergen]

On Thursday, December 04, 2003 6:22 PM CET, Martijn Tonies wrote:
> Cause the WHERE clause only works on columns. If you want to
> do a match on the result of the CASE, you have to do your case
> thingy again:
> 
> WHERE ( case ...yourstuff... end ) = 'something'

Wouldn't HAVING help here?

-- 
Yves Goergen
[EMAIL PROTECTED]
Please don't CC me (causes double mails)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: WHERE with CASE colums?

[Martijn Tonies]

Hi Scott,


===
When searching and listing them I want to remove any preceeding 'The ', 'An
', or 'A ' that occurs in the title and I also want to be able to search it.
The case statement as follows seems to work fine:

SELECT
CASE
WHEN title LIKE  'The %' THEN RIGHT( title, length( title )  -4  )
WHEN title LIKE  'A %' THEN RIGHT( title, length( title )  -2  )
WHEN title LIKE  'An %' THEN RIGHT( title, length( title )  -3  )
ELSE title
END  AS modtitle
FROM Journal_Info

But if I try a WHERE clause using the columns AS title I get an error.

Can anyone help me figure out how to search the CASEed column?
==

Are you trying:
WHERE modtitle = '... something ... '
?

Cause the WHERE clause only works on columns. If you want to
do a match on the result of the CASE, you have to do your case
thingy again:

WHERE ( case ...yourstuff... end ) = 'something'

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

WHERE with CASE colums?

[Scott Turnbull]

I have a table called 'Journal_Info' containing titles of various journals

When searching and listing them I want to remove any preceeding 'The ', 'An ', or 'A ' 
that occurs in the title and I also want to be able to search it.  The case statement 
as follows seems to work fine:

SELECT 
CASE 
WHEN title LIKE  'The %' THEN RIGHT( title, length( title )  -4  ) 
WHEN title LIKE  'A %' THEN RIGHT( title, length( title )  -2  ) 
WHEN title LIKE  'An %' THEN RIGHT( title, length( title )  -3  ) 
ELSE title
END  AS modtitle
FROM Journal_Info

But if I try a WHERE clause using the columns AS title I get an error.

Can anyone help me figure out how to search the CASEed column?

Thanks in advance for any help.

Scott