With ColdFusion 9, we ended up replacing all the wildcard selects with actual
column names, among other fortifications. We did not experience this issue on
6.1.
~|
Order the Adobe Coldfusion Anthology now!
Since everyone should be in a SQL Join state of mind...here's another one.
I have a weird issue that I haven't been able to narrow down yet. I'm trying to
add a new field to one of my tables to store some additional information, but
once I add the column, my previously working query breaks.
This might be an issue I've had to deal with before.
Do something to change the fingerprint (no idea what the correct term
would be) of the query - or restart cf. By change the fingerprint, it
could be something as simple ad swapping p.* and pt.* (swapping as
in their ordinal position in the
On 2/1/2011 1:23 PM, Debbie Morris wrote:
What am I overlooking?
The evil of using * in SELECT clauses.
When that is done, database drivers are know to cache the columns and
datatypes of the SQL queries. Then somebody comes along and changes the
database structure, like you adding a field.
On 2/1/2011 2:21 PM, Charlie Stell wrote:
I assume this is something on CF's side -
as restarting the CF service also fixes it.
Not ColdFusion itself, but the database drivers used by ColdFusion and
the cached (pooled) data source settings.
Changing the Datasource to not used pooled settings
The evil of using * in SELECT clauses.
I'm with Ian on this 100%. Often times developers think that using * will be
faster, and easier and allow more flexibility. However that couldn't be
further from the truth as you are seeing now. Take Ian's advice and define
each column you want from your
, so I'll tackle this
again in the morning. Thanks for the help!
Debbie
-Original Message-
From: Michael Grant [mailto:mgr...@modus.bz]
Sent: Tuesday, February 01, 2011 5:41 PM
To: cf-talk
Subject: Re: SQL query question
The evil of using * in SELECT clauses.
I'm with Ian on this 100
Ewok wrote:
this is by far the easiest way to link tables.
No... Its the only way to link tables that's why we do it.
Not at all, you can link tables in many ways: you just have to do
it all by hand instead of using the features that are provided by
the database.
It seems that you are
S.Isaac Dealey wrote:
I would expect in this case that having a primary key on the table he
described wouldn't change the sql syntax much (if at all).
It wouldn't change the syntax at all. For all we know it has a
primary key and if not, how about the following one:
ALTER TABLE ADD PRIMARY
, May 22, 2005 3:10 AM
To: CF-Talk
Subject: Re: SQL Query Question
Ewok wrote:
this is by far the easiest way to link tables.
No... Its the only way to link tables that's why we do it.
Not at all, you can link tables in many ways: you just have to do
it all by hand instead of using
to decide
which are the
latest records
-Original Message-
From: Jeff Chastain [mailto:[EMAIL PROTECTED]
Sent: Friday, May 20, 2005 9:59 AM
To: CF-Talk
Subject: OT: SQL Query Question
Sorry for the off-topic, but I have been beating on this
one for a while and
the only answer I can
Ewok wrote:
Use distinct and order descending by crid. In the future, I highly recommend
a unique key (PK) or at least a date/time field to decide which are the
latest records
The datamodel is fine, crID and actID function nicely as
composite primary key.
Jochem
How so with duplicates of both?
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Saturday, May 21, 2005 10:13 AM
To: CF-Talk
Subject: Re: SQL Query Question
Ewok wrote:
Use distinct and order descending by crid. In the future, I highly
recommend
a unique key
#
/cfoutput
/pre
-Original Message-
From: Ewok [mailto:[EMAIL PROTECTED]
Sent: Friday, May 20, 2005 10:40 PM
To: CF-Talk
Subject: RE: SQL Query Question
Use distinct and order descending by crid. In the future, I highly recommend
a unique key (PK) or at least a date/time field to decide
Ewok wrote:
How so with duplicates of both?
Because combined they are a unique identifier.
Jochem
~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours
having trouble with would be less trouble than it'
already not if they existed here.
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Saturday, May 21, 2005 2:41 PM
To: CF-Talk
Subject: Re: SQL Query Question
Ewok wrote:
How so with duplicates of both?
Because
Ewok wrote:
I guess that痴 one way of looking at it... I think creating the need to
combine them is more work than its worth or would ever need to be. Valid or
invalid, I still recommend something a little more easier to differentiate
records with such as a single unique key per record.
I
van Dieten [mailto:[EMAIL PROTECTED]
Sent: Saturday, May 21, 2005 6:27 PM
To: CF-Talk
Subject: Re: SQL Query Question
Ewok wrote:
I guess that痴 one way of looking at it... I think creating the need to
combine them is more work than its worth or would ever need to be. Valid
or
invalid, I still
If your not getting uptight... I'll drop it here anyway
because... well... pro-longed exposure to me tends to
make people that way :)
Droids don't tear peoples arms out of their sockets if they... oh
nevermind... :)
s. isaac dealey 954.522.6080
new epoch : isn't it time for a change?
add
After all, what hes having trouble with would be less
trouble than it' already not if they existed here.
I would expect in this case that having a primary key on the table he
described wouldn't change the sql syntax much (if at all). I still
like to have a primary key on these sorts of tables,
. Isaac Dealey [mailto:[EMAIL PROTECTED]
Sent: Saturday, May 21, 2005 8:04 PM
To: CF-Talk
Subject: RE: SQL Query Question
After all, what hes having trouble with would be less
trouble than it' already not if they existed here.
I would expect in this case that having a primary key on the table he
True, more would be helpful. But a unique identifier would
serve the purpose
of deciding which was entered last which I believed he
asked... but only he
could clarify that and since he's not responding with any
input... :)
you calling Jochem a wookie? hehe
Actually it was a mangled
Sorry for the off-topic, but I have been beating on this one for a while and
the only answer I can find is really, really ugly. So, hopefully somebody
knows more about queries that I do and can show me the error of my ways
I have a table that looks something like this
crID
select t.* from test t where t.actid in
(SELECT MAX(t2.actID) FROM test t2 where t2.crID = t.crID)
ought to work...
s. isaac dealey 954.522.6080
new epoch : isn't it time for a change?
add features without fixtures with
the onTap open source framework
http://www.fusiontap.com
Jeff Chastain wrote:
I have a table that looks something like this
crIDactIDdescriptionowner
1 1Test 444
1 2Test Update124
2 1Test 578
Now, what I need is a listing of all distinct
I knew there was any easier way to do this.
Thanks
-- Jeff
From: S. Isaac Dealey [EMAIL PROTECTED]
Sent: Friday, May 20, 2005 9:16 AM
To: CF-Talk cf-talk@houseoffusion.com
Subject: Re: OT: SQL Query Question
select t.* from test t where t.actid
this.
Thanks
-- Jeff
From: S. Isaac Dealey [EMAIL PROTECTED]
Sent: Friday, May 20, 2005 9:16 AM
To: CF-Talk cf-talk@houseoffusion.com
Subject: Re: OT: SQL Query Question
select t.* from test t where t.actid in
(SELECT MAX(t2.actID) FROM test t2
: SQL Query Question
Sorry for the off-topic, but I have been beating on this one for a while and
the only answer I can find is really, really ugly. So, hopefully somebody
knows more about queries that I do and can show me the error of my ways
I have a table that looks something like
We are using CF5.x and MSSQL server and I have a particular conundrum I am
hoping one of you SQL experts can assist with.
I have a record set like so: PN, V1, V2, V3...V15 Each V field contains a
numeric value for each record. So a short record set might look something
like:
PN, V1, V2, V3
A
, 2005 8:01 AM
...To: CF-Talk
...Subject: SQL Query Question
...
...We are using CF5.x and MSSQL server and I have a particular conundrum I
...am
...hoping one of you SQL experts can assist with.
...
...I have a record set like so: PN, V1, V2, V3...V15 Each V field
...contains a
...numeric value
Ian,
Thanks!!! That put me on the right track. I had never used a Union before
and didn't even think about it. The resultant query looks like:
SELECT DISTINCT v1 AS V_values
FROM table
UNION
SELECT DISTINCT v2 AS V_values
FROM table
UNION
Say I have two simple queries:
cfquery name=getCustsFromCusts datasource=#DSN#
SELECT CustID
FROMCustomers
/cfquery
[and that query returns 700 records.]
cfquery name=getCustsFromOrders datasource=#DSN#
SELECT CustID
FROMOrders
/cfquery
[and that query returns 500 records.]
The first
Multiple ways heres one:
SELECT CustID
FROMCustomers
WHERE CustID NOT IN (Select CustID From Orders)
Adam
On Tue, 30 Nov 2004 12:01:30 -0500, Che Vilnonis [EMAIL PROTECTED] wrote:
Say I have two simple queries:
cfquery name=getCustsFromCusts datasource=#DSN#
SELECT CustID
FROM
How about this?
Select CustID from customers where custID not in (select custid from
orders)
-dov
-Original Message-
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 30, 2004 12:01 PM
To: CF-Talk
Subject: SQL Query question. Please help...
Say I have two simple
Sometimes using NOT can have performance issues
You can also do:
SELECT a.CustID
FROMCustomers a,Orders b
WHERE a.CustID = b.CustID
-Original Message-
From: Adam Haskell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 30, 2004 11:06 AM
To: CF-Talk
Subject: Re: SQL Query question
, November 30, 2004 11:05 AM
To: CF-Talk
Subject: RE: SQL Query question. Please help...
Sometimes using NOT can have performance issues
You can also do:
SELECT a.CustID
FROMCustomers a,Orders b
WHERE a.CustID = b.CustID
-Original Message-
From: Adam Haskell [mailto:[EMAIL PROTECTED]
Sent
-Original Message-
From: Adam Haskell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 30, 2004 11:06 AM
To: CF-Talk
Subject: Re: SQL Query question. Please help...
Multiple ways heres one:
SELECT CustID
FROMCustomers
WHERE CustID NOT IN (Select CustID From Orders)
Adam
On Tue, 30
You can also do:
SELECT a.CustID
FROMCustomers a,Orders b
WHERE a.CustID = b.CustID
-Original Message-
From: Adam Haskell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 30, 2004 11:06 AM
To: CF-Talk
Subject: Re: SQL Query question. Please help...
Multiple ways
Adam/Dov
Using your query my recordcount is off.
Actual # of customer ids from my customers table = 865.
Actual # of customer ids from my orders table = 596.
Using your query, the difference = 335.
865 - 596 should = 269. What am I missing? Debug info is below.
Under your math you are assuming a 1 to 1 relationship between orders
and Custromers. Unless you are running a scam, where no customer would
ever by from you twice, that is not case, the whole reason you have 2
seperate tables. Hope thats makes sense.
Adam H
On Tue, 30 Nov 2004 12:18:23 -0500,
You have multiple Orders for some customers.
What is count for:
SELECT DISTINCT Custid from Orders
-Original Message-
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 30, 2004 12:18 PM
To: CF-Talk
Subject: RE: SQL Query question. Please help...
Adam/Dov
I see. Haven't had my lunch yet. Thanks for your help.
Che
-Original Message-
From: Adam Haskell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 30, 2004 12:23 PM
To: CF-Talk
Subject: Re: SQL Query question. Please help...
Under your math you are assuming a 1 to 1 relationship
At 12:22 PM 11/30/2004, Adam Haskell wrote:
Under your math you are assuming a 1 to 1 relationship between orders
and Custromers. Unless you are running a scam, where no customer would
ever by from you twice, that is not case, the whole reason you have 2
seperate tables. Hope thats makes sense.
Hello,
I'm working on a project where records will be added to a single table, and
each record will be assigned to one of six categories (using a category
column). Is there a way I can do a single query to pull the most recent
record from each of the six categories? Or is my only option to do
AND
a.update_date = max_dates.update_date
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 27, 2003 3:06 PM
To: CF-Talk
Subject: Advanced SQL Query Question
Hello,
I'm working on a project where records will be added to a single table
Hello,
I'm working on a project where records will be added to a single
table, and each record will be assigned to one of six categories
(using a category column). Is there a way I can do a single query to
pull the most recent record from each of the six categories? Or is
my only option to
-Original Message-
From: Marlon Moyer [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 27, 2003 1:45 PM
To: CF-Talk
Subject: RE: Advanced SQL Query Question
Jonathan,
Try this out:
**
Untested code!!!
**
SELECT *
FROM
tblWhatever a
INNER JOIN
(
SELECT
Hi everyone,
Okay, I've been struggling with this one for a while, and I'm sure there's a
solution, but I'm just not seeing it.
I've got a collection of tables, among which are document, and attrib_xref.
In the attrib_xref table, there's (among others) the following columns:
DocID
AttribID
]]
Sent: Wednesday, May 29, 2002 2:02 PM
To: CF-Talk
Subject: SQL query question
Hi everyone,
Okay, I've been struggling with this one for a while, and I'm sure there's a
solution, but I'm just not seeing it.
I've got a collection of tables, among which are document, and attrib_xref
of Medicine Public Health
Voice (614) 292-5960
Fax (614) 292-0745
-Original Message-
From: Dirk Sieber [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 29, 2002 2:02 PM
To: CF-Talk
Subject: SQL query question
Hi everyone,
Okay, I've been struggling with this one for a while, and I'm sure
Health
Voice (614) 292-5960
Fax (614) 292-0745
-Original Message-
From: Dirk Sieber [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 29, 2002 2:02 PM
To: CF-Talk
Subject: SQL query question
Hi everyone,
Okay, I've been struggling with this one for a while, and I'm sure there's
So, this is where I start thinking that maybe there isn't that elusive easy
solution after all... ;-)
Thanks for your suggestions... I'm going to start playing around with them
now, and see what I can come up with.
Dirk
__
I'm having trouble with this query.
There are two tables, issue and issueOption.
There is a one-to-many relationship between issue (one) and issueOption
(many). issID is the key that joins the two tables.
I want to select all records from issue that are not in the issueOption. In
other
Query Question
I'm having trouble with this query.
There are two tables, issue and issueOption.
There is a one-to-many relationship between issue (one) and issueOption
(many). issID is the key that joins the two tables.
I want to select all records from issue that are not in the issueOption
I'm having trouble with this query.
There are two tables, issue and issueOption.
There is a one-to-many relationship between issue (one) and
issueOption (many). issID is the key that joins the two tables.
I want to select all records from issue that are not in the
issueOption. In other
I've done a lot in CF but this silly little problem just stumped me at the
moment. If anyone would be kind enough to straighten me out on this I'd
much appreciate it!
I have a database of products. I need to submit a value ( a pricerange) to
the query that will return only the items in that
Britta,
You'll have to do some processing on PriceRange to turn it into:
(value x) AND (value y),
or into:
value BETWEEN x and y
You can't just plug "1 50" into it. What does "1 50" mean, anyway?? ;-)
Lee (Bjork) Borkman
http://bjork.net ColdFusion Tags by Bjork
-Original
I have the following SP:
CREATE PROCEDURE [n_speaker_list] AS
SELECT DISTINCT s.f_name, s.l_name, s.pro_title, c.co_name, sm.subject,
a.agenda_title, s.sp_file
FROM speakers s, company c, subject_matter sm, agenda a
WHERE a.time_slot = s.time_slot
AND s.subject_id = sm.subject_id
AND
58 matches
Mail list logo
