[thesite] friday is a good day..

rudy r937 at interlog.com
Sat May 19 08:53:05 CDT 2001


> i think we can minimize the use of that subselect to only
> those instances where the user is logged in.
>
> <cfif Val(session.user.id)>
>         (SELECT Count(*)
>            FROM replies
>           WHERE contentid = content.contentid
>             AND datemod > '#DateFormat(session.user.lastlogin)#'
>         ) AS newcomments
> <cfelse>
>         (SELECT '0'
>            FROM replies
>         ) AS newcomments
> </cfif>

hi jeff

excellent idea!!

dean, did you put an index on replies.datemod?  i think that would really
help this subquery


>(aside: can i do a select without specifying a table?)

no

you can select from DUAL as matt suggested

however, for the query above, i think what you are really looking for is
this --

  <cfelse>
       0 AS newcomments
  </cfif>

(o;

>:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>: does oracle support anything like microsoft's
>: SELECT TOP or mysql's LIMIT?
>:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>good question, but that won't help us out in this situation as we need to
>generate the entire resultset in order to page through since coldfusion
>needs to have the entire resultset for startrow and maxrows attributes of
>the cfoutput to work properly.

thank you, thank you

this has finally put to rest a question that's been bugging me for a long
time

paging through a result set (next/previous 10 records, et cetera) is done
entirely in cold fusion and the database is not re-queried

that's exactly the way i had hoped it was done

thanks jeff, you da man





More information about the thesite mailing list