Solved

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

Posted on 2009-06-30
8
352 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

628 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