473,836 Members | 1,464 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

"unknown column image_location_ country_alt" but this column must exist!

SELECT
IF(image_locati on_country IS NULL OR image_location_ country = '', 'x',
NULL) AS image_location_ country_alt,
image_path

FROM image

WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'

ORDER BY upper(image_loc ation_country_a lt) desc,
upper(image_loc ation_country) DESC,
upper(image_loc ation_state) DESC,
upper(image_loc ation_city) DESC,
upper(image_nam e) DESC,
upper(right(ima ge_name, 2)) DESC

LIMIT 1
This query produces the following error:

Unknown column 'image_location _country_alt' in 'order clause' using
query
I'm sorry but I must have this "column" in the query, it's vital for
required sorting order (you have to sort image_location_ country in
alphanumeric order, however, that column can also be null, BUT all
NON-NULL fields MUST BE FIRST before all NULL fields!)

I'm not sure what's happening, please help!

Thanx
Phil

Sep 18 '06 #1
8 3289

ph************* *@gmail.com wrote:
SELECT
IF(image_locati on_country IS NULL OR image_location_ country = '', 'x',
NULL) AS image_location_ country_alt,
image_path

FROM image

WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'

ORDER BY upper(image_loc ation_country_a lt) desc,
upper(image_loc ation_country) DESC,
upper(image_loc ation_state) DESC,
upper(image_loc ation_city) DESC,
upper(image_nam e) DESC,
upper(right(ima ge_name, 2)) DESC

LIMIT 1

This query produces the following error:

Unknown column 'image_location _country_alt' in 'order clause' using
query

I'm sorry but I must have this "column" in the query, it's vital for
required sorting order (you have to sort image_location_ country in
alphanumeric order, however, that column can also be null, BUT all
NON-NULL fields MUST BE FIRST before all NULL fields!)

I'm not sure what's happening, please help!

Thanx
Phil
Not quite sure what's going on here, but does this work:

SELECT image_path, IF(image_locati on_country IS NULL OR
image_location_ country ='', '1', 0) AS isnull
FROM image
WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'
ORDER BY isnull ASC,
upper(image_loc ation_country) DESC,
upper(image_loc ation_state) DESC,
upper(image_loc ation_city) DESC,
upper(image_nam e) DESC,
upper(right(ima ge_name, 2)) DESC
LIMIT 1

Sep 18 '06 #2

strawberry wrote:
ph************* *@gmail.com wrote:
SELECT
IF(image_locati on_country IS NULL OR image_location_ country = '', 'x',
NULL) AS image_location_ country_alt,
image_path

FROM image

WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'

ORDER BY upper(image_loc ation_country_a lt) desc,
upper(image_loc ation_country) DESC,
upper(image_loc ation_state) DESC,
upper(image_loc ation_city) DESC,
upper(image_nam e) DESC,
upper(right(ima ge_name, 2)) DESC

LIMIT 1
This query produces the following error:

Unknown column 'image_location _country_alt' in 'order clause' using
query
I'm sorry but I must have this "column" in the query, it's vital for
required sorting order (you have to sort image_location_ country in
alphanumeric order, however, that column can also be null, BUT all
NON-NULL fields MUST BE FIRST before all NULL fields!)

I'm not sure what's happening, please help!

Thanx
Phil

Not quite sure what's going on here, but does this work:

SELECT image_path, IF(image_locati on_country IS NULL OR
image_location_ country ='', '1', 0) AS isnull
FROM image
WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'
ORDER BY isnull ASC,
upper(image_loc ation_country) DESC,
upper(image_loc ation_state) DESC,
upper(image_loc ation_city) DESC,
upper(image_nam e) DESC,
upper(right(ima ge_name, 2)) DESC
LIMIT 1
No it doesn't "unknown column "isnull""

Phil

Sep 18 '06 #3

ph************* *@gmail.com wrote:
strawberry wrote:
ph************* *@gmail.com wrote:
SELECT
IF(image_locati on_country IS NULL OR image_location_ country = '', 'x',
NULL) AS image_location_ country_alt,
image_path
>
FROM image
>
WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'
>
ORDER BY upper(image_loc ation_country_a lt) desc,
upper(image_loc ation_country) DESC,
upper(image_loc ation_state) DESC,
upper(image_loc ation_city) DESC,
upper(image_nam e) DESC,
upper(right(ima ge_name, 2)) DESC
>
LIMIT 1
>
This query produces the following error:
>
Unknown column 'image_location _country_alt' in 'order clause' using
query
>
I'm sorry but I must have this "column" in the query, it's vital for
required sorting order (you have to sort image_location_ country in
alphanumeric order, however, that column can also be null, BUT all
NON-NULL fields MUST BE FIRST before all NULL fields!)
>
I'm not sure what's happening, please help!
>
Thanx
Phil
Not quite sure what's going on here, but does this work:

