Solved

How to get aournd undefined column error in MySQL without added another column

Posted on 2009-06-30
8
346 Views
Last Modified: 2012-05-07
What I am trying to do is pretty simple (or so I thought).  I may get an account name to look up either of the form "a" or "a@b". In the former case I want to compare against one column, in the later two columns concatenated.

Below shows what I have tried and the results.  I realize that the easy answer is to just add another column in the table for email but that seems like a duplication of information.  Another possibility is that it may work keeping all names in one column and then splitting them out into two parts if there is an "@" symbol but I like having two columns as it makes for easy sorting by either column when looking at the table directly.

I'm not sure yet whether the system I am setting will be able to insert into multiple tables so I currently trying to keep this as a one table solution.

However, I also realize that the answer may be "you can't do what you are trying to do", which is fine but I thought I would ask just in case there is a way.


#
# In both cases below consider all instances of "a@b" to also sometimes be of the form "a"
#
 
#
# Case 1: Does not return row in case of "a@b", does work in case of "a"
#
SELECT 
	`pwd` 
FROM `accts` 
WHERE  
	IF (
		INSTR('@', 'a@b') != 0, 
		`user` = 'a@b' AND `realm` IS NULL, 
		CONCAT_WS('@', `user`, `realm`) = 'a@b'
	)
 
#
# Case 2: Gives undefined column error
#
SELECT 
	`pwd`, 
	CONCAT_WS('@', `user`, `realm`) AS email
FROM `accts` 
WHERE  
	IF (
		INSTR('@', 'a@b') != 0, 
		`user` = 'a@b' AND `realm` IS NULL, 
		`email` = 'a@b'
	)

Open in new window

0
Comment
Question by:RegProctor
  • 4
  • 3
8 Comments
 
LVL 1

Author Comment

by:RegProctor
ID: 24743593
Made a mistake copying the code over. Here it is fixed (although not working so not sure I should say "fixed").

The only difference is "!=" changed to "=".
#
# In both cases below consider all instances of "a@b" to also sometimes be of the form "a"
#
 
#
# Case 1: Does not return row in case of "a@b", does work in case of "a"
#
SELECT 
	`pwd` 
FROM `accts` 
WHERE  
	IF (
		INSTR('@', 'a@b') = 0, 
		`user` = 'a@b' AND `realm` IS NULL, 
		CONCAT_WS('@', `user`, `realm`) = 'a@b'
	);
 
#
# Case 2: Gives undefined column error
#
SELECT 
	`pwd`, 
	CONCAT_WS('@', `user`, `realm`) AS email
FROM `accts` 
WHERE  
	IF (
		INSTR('@', 'a@b') = 0, 
		`user` = 'a@b' AND `realm` IS NULL, 
		`email` = 'a@b'
	);

Open in new window

0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 24750318
Try this:
SELECT 
        `pwd`, 
        CONCAT_WS('@', `user`, `realm`) AS email
FROM `accts` 
WHERE  
        IF (
                INSTR('@', 'a@b') = 0, 
                `user` = 'a@b' AND `realm` IS NULL, 
                CONCAT_WS('@', `user`, `realm`) = 'a@b'
        );

Open in new window

0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 24751455
You can't refer to an aliased column in the WHERE clause. You have three choices:

- Repeat the expression in the WHERE clause, like TerryAtOpus shows
- Use a HAVING clause instead of a WHERE clause to refer to filter those expressions
- Make the expression part of a subquery, as in SELECT email FROM (SELECT CONCAT_WS('@', `user`, `realm`) AS email FROM accts) AS dt WHERE email = blah...

The approach given by TerryAtOpus is probably best.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:RegProctor
ID: 24752747
As given by Terry still the same problem, shown below:
Given the data:
 
user  realm  pwd
 a      b     c
 a     NULL   d
 
In the case of:
WHERE  
        IF (
                INSTR('@', 'a@b') = 0, 
                `user` = 'a@b' AND `realm` IS NULL, 
                CONCAT_WS('@', `user`, `realm`) = 'a@b'
        );
 
returns: 0 rows (should be 1 row)
 
And in the case of:
WHERE  
        IF (
                INSTR('@', 'a') = 0, 
                `user` = 'a' AND `realm` IS NULL, 
                CONCAT_WS('@', `user`, `realm`) = 'a'
        );
 
returns: 1 row (just as it should.)

Open in new window

0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 24758441
I think you've got the logic mixed up for the if condition in the first case. It should be:
WHERE  
        IF (
                INSTR('@', 'a@b'),
                `user` = 'a@b' AND `realm` IS NULL,
                CONCAT_WS('@', `user`, `realm`) = 'a@b'
        );
 
And the second case just doesn't make sense, given the data you've got. If your realm is NULL then shouldn't the user be "a@b" otherwise neither of your options in the "if" will return true?
0
 
LVL 1

Author Comment

by:RegProctor
ID: 24761018
       IF (
                INSTR('@', 'a') = 0,
                `user` = 'a' AND `realm` IS NULL,
                CONCAT_WS('@', `user`, `realm`) = 'a'
        );

Replacing 'a' with the placeholder 'ToFind':
        IF (
                INSTR('@', 'ToFind') = 0,
                `user` = 'ToFind' AND `realm` IS NULL,
                CONCAT_WS('@', `user`, `realm`) = 'ToFind'
        );

 In pseudo code:
        IF (
                'ToFind' does not have an '@' symbol then do this (assume that the realm is NULL):
                                `user` = 'ToFind' AND `realm` IS NULL,
                otherwise, do this (assume that the realm is NOT NULL but a value we want to match):
                                CONCAT_WS('@', `user`, `realm`) = 'ToFind'
        );

I just don't see a problem with this.
0
 
LVL 35

Accepted Solution

by:
Terry Woods earned 350 total points
ID: 24768508
You're right - it's ok. Your problem is the order of the parameters passed to instr. This works for me on some test data:
       IF (
                INSTR('a@b','@') = 0,
                `user` = 'a' AND `realm` IS NULL,
                CONCAT_WS('@', `user`, `realm`) = 'a@b'
        )

Open in new window

0
 
LVL 1

Author Closing Comment

by:RegProctor
ID: 31598270
Duh! I can't believe I didn't see that!

Thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Multilanguage Database Design in MySQL 5 118
selecting date modified field from a table 2 45
RDBMS and No sql database 4 58
mysql update statement 3 23
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question