Hi
we have a relational database and one task is taking far too long but we cannot
see any way to improve it, although i do feel there is a way to massively
improve it... so would like some expert help on this
we have a normal table which has a One-to-Many table coming off of it...
table 1
What DB are you using? If it's MySQL, you can use this:
select client.name, group_concat(clientphone.number) as phone numbers
from client
left outer join clientphone on client.id = clientphone.clientId
group by client.name
If it's MS SQL Server you can use a subquery with FOR XML PATH and
The easiest way is use a JOIN to get a query that will have a phone number
on each row, then use the 'group' attribute of cfoutput to display them
correctly.
On Tue, Apr 14, 2009 at 3:38 PM, Richard White rich...@j7is.co.uk wrote:
Hi
we have a relational database and one task is taking far
Have you looked into StoredProcs? Push the load off the web server onto the
SQL Server (or Oracle whichever)
On Tue, Apr 14, 2009 at 3:38 PM, Richard White rich...@j7is.co.uk wrote:
Hi
we have a relational database and one task is taking far too long but we
cannot see any way to improve
There isn't really an efficient way to get a comma separated list in
one column with your DB query. However, there is a far more efficient
method that uses a single query and groups the output:
cfquery name=qryClients datasource=myDsn
SELECT c.clientId, c.firstname, c.lastname, t.number
FROM
thanks for all your replies.
barney, yes we are using mysql and didnt even know about the qroup_concat. it
works like a dream and where this task was taking 23 seconds to complete it is
now taking a matter of milliseconds :)
fantastic and thanks once again for all your replies :)
What DB
Nice. So group_concat works sort of like ColdFusion's valuelist method. Very
nice indeed!
-Original Message-
From: Richard White [mailto:rich...@j7is.co.uk]
Sent: Tuesday, April 14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks for all your replies.
barney, yes
: Tuesday, April 14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks for all your replies.
barney, yes we are using mysql and didnt even know about the qroup_concat.
it works like a dream and where this task was taking 23 seconds to complete
it is now taking a matter
valuelist method.
Very
nice indeed!
-Original Message-
From: Richard White [mailto:rich...@j7is.co.uk]
Sent: Tuesday, April 14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks for all your replies.
barney, yes we are using mysql and didnt even know about
wrote:
Nice. So group_concat works sort of like ColdFusion's valuelist method. Very
nice indeed!
-Original Message-
From: Richard White [mailto:rich...@j7is.co.uk]
Sent: Tuesday, April 14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks for all your replies
14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks for all your replies.
barney, yes we are using mysql and didnt even know about the qroup_concat.
it works like a dream and where this task was taking 23 seconds to complete
it is now taking a matter of milliseconds
...@commadelimited.com
wrote:
Nice. So group_concat works sort of like ColdFusion's valuelist method.
Very
nice indeed!
-Original Message-
From: Richard White [mailto:rich...@j7is.co.uk]
Sent: Tuesday, April 14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks
group_concat works sort of like ColdFusion's valuelist method.
Very
nice indeed!
-Original Message-
From: Richard White [mailto:rich...@j7is.co.uk]
Sent: Tuesday, April 14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks for all your replies.
barney, yes we are using
Is there a way in MSSQL and Oracle or either that will allow you to do a select
on a varchar field and be able to see if that record can be converted to a
number? We have a ton of databases that used varchar to handle numbers and are
getting issues now that some records have gotten data other
Robert Everland III wrote:
Is there a way in MSSQL and Oracle or either that will allow you to do a
select on a varchar field and be able to see if that record can be converted
to a number? We have a ton of databases that used varchar to handle numbers
and are getting issues now that some
In sql server if you want to find the records that aren't numeric you
can use:
Select *
From your_table
Where isNumeric(fieldToCheck) = 0
-Original Message-
From: Robert Everland III [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 09, 2006 2:52 PM
To: CF-Talk
Subject: SQL Challenge
, 2006 1:52 PM
To: CF-Talk
Subject: SQL Challenge
Is there a way in MSSQL and Oracle or either that will allow you to do a
select on a varchar field and be able to see if that record can be
converted to a number? We have a ton of databases that used varchar to
handle numbers and are getting issues now
In MS-SQL, there is a isNumeric function...it may get you most of the
way there, but also I will tell you that sometimes it returns true for
things that won't convert...case in point...throw this in query
analyzer
select isnumeric('1,1.1') 'returns true
select cast('1,1.1' as numeric)
In Oracle I this function:
CREATE OR REPLACE function is_number
( p_string in varchar2 )
return number
deterministic
as
l_num number;
begin
l_num := p_string;
return 1;
exception
when others then
return null;
end;
/
On 2/9/06, Jim Wright [EMAIL PROTECTED] wrote:
In MS-SQL,
-Original Message-
From: Robert Everland III [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 09, 2006 2:52 PM
To: CF-Talk
Subject: SQL Challenge
Is there a way in MSSQL and Oracle or either that will allow you to do a
select on a varchar field and be able to see if that record
I've got a challenge for any and all SQL guru's.I need to return a record set consisting of all records with a date in the future of today as well as one and only one record previous to the current date, if one exists.
Would this best be done with a union of two selects, or is there a trick that
Ok here is the challenge. Surprise is at the end.
I am trying to put together a report on our member's membership periods. This report needs to identify the entire member's membership history. Memberships are based on an annual subscription. However, there are sometime breaks in one's membership
-Talk
Subject: SQL Challenge
Ok here is the challenge. Surprise is at the end.
I am trying to put together a report on our member's membership periods.
This report needs to identify the entire member's membership history.
Memberships are based on an annual subscription. However, there are sometime
Made a mistake for the report output
MEMBER A07/1/200107/31/2004
MEMBER B08/1/200308/31/2004
MEMBER B07/1/200107/31/2003
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
PM
To: CF-Talk
Subject: RE: SQL Challenge
Made a mistake for the report output
MEMBER A07/1/200107/31/2004
MEMBER B08/1/200308/31/2004
MEMBER B07/1/200107/31/2003
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
-
From: DURETTE, STEVEN J (AIT) [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 06, 2003 3:11 PM
To: CF-Talk
Subject: RE: SQL Challenge
Eric,
Your example for Member B doesn't make sense to me.
According to the dates you gave, there was no lapse, he just got a few extra
months.
Can you
Has to be in SQL. Will need to put it in a Stored Proc at some point.
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
What kind of database are we dealing with since date functions vary from DB
to DB?
-Original Message-
From: Eric Creese [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 06, 2003 2:41 PM
To: CF-Talk
Subject: SQL Challenge
Ok here is the challenge. Surprise is at the end.
I am trying
Send the prize first pelase :-)
-Original Message-
From: Eric Creese [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 06, 2003 3:41 PM
To: CF-Talk
Subject: SQL Challenge
Ok here is the challenge. Surprise is at the end.
I am trying to put together a report on our member's membership
4:26 PM
To: CF-Talk
Subject: RE: SQL Challenge
Has to be in SQL. Will need to put it in a Stored Proc at some point.
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
30 matches
Mail list logo