SELECT image_path, IF(image_locati on_country IS NULL OR
image_location_ country ='', '1', 0) AS isnull
FROM image
WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'
ORDER BY isnull ASC,
upper(image_loc ation_country) DESC,
upper(image_loc ation_state) DESC,
upper(image_loc ation_city) DESC,
upper(image_nam e) DESC,
upper(right(ima ge_name, 2)) DESC
LIMIT 1

No it doesn't "unknown column "isnull""

Phil
Can I just confirm that you're using mysql 4.1 or later?

Sep 18 '06 #4

strawberry wrote:
ph************* *@gmail.com wrote:
strawberry wrote:
ph************* *@gmail.com wrote:
SELECT
IF(image_locati on_country IS NULL OR image_location_ country = '', 'x',
NULL) AS image_location_ country_alt,
image_path

FROM image

WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'

ORDER BY upper(image_loc ation_country_a lt) desc,
upper(image_loc ation_country) DESC,
upper(image_loc ation_state) DESC,
upper(image_loc ation_city) DESC,
upper(image_nam e) DESC,
upper(right(ima ge_name, 2)) DESC

LIMIT 1
This query produces the following error:

Unknown column 'image_location _country_alt' in 'order clause' using
query
I'm sorry but I must have this "column" in the query, it's vital for
required sorting order (you have to sort image_location_ country in
alphanumeric order, however, that column can also be null, BUT all
NON-NULL fields MUST BE FIRST before all NULL fields!)

I'm not sure what's happening, please help!

Thanx
Phil
>
Not quite sure what's going on here, but does this work:
>
SELECT image_path, IF(image_locati on_country IS NULL OR
image_location_ country ='', '1', 0) AS isnull
FROM image
WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'
ORDER BY isnull ASC,
upper(image_loc ation_country) DESC,
upper(image_loc ation_state) DESC,
upper(image_loc ation_city) DESC,
upper(image_nam e) DESC,
upper(right(ima ge_name, 2)) DESC
LIMIT 1
No it doesn't "unknown column "isnull""

Phil

Can I just confirm that you're using mysql 4.1 or later?
Yes, 4.1.20

Sep 18 '06 #5

ph************* *@gmail.com wrote:
strawberry wrote:
ph************* *@gmail.com wrote:
strawberry wrote:
ph************* *@gmail.com wrote:
SELECT
IF(image_locati on_country IS NULL OR image_location_ country = '', 'x',
NULL) AS image_location_ country_alt,
image_path
>
FROM image
>
WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'
>
ORDER BY upper(image_loc ation_country_a lt) desc,
upper(image_loc ation_country) DESC,
upper(image_loc ation_state) DESC,
upper(image_loc ation_city) DESC,
upper(image_nam e) DESC,
upper(right(ima ge_name, 2)) DESC
>
LIMIT 1
>
This query produces the following error:
>
Unknown column 'image_location _country_alt' in 'order clause' using
query
>
I'm sorry but I must have this "column" in the query, it's vital for
required sorting order (you have to sort image_location_ country in
alphanumeric order, however, that column can also be null, BUT all
NON-NULL fields MUST BE FIRST before all NULL fields!)
>
I'm not sure what's happening, please help!
>
Thanx
Phil

Not quite sure what's going on here, but does this work:

SELECT image_path, IF(image_locati on_country IS NULL OR
image_location_ country ='', '1', 0) AS isnull
FROM image
WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'
ORDER BY isnull ASC,
upper(image_loc ation_country) DESC,
upper(image_loc ation_state) DESC,
upper(image_loc ation_city) DESC,
upper(image_nam e) DESC,
upper(right(ima ge_name, 2)) DESC
LIMIT 1
>
No it doesn't "unknown column "isnull""
>
Phil
Can I just confirm that you're using mysql 4.1 or later?

Yes, 4.1.20

So this query works fine:

SELECT image_path AS imagepath FROM image ORDER BY imagepath LIMIT 1;

Sorry, I'm clutching at straws now (as if you couldn't guess)

Sep 18 '06 #6

strawberry wrote:
ph************* *@gmail.com wrote:
strawberry wrote:
ph************* *@gmail.com wrote:
strawberry wrote:
ph************* *@gmail.com wrote:
SELECT
IF(image_locati on_country IS NULL OR image_location_ country = '', 'x',
NULL) AS image_location_ country_alt,
image_path

FROM image

WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'

ORDER BY upper(image_loc ation_country_a lt) desc,
upper(image_loc ation_country) DESC,
upper(image_loc ation_state) DESC,
upper(image_loc ation_city) DESC,
upper(image_nam e) DESC,
upper(right(ima ge_name, 2)) DESC

LIMIT 1
This query produces the following error:

Unknown column 'image_location _country_alt' in 'order clause' using
query
I'm sorry but I must have this "column" in the query, it's vital for
required sorting order (you have to sort image_location_ country in
alphanumeric order, however, that column can also be null, BUT all
NON-NULL fields MUST BE FIRST before all NULL fields!)

I'm not sure what's happening, please help!

