> Hi
> all
>  
> Hope everyone's
> looking forward to the weekend. I certainly am!
>  
> I asked a few weeks
> ago if anyone had seen a coldfusion database query tool. I found
a
> plugin for ExtJS which basically creates the WHERE statement from the
choices
> you make (CherryExt). I've ported the backend code to Coldfusion but am
having a
> few problems with the SQL. Some the SQL has subqueries for counting rows
etc. If
> I pass in a SQL filter of "materialsent = 1" into the first query it
> obviously fails, so I got around it by then querying a query. It means I
have to
> load the whole recordset in the first query (very slow with large
recordsets)
> and also qoq do not allow me to use the limit command. Does anyone have
any
> suggestions of how I could do this better or point me in the right
direction,
> SQL isn't my strongest skill!
>  
> <cfquery name="returnData"
> datasource="#application.settings.datasource#"> SELECT
> tbl_organisations.id, tbl_organisations.name,
> tbl_organisations.datecreated,  (SELECT
> COUNT(*)        FROM
> lnk_materials        WHERE
> tbl_organisations.id = lnk_materials.orgId) AS materialsent FROM
> tbl_organisations</cfquery>
>  
> <cfquery name="returnData"
> dbtype="query"> SELECT * FROM returnData WHERE
> #getSQL(filters)#</cfquery>
>  
> Kind
> regards
> Shaun Perry
> Wiredmonkey +44 (0)131 2080811+44 (0)7921

> This e-mail is confidential and should not be used by
> anyone who is not the original intended recipient. If you have received
this
> e-mail in error please inform the sender and delete it from your mailbox
or any
> other storage mechanism. Wiredmonkey cannot accept liability for any
statements
> made which may be the sender's own and not expressly made on behalf of
> Wiredmonkey. Electronic mail is not a secure form of communication and can
be
> subject to interference.
>  

On Behalf Of Gareth.cole
Sent: 16 October 2009 13:21
To: scottishcfug@googlegroups.com
Subject: [SCFUG] Re: Pure SQL or dtype="query" anyone?

Hi Shaun,

You might want to try re-writing the first query using GROUP BY to get the
aggregate information. Some of the filters would go in the WHERE statement,
and some in a HAVING statement.
Been a while since I've used HAVING, so I'm not 100% sure this will work

--------- Original Message --------
From: scottishcfug@googlegroups.com
To: scottishcfug@googlegroups.com <scottishcfug@googlegroups.com>
Subject: [SCFUG] Pure SQL or dtype="query" anyone?
Date: Oct 16, 2009 02:12 PM

> Hi
> all
> &nbsp;
> Hope everyone's
> looking forward to the weekend. I certainly am!
> &nbsp;
> I asked a few weeks
> ago&nbsp;if anyone had seen a coldfusion&nbsp;database query tool. I
> found
a
> plugin for ExtJS which basically creates the WHERE statement from the
choices
> you make (CherryExt). I've ported the backend code to Coldfusion but
> am
having a
> few problems with the SQL. Some the SQL has subqueries for counting
> rows
etc. If
> I pass in a SQL&nbsp;filter of "materialsent = 1" into the first query
> it obviously fails, so I got around it by then querying a query. It
> means I
have to
> load the whole recordset in the first query (very slow with large
recordsets)
> and also qoq do not allow me to use the limit command. Does anyone
> have
any
> suggestions of how I could do this better or point me in the right
direction,
> SQL isn't my strongest skill!
> &nbsp;
> &lt;cfquery name="returnData"
> datasource="#application.settings.datasource#"&gt;&nbsp;SELECT
> tbl_organisations.id, tbl_organisations.name,
> tbl_organisations.datecreated,&nbsp;&nbsp;(SELECT
> COUNT(*)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM
> lnk_materials&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE
> tbl_organisations.id = lnk_materials.orgId) AS materialsent&nbsp;FROM
> tbl_organisations&lt;/cfquery&gt; &nbsp; &lt;cfquery name="returnData"
> dbtype="query"&gt;&nbsp;SELECT *&nbsp;FROM returnData&nbsp;WHERE
> #getSQL(filters)#&lt;/cfquery&gt; &nbsp; Kind regards Shaun Perry
> Wiredmonkey +44 (0)131 2080811+44 (0)7921
121021www.wiredmonkey.co.ukwww.wiredmonkey.co.uk/blog

> This e-mail is confidential and should not be used by anyone who is
> not the original intended recipient. If you have received
this
> e-mail in error please inform the sender and delete it from your
> mailbox
or any
> other storage mechanism. Wiredmonkey cannot accept liability for any
statements
> made which may be the sender's own and not expressly made on behalf of
> Wiredmonkey. Electronic mail is not a secure form of communication and
> can
be
> subject to interference.
> &nbsp;

Sent to you using Uebimiau Webmail version 3.11 Developed by Dave and Todd
at http://www.manvel.net and http://www.tdah.us