SELECT
IF(image_location_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_location_country_alt) desc,
upper(image_location_country) DESC,
upper(image_location_state) DESC,
upper(image_location_city) DESC,
upper(image_name) DESC,
upper(right(image_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 8  3245  ph**************@gmail.com wrote:
SELECT
IF(image_location_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_location_country_alt) desc,
upper(image_location_country) DESC,
upper(image_location_state) DESC,
upper(image_location_city) DESC,
upper(image_name) DESC,
upper(right(image_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_location_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_location_country) DESC,
upper(image_location_state) DESC,
upper(image_location_city) DESC,
upper(image_name) DESC,
upper(right(image_name, 2)) DESC
LIMIT 1
strawberry wrote:
ph**************@gmail.com wrote:
SELECT
IF(image_location_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_location_country_alt) desc,
upper(image_location_country) DESC,
upper(image_location_state) DESC,
upper(image_location_city) DESC,
upper(image_name) DESC,
upper(right(image_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_location_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_location_country) DESC,
upper(image_location_state) DESC,
upper(image_location_city) DESC,
upper(image_name) DESC,
upper(right(image_name, 2)) DESC
LIMIT 1
No it doesn't "unknown column "isnull""
Phil ph**************@gmail.com wrote:
strawberry wrote:
ph**************@gmail.com wrote:
SELECT
IF(image_location_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_location_country_alt) desc,
upper(image_location_country) DESC,
upper(image_location_state) DESC,
upper(image_location_city) DESC,
upper(image_name) DESC,
upper(right(image_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_location_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_location_country) DESC,
upper(image_location_state) DESC,
upper(image_location_city) DESC,
upper(image_name) DESC,
upper(right(image_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?
strawberry wrote:
ph**************@gmail.com wrote:
strawberry wrote:
ph**************@gmail.com wrote:
SELECT
IF(image_location_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_location_country_alt) desc,
upper(image_location_country) DESC,
upper(image_location_state) DESC,
upper(image_location_city) DESC,
upper(image_name) DESC,
upper(right(image_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_location_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_location_country) DESC,
upper(image_location_state) DESC,
upper(image_location_city) DESC,
upper(image_name) DESC,
upper(right(image_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 ph**************@gmail.com wrote:
strawberry wrote:
ph**************@gmail.com wrote:
strawberry wrote:
ph**************@gmail.com wrote:
SELECT
IF(image_location_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_location_country_alt) desc,
upper(image_location_country) DESC,
upper(image_location_state) DESC,
upper(image_location_city) DESC,
upper(image_name) DESC,
upper(right(image_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_location_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_location_country) DESC,
upper(image_location_state) DESC,
upper(image_location_city) DESC,
upper(image_name) DESC,
upper(right(image_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)
strawberry wrote:
ph**************@gmail.com wrote:
strawberry wrote:
ph**************@gmail.com wrote:
strawberry wrote:
ph**************@gmail.com wrote:
SELECT
IF(image_location_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_location_country_alt) desc,
upper(image_location_country) DESC,
upper(image_location_state) DESC,
upper(image_location_city) DESC,
upper(image_name) DESC,
upper(right(image_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_location_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_location_country) DESC,
upper(image_location_state) DESC,
upper(image_location_city) DESC,
upper(image_name) DESC,
upper(right(image_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 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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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"...
|
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
|
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...
|
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...
|
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...
|
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...
|
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.
...
|
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...
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: giovanniandrean |
last post by:
The energy model is structured as follows and uses excel sheets to give input data:
1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
|
by: NeoPa |
last post by:
Hello everyone.
I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report).
I know it can be done by selecting :...
|
by: Teri B |
last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course.
0ne-to-many. One course many roles.
Then I created a report based on the Course form and...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM)
Please note that the UK and Europe revert to winter time on...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
|
by: isladogs |
last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, Mike...
|
by: GKJR |
last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
| |