Thanx
Phil
>
Not quite sure what's going on here, but does this work:
>
SELECT image_path, IF(image_locati on_country IS NULL OR
image_location_ country ='', '1', 0) AS isnull
FROM image
WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'
ORDER BY isnull ASC,
upper(image_loc ation_country) DESC,
upper(image_loc ation_state) DESC,
upper(image_loc ation_city) DESC,
upper(image_nam e) DESC,
upper(right(ima ge_name, 2)) DESC
LIMIT 1

No it doesn't "unknown column "isnull""

Phil
>
Can I just confirm that you're using mysql 4.1 or later?
Yes, 4.1.20


So this query works fine:

SELECT image_path AS imagepath FROM image ORDER BY imagepath LIMIT 1;

Sorry, I'm clutching at straws now (as if you couldn't guess)
I got it.. but the solution makes no sense. You apparently can't do
upper() on this alias because MySQL doesn't allow functions upon
aliases.

Phil

Sep 18 '06 #7
ph************* *@gmail.com wrote:
Unknown column 'image_location _country_alt' in 'order clause' using
query
I have confirmed the error you saw, using both MySQL 4.1.21 and 5.0.24a.

This looks identical to the problem in Bug #11694:
http://bugs.mysql.com/bug.php?id=11694

If you just use the plain column alias name, it works. If you include
it in an UPPER() expression, it breaks. This is consistent with the bug
described in Bug #11694.

That bug was supposed to have been fixed as of MySQL 4.1.16. But it
appears to have regressed (that is, re-broken).

Regards,
Bill K.
Sep 18 '06 #8
Bill Karwin wrote:
This looks identical to the problem in Bug #11694:
http://bugs.mysql.com/bug.php?id=11694
I did another test, and narrowed down the problem. It's not exactly a
regression; the test cases in Bug #11694 are still fixed correctly.
But it's a slightly different problem.

If the alias is for an expression instead of a plain column name in the
select-list, MySQL gives an error if you use the alias in an expression
in the ORDER BY clause.

I've logged a new bug: http://bugs.mysql.com/bug.php?id=22457
See that bug log for more detailed explanation, and steps to demonstrate
the bug.

Regards,
Bill K.
Sep 18 '06 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
3392
by: Marcus | last post by:
I'm running into a situation that has me adding a value of "Unknown" to a reference table. I am being pulled between two trains of thought, and was curious to get other's input on in. I give an example below. 1-) Adding "Unknown" to a reference table is bad. Doing so effectively changes the Nullability option of every FK that references the table to a NULLable FK relation. 2-) Simply adding a "Not Known/Undetermined" value to the...
3
6658
by: Ed L. | last post by:
On 7.4.6, is there any problem with defining one column of a view to be a string literal? For example ... $ psql -c "create view fooview as select 'bar' as footype" WARNING: column "footype" has type "unknown" DETAIL: Proceeding with relation creation anyway. CREATE VIEW Or is this warning just noise in this case?
1
8257
by: Sandro Daehler | last post by:
Hy i have a problem with the following statement: SELECT MAX(id) FROM apconfig; if i send this statement i got the following error: ERROR: column "id" does not exist
9
10758
by: Klaus Johannes Rusch | last post by:
IE7 returns "unknown" instead of "undefined" when querying the type of an unknown property of an object, for example document.write(typeof window.missingproperty); Has "unknown" been defined as a valid return value for the typeof operator in a later version of ECMAScript or is this a JScript "feature"? -- Klaus Johannes Rusch
6
2770
by: Sile | last post by:
Hello, I'm trying to get f2py working from the command line on windows XP. I have mingw32 as my C complier (after some advice on a previous thread) and Compaq Visual Fortran 6.5. Changing my C complier reduced my errors but I'm still having trouble. I think I have all the correct paths set but I'm not sure. F2PY gets further when I specifically tell it what my compilers are as follows................. C:\Program...
7
38031
by: John | last post by:
Hi Everyone, I'm having this extremely annoying problem with Internet Explorer 6, giving me an error message saying "unknown runtime error" whenever I try to alter the contents of a <divelement using innerHTML. Now, I've researched this problem on the web, and found many references to it, but none of them quite addressed my specific situation, and since my experience with JavaScript is limited, I was not able to adapt the solutions I...
0
4181
by: Peter Nofelt | last post by:
Hi all, ISSUE: ==================== In SQL 2005 (sp2) I get the following error when preforming a bulk insert with an associated xml format file: "Could not bulk insert. Unknown version of format file" Question:
6
14393
by: crs27 | last post by:
Hai, Im getting this error.There is a column by name "nr_first_name" in the table "gistl_new_request". Yet im getting as that dose not exist.Would like to now what is the cause of it. Thank You
1
8683
by: JaganMohanRao | last post by:
i have create a table and inserted a values init. when i am getting the values it is throwing the exception "ERROR: column "dtype" of relation "xxxx" does not exist" where xxxx is table name. if any body knows the solution kindly help me Thanks in advance .. Jagan
0
9671
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10551
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10595
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10254
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9379
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7793
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6979
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
2
4021
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.