Solved

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

Posted on 2009-06-30
8
344 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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.

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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

21 Experts available now in Live!

Get 1:1 Help Now