Peter Tanswell wrote:
I have a query that I have created - it works against a MS-Access database
but does not work with MySQL. I have searched through the MySQL notes that I
have but cant find out why - I think its because of the AS MatTotal section
of the query.
You can not use a column
Hi there
I have a query that I have created - it works against a MS-Access database
but does not work with MySQL. I have searched through the MySQL notes that I
have but cant find out why - I think its because of the AS MatTotal section
of the query.
What do you mean by does not work with mySQL
Peter,
The reason it's not working is not because of the alias, but because
you are trying to use the alias in the SELECT clause. MySQL only
allows you to reference aliases in the GROUP BY, ORDER BY, or HAVING
clauses. (http://dev.mysql.com/doc/refman/5.0/en/problems-with-
alias.html)
You can not use a column alias you define in the select list as a base
column to some other calculated column in that same select list, i.e.:
True.
SELECT A + B AS C, C / D AS E
I have to say I'm surprised Access actually _does_ allow you to do that. I'd
never tried it before now
Are you sure that URL_PASSWORD_ID had a value otherwise it may be
executing: WHERE PASSWORD_ID =
Output the variable before running the query to make sure that it isn't this
On 11/10/06, alex poyaoan [EMAIL PROTECTED] wrote:
Hi everybody need help on this update query which gives me a
Are you sure that URL_PASSWORD_ID had a value otherwise it may be
executing: WHERE PASSWORD_ID =
Output the variable before running the query to make sure that it isn't this
On 11/10/06, alex poyaoan [EMAIL PROTECTED] wrote:
Hi Rich,
Well the variable i guess is coming up since this is
PASSWORD is a reserved word for many DBs; try [PASSWORD] as the column
name instead.
On 11/10/06, alex poyaoan [EMAIL PROTECTED] wrote:
Are you sure that URL_PASSWORD_ID had a value otherwise it may be
executing: WHERE PASSWORD_ID =
Output the variable before running the query to make
PASSWORD is a reserved word for many DBs; try [PASSWORD] as the column
name instead.
On 11/10/06, alex poyaoan [EMAIL PROTECTED] wrote:
Thanks James it worked so i'll try not to use password as a field because as
you said it is a reserved word.. again thanks
The first thing to try is [password] rather than password. It's a reserved
word and needs to be escaped if you must use it. I'd personally rename it to
something else instead of trying to remember to escape it everytime.
-Original Message-
From: alex poyaoan [mailto:[EMAIL PROTECTED]
and apparently someone already said that. ;-)
cheers
-Original Message-
From: Bobby Hartsfield [mailto:[EMAIL PROTECTED]
Sent: Friday, November 10, 2006 10:24 AM
To: CF-Talk
Subject: RE: help on update query
The first thing to try is [password] rather than password. It's
Doug,
Even though someone already posted a solution to your particular problem, I
thought I'd share a technique I've used in the past to do arbitrary sorting
of query results.
This method invloves using ColdFusion's Query of Queries functionality. I
will use your query as an example, and for the
cfquery datasource=#request.site.dsn# name=get_child_categories
SELECT
categories.cat_id,
categories.category,
categories.parent_id
FROM
categories
WHERE
categories.parent_id =
cfqueryparam cfsqltype=cf_sql_integer value=#cat_id#
ORDER BY
categories.category asc
/cfquery
This looks about
As a long term solution, you might want to add a column to your products
table called SortOrder or Priority which contains the actual order you
want the query to output. This way you are not stuck to any particular way
of sorting (by id, name etc.) You could make the Other category 9 or
it at the end =)
Chris
-Original Message-
From: Josh Nathanson [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 27, 2006 2:26 PM
To: CF-Talk
Subject: [SPAM] Re: Help displaying a query result
As a long term solution, you might want to add a column to your products
table called
Whoops sorry, that should be add a column to your CATEGORIES table not
products table as in my first post.
Then of course you would add ORDER BY SortOrder to your query.
-- Josh
- Original Message -
From: Doug Brown [EMAIL PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
Sent:
I missed the need to have other at the bottom. Ok, to get Other at the
bottom:
cfquery datasource=#request.site.dsn# name=get_child_categories
SELECT
categories.cat_id,
categories.category,
categories.parent_id
FROM
categories
WHERE
categories.parent_id =
cfqueryparam
Doug Brown wrote:
I have a list of categories, and I want to sort them ascending, but I need to
have the category (other) always listed last. Anyone have ideas on how to
accomplish this?
IE:
Baby Backpacks
Baby Carriers and Slings
Baby Jumpers
Play Pens
Vibrating Chairs
Other
My
about a arrays?
- Original Message -
From: Peterson, Chris [EMAIL PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
Sent: Wednesday, September 27, 2006 12:51 PM
Subject: Re: Help displaying a query result
For the 'fancy' way, you could always do, after the query:
cfset queryAddRow
: Help displaying a query result
Doug Brown wrote:
I have a list of categories, and I want to sort them ascending, but I
need to have the category (other) always listed last. Anyone have ideas on
how to accomplish this?
IE:
Baby Backpacks
Baby Carriers and Slings
Baby Jumpers
Play Pens
For some reason the post did not make it fully through. Try again:
SELECT MAX(QuizDate) as QuizDate, QuizID
FROM Test_Board_Biomed
WHERE Customer_ID = 21
GROUP BY QuizID
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
Having problems with sending full post to list and problem with
a query.
Try third time
SELECT MAX(QuizDate) as QuizDate, QuizID
FROM Test_Board_Biomed
WHERE Customer_ID = 21
GROUP BY QuizID
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations
PROTECTED] [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 14, 2004 1:09 PM
To: CF-Talk
Subject: Re: Help with Select Query
Having problems with sending full post to list and problem with
a query.
Try third time
SELECT MAX(QuizDate) as QuizDate, QuizID
FROM Test_Board_Biomed
If your post is being truncated, IIRC long strings of dashes - might trigger a truncate in forwarded e-mails from HOF.
--
Ian Skinner
Web Programmer
BloodSource
file:///C:\Documents%20and%20Settings\iskinner\Application%20Data\Microsoft\Signatures\www.BloodSource.org
untested
cfquery ...
select
max(eHistory.eHistory_ID) as LastRow_ID,
max(eHistory.Date_Out) as columnofinterest
from eHistory
where eHistory.Equip_ID = 2
group by eHistory.eHistory_ID, eHistory.Date_Out
/cfquery
/untested
Doug
Gonzo Rock wrote:
using MS-SQL Server here and I am trying to
Hi Gonz,
It might be better to do this as a subquery like so:
cfquery name=LastRowID datasource=#attributes.datasource#
select eHistory.Date_Out as columnofinterest
from eHistory
where eHistory.Equip_ID = 2
and eHistory.eHistory_ID IN (select max(eHistory.eHistory_ID) from
eHistory where
Yes, (I just added a, ehistory.ehistory_id as LastRow_ID to the first query)...
it pulls up one record I'll have to wait until there are more than one
records in the database before claiming victory. This is an app that has
been running against Postgres and there are a couple of MSSQL
Duane Boudreau wrote:
SELECT ID,
IF ImageURL6 IS NOT NULL
BEGIN
SET @numPhotos = 6
END
IF ImageURL5 IS NOT NULL
BEGIN
SET @numPhotos = 5
END
...
ELSE
BEGIN
SET @numPhotos = 0
END
FROM cdmdatafeed
In SQL it would be:
SELECT
ID,
CASE
WHEN
Jochem van Dieten wrote:
In SQL it would be:
SELECT
ID,
CASE
WHEN ImageURL6 IS NOT NULL
THEN numPhotos = 6
WHEN ImageURL5 IS NOT NULL
THEN numPhotos = 5
WHEN ImageURL4 IS NOT NULL
THEN numPhotos = 4
WHEN ImageURL3 IS NOT NULL
THEN numPhotos = 3
WHEN
thanks that did it.
duane
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 16, 2003 5:19 PM
To: CF-Talk
Subject: Re: Help with A Query
Jochem van Dieten wrote:
In SQL it would be:
SELECT
ID,
CASE
WHEN ImageURL6 IS NOT NULL
At 12:15 PM 1/10/2003, you wrote:
I would do the following
SELECT tblShopper.shopperID
FROM tblShopper
WHERE shopperID NOT IN (SELECT DISTINCT tblOrder.shopperid FROM tblorder)
Didn't catch the original post, but you could probably to the above more
efficiently with an outer join
SELECT
If you're using SQL Server, you could rewrite your query like this:
SELECT s.shopperID
FROM tblShopper s
WHERE NOT EXISTS (SELECT * FROM tblorder o where o.shopperID = s.shopperID)
~Dina
- Original Message -
From: Eric Creese
To: CF-Talk
Sent: Friday, January 10, 2003 10:15
- Original Message -
From: Dina Hess
To: CF-Talk
Sent: Friday, January 10, 2003 2:07 PM
Subject: Re: Help with a query.
If you're using SQL Server, you could rewrite your query like this:
SELECT s.shopperID
FROM tblShopper s
WHERE NOT EXISTS (SELECT * FROM tblorder o where
]]
Sent: Wednesday, May 29, 2002 8:12 PM
To: CF-Talk
Subject: RE: Help with using query with [] notation
Hey Dave,
I have waited for a while for this :) You can do this:
myQuery['col_id']['rowNumber']
On CF 4.5 on Solaris at least. It also works in CFMX on Win 2k.
My test code
I know it's possible to do something like this:
MyQuery[FieldName] (I know it's probably wrong, but
serves the purpose here)
For the record, you can't do that, but you can do this:
MyQuery.FieldName[i]
to reference the column value at row i.
The CF Documentation does mention this
-
From: Dave Watts [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 30 May 2002 2:05 p.m.
To: CF-Talk
Subject: RE: Help with using query with [] notation
I know it's possible to do something like this:
MyQuery[FieldName] (I know it's probably wrong, but
serves the purpose here
To: CF-Talk
Subject: RE: Help with using query with [] notation
I know it's possible to do something like this:
MyQuery[FieldName] (I know it's probably wrong, but
serves the purpose here)
For the record, you can't do that, but you can do this:
MyQuery.FieldName[i]
to reference
: Wednesday, May 29, 2002 10:05 PM
To: CF-Talk
Subject: RE: Help with using query with [] notation
I know it's possible to do something like this:
MyQuery[FieldName] (I know it's probably wrong, but
serves the purpose here)
For the record, you can't do that, but you can do
I have waited for a while for this :)
That probably means you need to get out more!
You can do this:
myQuery['col_id']['rowNumber']
Yes, you certainly can. However, if you omit the array position, you'll get
an error - at least, that's been my experience. That is, if you do this:
and you can even write
#ArrayMax(MyQuery[FieldName])#
if FieldName is a numeric column. Cool huh?
That is pretty cool, and I haven't seen that before. However, I suspect it
may not work in versions of CF prior to 4.5.1, so if you're writing code
that may run on an old server, be careful!
Your right, I do need to get out more, but I still have som much to learn!!
Tim
-Original Message-
From: Dave Watts [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 29, 2002 10:39 PM
To: CF-Talk
Subject: RE: Help with using query with [] notation
I have waited for a while
I am sorry, I was bad there. I am just so in awe of you that
it was nice to think I knew someting for once :)
No need to apologize - I'm sure you know many things I don't know! Anyway,
you didn't insult me or anything like that, so don't worry.
Dave Watts, CTO, Fig Leaf Software
basically, do this
make a bunch of hidden fields ...
like S_name, S_address, S_city, S_state, and insert the value of the
addresses this user used. repeat this x amount of times. X being the number
of different addresses they used.
this essentially creates an array. so lets say u have 3
Hi,
SELECT DISTINCT fruit, count(fruit) AS fruitcount
FROM greengrocer
GROUP BY fruit
ORDER BY fruit
HTH,
Chris
--Original Message--
From: "chad jackson" [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: January 25, 2001 11:39:49 PM GMT
Subject: help with sql query/CF
Hi,
I
Try:
SELECT fruit, COUNT(fruit) AS NumFruits
FROM greengrocer
GROUP BY fruit
ORDER BY fruit
Russel Madere, Jr. Senior Web Developer
ICQ: 5446158 http://www.TurboSquid.com
Some days you eat the
Select fruit,count(fruit)
From greengrocer
Group By fruit
John Anderson
http://www.aloha-webdesign.com
-Original Message-
From: chad jackson [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 25, 2001 3:40 PM
To: CF-Talk
Subject: help with sql query/CF
Hi,
I have a standard query
Well, the problem is with your join - each row in table 1 is being joined
with EVERY row in table 2, so that your result set is 6 rows rather than 3.
What happens if you take out the where a.user=b.user condition and just have
WHERE a.user = 'karl' and b.user = 'karl'?
If you give us a little
Using a UNION did the trick. Thanks for the tip.
Gary
- Original Message -
From: "Hayes, David" [EMAIL PROTECTED]
To: "CF-Talk" [EMAIL PROTECTED]
Sent: Wednesday, November 29, 2000 8:21 AM
Subject: RE: Help With SQL Query
Well, the problem is with your join -
note the "ENTERPRISE ENCRYPTION CTR." ...thats my problem...it should
be listing the right product for the product code that was given...it
does...but it only the first record. can anyone explain why ?
...
CFQUERY Name="knownissues" DataSource="CCMast"
select *
from nxsdfct
order by
I've got an existing database table that contains contact
(address, city, email, etc) information and OnOff states
for approximately 30 products.
Table Structure:
company_ID
company_Name
company_Address
company_City
company_Email
company_RedBalloons
company_GreenBalloons
49 matches
Mail list logo
