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:341556
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
