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!
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
My impression from his description was that the actID was a sequential
number associated with the crID so in his case he would always know
that there would be only one record with a given actID for any given
crID which is a foreign key to another table -- and as he wanted all
data on that row,
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
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
-Original Message-
From: Jeff Chastain [mailto:[EMAIL PROTECTED]
Sent: Friday, May 20, 2005 9:59 AM
To: CF-Talk
Subject: OT:
Others will probably come up with a better way, but my first thought was a
series of unions.
SELECT DISTINCT values
FROM
(
SELECT DISTINCT v1 AS values
FROM table
UNION
SELECT DISTINCT v2 AS values
FROM table
UNION
SELECT
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
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.
All I can quickly come up with would be to use self-joins for every
attribute/value pair.
select A.DocId from
attrib_xref A inner join attrib_xref B on A.DocID = B.DocID
where A.attribId = 12
and B.attribId = 24
and A.attribValue = 'some text'
and B.attribValue = 'some other text'
...
You'll
perhaps something like this
I'm sure there is a better way but this may be a start.
HTH
Select AttribValue
From MyTable
Where 0 = 0
cfif isdefined('attributes.AttribID') and isdefined('attributes.DocID')
And (
cfset lc = 0
cfloop list=#attributes.AttribID# index='x'
cfloop
let's try again
Select DocID,AttribID,AttribValue
From attrib_xref
Where 0 = 0
cfif isdefined('attributes.AttribID') and isdefined('attributes.DocID')
And (
cfset xc = 0
cfset yc = 0
cfloop list=#attributes.DocID# index='x'
cfset yc = 0
cfset xc = #xc#+1
cfif xc eq 1
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
__
Something like this might work:
SELECT DISTINCT IssID
FROM Issue
WHERE NOT EXISTS
(SELECT *
FROM issueOption
WHERE issueOption.issID = Issue.issID)
-Original Message-
From: Nick Bourgeois [mailto:[EMAIL PROTECTED]]
Sent: Sunday, May 19, 2002 11:32 AM
To: CF-Talk
Subject: SQL
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
41 matches
Mail list logo
