Re: How would I do this in SQL?
It can go up to 5 levels deep, no more than that. Re: the UDF, that's a good idea, I'll take a look at the link, thanks! > Sorry that I answer didn't take inheritance into account. Is there a > maximum number of levels of subcategories, or will it always be an > unknown? I have done looping with tSQL in the past using cursors, and > it really is a pain. > > You could use a UDF (User Defined Function) and return a table > variable. Here is a link for an example of returning a table > variable: http://sqlt.tripod.com/recursivity.htm > > You could probably modify this example to return a bit value if the > category or any parent has a product assigned. In theory if you write > the function correctly you could write a single update statement that > sets the hasproducts column equal to the funtion value passing in the > category as a parameter. > > Something like > UPDATE categories set hasprodutcs = fnHasProduct(category) > > where fnHasProduct id the name of your UDF and accepts the category as > a parameter. > > The real work would be in writing your UDF. > > You would want you to thoroughly test your UDF, but it could provide > you with a powerful and simple execution that even incorporates a > little OOP using tSQL. ~| 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:341571 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: How would I do this in SQL?
Sorry that I answer didn't take inheritance into account. Is there a maximum number of levels of subcategories, or will it always be an unknown? I have done looping with tSQL in the past using cursors, and it really is a pain. You could use a UDF (User Defined Function) and return a table variable. Here is a link for an example of returning a table variable: http://sqlt.tripod.com/recursivity.htm You could probably modify this example to return a bit value if the category or any parent has a product assigned. In theory if you write the function correctly you could write a single update statement that sets the hasproducts column equal to the funtion value passing in the category as a parameter. Something like UPDATE categories set hasprodutcs = fnHasProduct(category) where fnHasProduct id the name of your UDF and accepts the category as a parameter. The real work would be in writing your UDF. You would want you to thoroughly test your UDF, but it could provide you with a powerful and simple execution that even incorporates a little OOP using tSQL. ~| 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:341569 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: How would I do this in SQL?
Ditto on the repost, not sure if my first reply posted... Actually it's much more than that. There are a ton of categories that won't have products, however, if they are a parent of a sub category that has a product, they ultimately have products as well. This is why it requires nested queries about 5 levels deep in coldfusion. Did you happen to scroll down enough to see the plethora of Sorry if this is a repost. I am not sure it posted the first time. > > I do not think any looping is required. You should be able to do this > using sub queries within the where clause. > > If you are using cfx_prod_custCat as a lookup table simply look for > any category that is not listed in the custcat_id field: > > UPDATE Categories SET hasproducts = 0 WHERE category IN > (SELECT DISTINCT category FROM Categories WHERE ID NOT IN > (SELECT DISTINCT custcat_id FROM cfx_prod_custCat)) > > You use the same logic for the 2nd part of the UNION clause: > > UPDATE Categories SET hasproducts = 0 WHERE category IN > (SELECT DISTINCT category FROM Categories WHERE category NOT IN > (SELECT DISTINCT category FROM products)) > > > Basically you use the sub query to pull a list of all products that > have a category assigned and the look for categories that are not in > that list. > > Hopefully I am not way off on this and it helps point you in the right > direction. > > Brian Cain ~| 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:341567 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: How would I do this in SQL?
I forgot to mention that this would be MS SQL 2000 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. > > ~| 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:341563 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: How would I do this in SQL?
I do not think any looping is required. You should be able to do this using sub queries within the where clause. If you are using cfx_prod_custCat as a lookup table simply look for any category that is not listed in the custcat_id field: UPDATE Categories SET hasproducts = 0 WHERE category IN (SELECT DISTINCT category FROM Categories WHERE ID NOT IN (SELECT DISTINCT custcat_id FROM cfx_prod_custCat)) You use the same logic for the 2nd part of the UNION clause: UPDATE Categories SET hasproducts = 0 WHERE category IN (SELECT DISTINCT category FROM Categories WHERE category NOT IN (SELECT DISTINCT category FROM products)) Basically you use the sub query to pull a list of all products that have a category assigned and the look for categories that are not in that list. Hopefully I am not way off on this and it helps point you in the right direction. Brian Cain ~| 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:341562 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: How would I do this in SQL?
Sorry if this is a repost. I am not sure it posted the first time. I do not think any looping is required. You should be able to do this using sub queries within the where clause. If you are using cfx_prod_custCat as a lookup table simply look for any category that is not listed in the custcat_id field: UPDATE Categories SET hasproducts = 0 WHERE category IN (SELECT DISTINCT category FROM Categories WHERE ID NOT IN (SELECT DISTINCT custcat_id FROM cfx_prod_custCat)) You use the same logic for the 2nd part of the UNION clause: UPDATE Categories SET hasproducts = 0 WHERE category IN (SELECT DISTINCT category FROM Categories WHERE category NOT IN (SELECT DISTINCT category FROM products)) Basically you use the sub query to pull a list of all products that have a category assigned and the look for categories that are not in that list. Hopefully I am not way off on this and it helps point you in the right direction. Brian Cain ~| 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:341561 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: How would I do this in SQL?
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. > > > > 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 > > > > > update categories > set hasproducts = cfsqltype="CF_SQL_INTEGER" /> > where category = cfsqltype="CF_SQL_INTEGER" /> > > OR category = cfsqltype="CF_SQL_INTEGER" /> > > > > > select parent_id > from categories > where category = value="#getcats.parent_id#" cfsqltype="CF_SQL_INTEGER" /> > > > > update categories > set hasproducts = cfsqltype="CF_SQL_INTEGER" /> > where category = value="#getparent.parent_id#" cfsqltype="CF_SQL_INTEGER" /> > >name="getparent2"> > select parent_id > from categories > where category = value="#getparent.parent_id#" cfsqltype="CF_SQL_INTEGER" /> > > >name="update"> > update categories > set hasproducts = value="1" cfsqltype="CF_SQL_INTEGER" /> > where category = value="#getparent2.parent_id#" cfsqltype="CF_SQL_INTEGER" /> > >name="getparent3"> > select parent_id > from categories > where category = value="#getparent2.parent_id#" cfsqltype="CF_SQL_INTEGER" /> > > >name="update"> > update categories > set hasproducts = value="1" cfsqltype="CF_SQL_INTEGER" /> > where category = value="#getparent3.parent_id#" cfsqltype="CF_SQL_INTEGER" /> > >datasource="#application.ds#" name="getparent4"> > select parent_id > from categories > where category > = > >0> >datasource="#application.ds#" name="update"> > update > categories > set hasproducts > = > where category > = > >datasource="#application.ds#" name="getparent5"> > select parent_id > from categories > where category > = > >
RE: How would I do this?
IFRAMES. Pages loaded in IFrames are loaded asynchronously from the main page. If you have three jobs, you might create a main page with three IFRAMES. Set the src of the IFrames to a page which will do one specific task (I'll call this a sub-page for now). The sub-page can use _javascript_ to update the parent as needed. So, if you had a table that indicated the state of each job, you can set the initial state when the page loads, and call the appropriate action page in the iframe(s). Then when a task/sub-page is complete, get a reference to the parent page element and change it's value accordingly. I've used IFrames for this type of behavior for some time now, and find it very handy. There are a few gotcha's of course, but overall it's simple enough to put in place. I guess the only real catch is if your target browser doesn't support IFrames. HTH Shawn -Original Message- From: Troy Simpson [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 18, 2004 4:36 PM To: CF-Talk Subject: Re: How would I do this? Oh, one other thing. How could I submit several requests to various back-end systems (RDBMS, IMAP, HTTP, etc. ) simultaneously. For example, I want to execute the following request simultaneously. All requests are independent of others. So I do not have to wait for one come back before processing the next on. 1. query on RDBMS_1, 2. run another query on RDBMS_2, 3. request an http page from a web server, 4. query an LDAP server. It does not matter which process comes back first/last. It does not matter if one of them fails. I did not want to execute them procedurally. If they do excute procedurally, then I would have to wait for a prior request to complete before moving own to the next request. Can ColdFusion do this? Thanks, Troy Troy Simpson wrote: > Hello all, > > I am using ColdFusionMX on Solaris8/9 > > I need to query some resources that do not respond in a timely manner > and as a fix, I can not cache the data. > > So what I would like to do is submit the request(queryies) to the > various backend systems, then respond to the user with something like > > "Preparing results. One moment please..." > > Then I would like to auto-submit the page about every 1 to 2 seconds to > determine what responses have been fullfilled and update the client with > a status of which requests have been completed and which requests are > still working. > > Is this possible with CFMX? > > I would presume this would require some multi-threading, etc. > > Thanks for you help. > > -- > Troy Simpson > Applications Analyst/Programmer, OCPDBA, MCSE, SCSA > North Carolina State University Libraries > Campus Box 7111 | Raleigh | North Carolina > ph.919.515.3855 | fax.919.513.3330 > E-mail: [EMAIL PROTECTED] > _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: How would I do this?
Not in a single request. CF is multithreaded, but only at the request level. Each request gets a single thread for all it's execution. Certain tags circumvent this (such as by specifying a timeout of zero on CFEXECUTE), but they're really severing themselves from the request thread, not just executing simultaneously. If you really want to multithread a request, I think you'll have to drop down into Java and do it that way. Of course, you'll lose all the simplicity of CF if you do that. Cheers, barneyb > -Original Message- > From: Troy Simpson [mailto:[EMAIL PROTECTED] > Sent: Tuesday, May 18, 2004 3:36 PM > To: CF-Talk > Subject: Re: How would I do this? > > Oh, one other thing. > > How could I submit several requests to various back-end > systems (RDBMS, > IMAP, HTTP, etc. ) simultaneously. > > For example, I want to execute the following request simultaneously. > All requests are independent of others. So I do not have to wait for > one come back before processing the next on. > 1. query on RDBMS_1, > 2. run another query on RDBMS_2, > 3. request an http page from a web server, > 4. query an LDAP server. > > It does not matter which process comes back first/last. It does not > matter if one of them fails. > > I did not want to execute them procedurally. If they do excute > procedurally, then I would have to wait for a prior request > to complete > before moving own to the next request. > > Can ColdFusion do this? > > Thanks, > Troy > > Troy Simpson wrote: > > Hello all, > > > > I am using ColdFusionMX on Solaris8/9 > > > > I need to query some resources that do not respond in a > timely manner > > and as a fix, I can not cache the data. > > > > So what I would like to do is submit the request(queryies) to the > > various backend systems, then respond to the user with > something like > > > > "Preparing results. One moment please..." > > > > Then I would like to auto-submit the page about every 1 to > 2 seconds to > > determine what responses have been fullfilled and update > the client with > > a status of which requests have been completed and which > requests are > > still working. > > > > Is this possible with CFMX? > > > > I would presume this would require some multi-threading, etc. > > > > Thanks for you help. > > > > -- > > Troy Simpson > > Applications Analyst/Programmer, OCPDBA, MCSE, SCSA > > North Carolina State University Libraries > > Campus Box 7111 | Raleigh | North Carolina > > ph.919.515.3855 | fax.919.513.3330 > > E-mail: [EMAIL PROTECTED] > > > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: How would I do this?
Oh, one other thing. How could I submit several requests to various back-end systems (RDBMS, IMAP, HTTP, etc. ) simultaneously. For example, I want to execute the following request simultaneously. All requests are independent of others. So I do not have to wait for one come back before processing the next on. 1. query on RDBMS_1, 2. run another query on RDBMS_2, 3. request an http page from a web server, 4. query an LDAP server. It does not matter which process comes back first/last. It does not matter if one of them fails. I did not want to execute them procedurally. If they do excute procedurally, then I would have to wait for a prior request to complete before moving own to the next request. Can ColdFusion do this? Thanks, Troy Troy Simpson wrote: > Hello all, > > I am using ColdFusionMX on Solaris8/9 > > I need to query some resources that do not respond in a timely manner > and as a fix, I can not cache the data. > > So what I would like to do is submit the request(queryies) to the > various backend systems, then respond to the user with something like > > "Preparing results. One moment please..." > > Then I would like to auto-submit the page about every 1 to 2 seconds to > determine what responses have been fullfilled and update the client with > a status of which requests have been completed and which requests are > still working. > > Is this possible with CFMX? > > I would presume this would require some multi-threading, etc. > > Thanks for you help. > > -- > Troy Simpson > Applications Analyst/Programmer, OCPDBA, MCSE, SCSA > North Carolina State University Libraries > Campus Box 7111 | Raleigh | North Carolina > ph.919.515.3855 | fax.919.513.3330 > E-mail: [EMAIL PROTECTED] > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: How would I do this?
CFFLUSH? At 03:11 PM 5/18/2004, you wrote: >Hello all, > >I am using ColdFusionMX on Solaris8/9 > >I need to query some resources that do not respond in a timely manner >and as a fix, I can not cache the data. > >So what I would like to do is submit the request(queryies) to the >various backend systems, then respond to the user with something like > >"Preparing results. One moment please..." > >Then I would like to auto-submit the page about every 1 to 2 seconds to >determine what responses have been fullfilled and update the client with >a status of which requests have been completed and which requests are >still working. > >Is this possible with CFMX? > >I would presume this would require some multi-threading, etc. > >Thanks for you help. > >-- >Troy Simpson > Applications Analyst/Programmer, OCPDBA, MCSE, SCSA >North Carolina State University Libraries >Campus Box 7111 | Raleigh | North Carolina >ph.919.515.3855 | fax.919.513.3330 >E-mail: [EMAIL PROTECTED] > >-- >[Todays Threads] >[This Message] >[Subscription] >[Fast > Unsubscribe] [User Settings] > >-- > >[] > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
