Solved

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

Posted on 2009-06-30
8
345 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
 
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
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.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Send secure, cloud-based, encrypted alerts and maintain HIPAA compliant messaging. Integrates priority & secure messaging into one application. Ensures IT, emergency respondents and healthcare professionals that their critical messages are never mis…

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now