OOps I meant to mention this is a MS SQL 2000 DB. On 1/27/2011 1:33 PM, Kelly Matthews wrote: > Ok so I haven't really written any code in tSQL yet that does major looping > like I'm doing in CF. Figured I'd post this here and see if anyone had some > input on how to convert this into tSQL so it could be run w/ in a DTS package. > > Basically we have products, top-level categories, sub-categories, > sub-sub-categories, etc. The purpose of this script is to find any sub, or > sub sub etc. categories that have no products, and mark that sub-category as > having no products. The script works great but I'd love to pull it out of CF. > Anyone care to take a stab or lead me in the right direction. IN the > meantime I shall google some SQL looping examples and see what I find. > > > <cfquery datasource="#application.ds#" name="getcats"> > select > distinct a.category, a.parent_id > from > products b > inner join > cfx_prod_custCat x > on > b.id = x.prod_id > inner join > categories a on a.category = x.custcat_id > > where > a.main_id is not null > > UNION > > select > distinct a.category, a.parent_id > from > categories a > inner join > products2 b > on > a.category = b.category > Where > a.main_id is not null > and b.deleted = 0 > </cfquery> > > <cfloop query="getcats"> > <cfquery datasource="#application.ds#" name="update"> > update categories > set hasproducts =<cfqueryparam value="1" > cfsqltype="CF_SQL_INTEGER" /> > where category =<cfqueryparam value="#getcats.category#" > cfsqltype="CF_SQL_INTEGER" /> > <cfif getcats.parent_id neq 0> > OR category =<cfqueryparam value="#getcats.parent_id#" > cfsqltype="CF_SQL_INTEGER" /> > </cfif> > </cfquery> > <cfif getcats.parent_id neq 0> > <cfquery datasource="#application.ds#" name="getparent"> > select parent_id > from categories > where category =<cfqueryparam > value="#getcats.parent_id#" cfsqltype="CF_SQL_INTEGER" /> > </cfquery> > <cfif getparent.parent_id neq 0> > <cfquery datasource="#application.ds#" name="update"> > update categories > set hasproducts =<cfqueryparam value="1" > cfsqltype="CF_SQL_INTEGER" /> > where category =<cfqueryparam > value="#getparent.parent_id#" cfsqltype="CF_SQL_INTEGER" /> > </cfquery> > <cfquery datasource="#application.ds#" > name="getparent2"> > select parent_id > from categories > where category =<cfqueryparam > value="#getparent.parent_id#" cfsqltype="CF_SQL_INTEGER" /> > </cfquery> > <cfif getparent2.parent_id neq 0> > <cfquery datasource="#application.ds#" > name="update"> > update categories > set hasproducts =<cfqueryparam > value="1" cfsqltype="CF_SQL_INTEGER" /> > where category =<cfqueryparam > value="#getparent2.parent_id#" cfsqltype="CF_SQL_INTEGER" /> > </cfquery> > <cfquery datasource="#application.ds#" > name="getparent3"> > select parent_id > from categories > where category =<cfqueryparam > value="#getparent2.parent_id#" cfsqltype="CF_SQL_INTEGER" /> > </cfquery> > <cfif getparent3.parent_id neq 0> > <cfquery datasource="#application.ds#" > name="update"> > update categories > set hasproducts =<cfqueryparam > value="1" cfsqltype="CF_SQL_INTEGER" /> > where category =<cfqueryparam > value="#getparent3.parent_id#" cfsqltype="CF_SQL_INTEGER" /> > </cfquery> > <cfquery > datasource="#application.ds#" name="getparent4"> > select parent_id > from categories > where category > =<cfqueryparam value="#getparent3.parent_id#" cfsqltype="CF_SQL_INTEGER" /> > </cfquery> > <cfif getparent4.parent_id neq > 0> > <cfquery > datasource="#application.ds#" name="update"> > update > categories > set hasproducts > =<cfqueryparam value="1" cfsqltype="CF_SQL_INTEGER" /> > where category > =<cfqueryparam value="#getparent4.parent_id#" cfsqltype="CF_SQL_INTEGER" /> > </cfquery> > <cfquery > datasource="#application.ds#" name="getparent5"> > select parent_id > from categories > where category > =<cfqueryparam value="#getparent5.parent_id#" cfsqltype="CF_SQL_INTEGER" /> > </cfquery> > <cfquery > datasource="#application.ds#" name="update"> > update > categories > set hasproducts > =<cfqueryparam value="1" cfsqltype="CF_SQL_INTEGER" /> > where category > =<cfqueryparam value="#getparent5.parent_id#" cfsqltype="CF_SQL_INTEGER" /> > </cfquery> > </cfif> > </cfif> > </cfif> > </cfif> > </cfif> > </cfloop> > >
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341558 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
