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!
http://www.amaz
ates for the evening, 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 c
>
> 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 yo
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 setting
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.
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
-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Sunday, 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
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
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
> 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
om: S. 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
> 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 table
> 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?
a
e that way :)
-Original Message-
From: Jochem 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 it
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 t
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
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 spe
##actID# #description#
#owner#
-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
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
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
~~
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, sele
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:
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
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 DISTINC
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.
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 relatio
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
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,
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.
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: C
HERE 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 heres one:
>
> SELECT CustID
> FRO
, 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
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
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 q
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:
>
>
> SELECT CustID
> FROMCustomers
>
>
> [and that query returns
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
__
Your
let's try again
Select DocID,AttribID,AttribValue
>From attrib_xref
Where 0 = 0
And (
(
(attrib_xref.DocID = #x# and attrib_xref.AttribID = #y#)
OR (attrib_xref.D
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
And (
(MyTable.AttribID = #x# and MyTable.DocID = #y#)
OR (dbo.personnel_appointment.department_id = #x#)
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 hav
> 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
> issueOptio
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 Que
41 matches
Mail list logo
