Re: (ot) Transact-SQL Help
Brad, I'll have to look at your response in much greater detail, but I can tell you this. Currently, I'm running a CF script that populates a prospect_export table once a day. The initial query returns 25,785 rows, which gets flattened into 20,265 rows for reporting purposes. Ultimately I'd rather this were a view, but barring that, I'd rather repopulate the table more often than once a day, and I'm fairly certain that doing it with SQL instead of CF would be more efficient. -- Rick Root CFFM - Open Source Coldfusion File Manager http://www.opensourcecf.com/cffm ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326228 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) Transact-SQL Help
Hi Rick, This is where the limiting nature of SQL comes in--SQL is a set language and has no (or very limited) looping/reshaping capability. Unless MS has extended their SQL in ways I'm not aware of, this would be nearly impossible. That's where the power of CF comes in. --Ben Rick Root wrote: I'm hoping someone here can point me in the right direction. I'm doing something in CF that I really need to be doing in SQL: I need to flatten this data: select A.entityid, A.MEMBERID, A.RELTYPE, A.leaderFlag from PTRACK.dbo.ptTeam A WHERE A.status=1 into this format: entityid, EXEC1, (1st row reltype='ETL') EXEC2, (2nd row reltype='ETL') TEAMLEAD, (1st row leaderFlag=1) MEMBER1,(1st row reltype='TMM' and leaderflag=0) MEMBER2,(2nd row reltype='TMM' and leaderflag=0) MEMBER3,(3rd row reltype='TMM' and leaderflag=0) MEMBER4,(4th row reltype='TMM' and leaderflag=0) MEMBER5,(5th row reltype='TMM' and leaderflag=0) AFSTAFF1, (1st row reltype='AFS') AFSTAFF2, (2nd row reltype='AFS') AFVOL,, (1st row reltype='AFV') DOCTOR1,, (1st row reltype='AMD') DOCTOR2,, (1st row reltype='AMD') The query returns multiple rows per entity. The output will be one row per entity. In CF, I'm looping over the first query, and creating a resultset that looks like the second query. If an entity has more than 1 executive team leader or more than 5 team members or more than 2 doctors associated, we ignore the extras. But I don't have the slightest friggin' clue how to do this in SQL. My db is MS SQL Server 2005 Thanks -- Ben Connerb...@webworldinc.com Web World, Inc. 888-206-6486 PO Box 1122 480-704-2000 Queen Creek, AZ 85242 ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326208 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) Transact-SQL Help
This is where the limiting nature of SQL comes in--SQL is a set language and has no (or very limited) looping/reshaping capability. Unless MS has extended their SQL in ways I'm not aware of, this would be nearly impossible. That's where the power of CF comes in. That's not true. You can use cursors to iterate through a recordset, for example. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more informatio ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326210 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) Transact-SQL Help
You might also take a look at ms sql 2005's row_number() function. You may be able to partition the data and use row_number() to limit the returned records. http://msdn.microsoft.com/en-us/library/ms186734.aspx ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326213 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: (ot) Transact-SQL Help
Original Message Subject: (ot) Transact-SQL Help From: Rick Root rick.r...@webworksllc.com Date: Thu, September 10, 2009 12:25 pm To: cf-talk cf-talk@houseoffusion.com I'm hoping someone here can point me in the right direction. I'm doing something in CF that I really need to be doing in SQL: After reading your post several times I think I might understand just what it is you are trying to accomplish here. Questions: What makes you think you need to do this in SQL? Is the current code performing poorly? Are there really hundreds of columns and this is just a simplified example? Are there really only 13 records in the ptTeam table or are you actually doing dozens of times on one page? Is there any particular difference between EXEC1 and EXEC2 or is it just up to the randomness of the query as to who gets to be 1 and who gets to be 2? Do you know if you will have at least one of every type of person? Since you asked-- as for SQL solutions, it looks like you are basically wanting a column for each record matching a particular criteria. My first thought is a messy select statement that joins to the ptTeam table once for every column. This will actually be made harder if the order of the members of the same type is not explicit. Note: you would need to change each join that isn't guaranteed to return a match to an LEFT OUTER JOIN. I'm also making the wild assumption that your records all share the same entityid but have a unique MEMBERID. You also didn't say what column you wanted to return for each member so I assumed you wanted the MEMBERID column. SELECT TOP 1 EXEC1.MEMBERID AS EXEC1, EXEC1.MEMBERID AS EXEC2, TEAMLEAD.MEMBERID AS TEAMLEAD, etc... FROM ptTeam EXEC1 INNER JOIN ptTeam EXEC2 ON EXEC1.entityid = 'foo' AND EXEC1.status = 1 AND reltype='ETL' AND EXEC1.MEMBERID EXEC2.MEMBERID INNER JOIN ptTeam TEAMLEAD ON EXEC1.entityid = 'foo' AND TEAMLEAD.status = 1 AND TEAMLEAD.leaderFlag=1 INNER JOIN etc... WHERE EXEC1.status = 1 AND reltype='ETL' AND EXEC1.entityid = 'foo' For the record, I would expect this to perform like crap since it's going to make every possible combination and then pick one to return with TOP 1. Moving on, another option would be to declare a table variable to hold your final result set: DECLARE @finalResult TABLE (entityid int, EXEC1 int, EXEC2 int, TEAMLEAD int, MEMBER1 int, MEMBER2 int, MEMBER3 int, MEMBER4 int etc...) Then populate your columns one statement at a time: INSERT INTO @finalResult (entityid, EXEC1) (SELECT entityid, MEMEBRID FRKM PTRACK.dbo.ptTeam WHERE status=1 AND reltype='ETL' UPDATE fr SET fr.EXEC2 = team.MEMBERID FROM @finalResult fr INNER JOIN ptTeam team ON fr.entityid = team.entityid AND team.status=1 AND team.reltype='ETL' AND fr.EXEC1 team.MEMBERID UPDATE fr SET fr.TEAMLEAD = lead.MEMBERID FROM @finalResult fr INNER JOIN ptTeam lead ON fr.entityid = lead.entityid AND lead.status=1 AND lead.leaderFlag=1 etc... Ok, now that I gave those examples let me say I think they're both pretty crappy. Honestly I'd do this in CF if it were me based on what you showed us, but I would do it with query of queries. Return your 15 or so records in a single, simple hit to the database. Then perform 6 qofqs on it-- one for each type of member. In other words, get all the EXECs in one, and all team lead in another, and then just loop over them as you go and output them as EXEC1, EXEC2, TEAMLEAD, etc. As long as you are only dealing with a dozen or so records from the database it should perform fine and and I think it will be a heck of a lot simpler than trying to make your SQL server take a row-based list of people and pivot them out into columns. That never works easily because it really isn't the kind of thing SQL Server was designed to do. On the cursor thing-- that is another option I didn't present, but I really don't think it's a better one. Thanks. ~Brad ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326214 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: (ot) Transact-SQL Help
As long as you are only dealing with a dozen or so records from the database it should perform fine and and I think it will be a heck of a lot simpler than trying to make your SQL server take a row-based list of people and pivot them out into columns. Assuming the example is actually representative of the data, partitioning with row_number() may be a feasible alternative to pivoting or cursors. But it would probably still require a bit of fancy footwork in terms of sql. Having said that, Brad raises some valid questions about the data. Is it really that small and simple a recordset, and if so what is the perceived advantage of doing it in sql over using CF ? -Leigh ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326216 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: Transact-SQL help
Rick Root wrote:
syntax of my function: getDistance(zip1,long1,lat1,zip2,long2,lat2)
taking either the zip code or the lat/long for each...
SELECT *
FROM prospects A
WHERE
zipcode in
(
SELECT B.zipcode
FROM zipcodes B
WHERE
getDistance('27502',0.0,0.0,'',B.latitude,B.longitude)
)
This query is not indexable so it needs to do the math on each and every
row. Prequalify the rows by drawing an imaginary box on the map from
b.lat + X to b.lat -X and b.lon + X to b.lon -X and finding only the
points in that box (the database can do that using an index). Then
perform your distance function only on the points in the box.
Jochem
~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 MX7 integration create powerful cross-platform RIAs
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268848
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: Transact-SQL help
On 2/6/07, Jochem van Dieten [EMAIL PROTECTED] wrote:
This query is not indexable so it needs to do the math on each and every
row. Prequalify the rows by drawing an imaginary box on the map from
b.lat + X to b.lat -X and b.lon + X to b.lon -X and finding only the
points in that box (the database can do that using an index). Then
perform your distance function only on the points in the box.
I did this to speed it up originally:
SELECT B.zipcode
FROM zipcodes B
WHERE
B.zipcode between '27002' and '28002'
getDistance('27502',0.0,0.0,'',B.latitude,B.longitude)
That caused it to only look at 1000 zip codes instead of 42000 zip codes.
the lat/long adjustment would probably be more effective.. or perhaps at
least, more accurate.
Rick
--
I'm not certified, but I have been told that I'm certifiable...
Visit http://www.opensourcecf.com today!
~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 MX7 integration create powerful cross-platform RIAs
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268854
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
