Re: (ot) SQL Question - flattening data
Depending on what you are doing with this data, seems to me that this should be done in the front end and not the db. So get your data in the original format: USER, CODE rick,AL rick,FR rick,TR rick,HS joe,AL joe,FU Bob,FM And then use cfoutput with query and group (a very rough output here): table thead trthUser/ththCodes/th /thead tbody cfoutput query=theQuery group=user tr td#user/td td cfoutput#code# /cfoutput /td tr /cfoutput /tbody /table Dominic 2009/11/13 Rick Root rick.r...@gmail.com From the documentation, pivot tables seem to require aggregate functions... The generic description would seem to work but the examples make it difficult to see how. But... I figured out a solution! Using SQL Server's row_number() over (partition by XXX order by XXX) I can make a subquery that returns data like this entityid,rownum,rectyp And then run this query (tb901 is my primary table) select tb901.entityid, R1.rectypcd as rectype1, r2.rectypcd as rectype2, r3.rectypcd as rectype3, r4.rectypcd as rectype4, r5.rectypcd as rectype5 from tb901 left join ( select row_number() over(partition by entityid order by rectypcd) as rownum, entityid, rectypcd from tb906 ) R1 on tb901.entityid=R1.entityid and R1.rownum=1 left join ( select row_number() over(partition by entityid order by rectypcd) as rownum, entityid, rectypcd from tb906 ) R2 on tb901.entityid=R2.entityid and R2.rownum=2 left join ( select row_number() over(partition by entityid order by rectypcd) as rownum, entityid, rectypcd from tb906 ) R3 on tb901.entityid=R3.entityid and R3.rownum=3 left join ( select row_number() over(partition by entityid order by rectypcd) as rownum, entityid, rectypcd from tb906 ) R4 on tb901.entityid=R4.entityid and R4.rownum=4 left join ( select row_number() over(partition by entityid order by rectypcd) as rownum, entityid, rectypcd from tb906 ) R5 on tb901.entityid=R5.entityid and R5.rownum=5 ~| 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:328396 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) SQL Question - flattening data
Just read the original post properly - please ignore me. Dominic 2009/11/15 Dominic Watson watson.domi...@googlemail.com Depending on what you are doing with this data, seems to me that this should be done in the front end and not the db. So get your data in the original format: USER, CODE rick,AL rick,FR rick,TR rick,HS joe,AL joe,FU Bob,FM And then use cfoutput with query and group (a very rough output here): table thead trthUser/ththCodes/th /thead tbody cfoutput query=theQuery group=user tr td#user/td td cfoutput#code# /cfoutput /td tr /cfoutput /tbody /table Dominic 2009/11/13 Rick Root rick.r...@gmail.com From the documentation, pivot tables seem to require aggregate functions... The generic description would seem to work but the examples make it difficult to see how. But... I figured out a solution! Using SQL Server's row_number() over (partition by XXX order by XXX) I can make a subquery that returns data like this entityid,rownum,rectyp And then run this query (tb901 is my primary table) select tb901.entityid, R1.rectypcd as rectype1, r2.rectypcd as rectype2, r3.rectypcd as rectype3, r4.rectypcd as rectype4, r5.rectypcd as rectype5 from tb901 left join ( select row_number() over(partition by entityid order by rectypcd) as rownum, entityid, rectypcd from tb906 ) R1 on tb901.entityid=R1.entityid and R1.rownum=1 left join ( select row_number() over(partition by entityid order by rectypcd) as rownum, entityid, rectypcd from tb906 ) R2 on tb901.entityid=R2.entityid and R2.rownum=2 left join ( select row_number() over(partition by entityid order by rectypcd) as rownum, entityid, rectypcd from tb906 ) R3 on tb901.entityid=R3.entityid and R3.rownum=3 left join ( select row_number() over(partition by entityid order by rectypcd) as rownum, entityid, rectypcd from tb906 ) R4 on tb901.entityid=R4.entityid and R4.rownum=4 left join ( select row_number() over(partition by entityid order by rectypcd) as rownum, entityid, rectypcd from tb906 ) R5 on tb901.entityid=R5.entityid and R5.rownum=5 ~| 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:328397 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Community Collaboration Chat starting Now
Wish I'd posted this an hour or so ago, but I got caught up trying to help my daughter with something. :) We're talking about design patterns and techniques that will help to encourage integration between different projects in the CF community. http://experts.na3.acrobat.com/collab/ Sign in as guest -- s. isaac dealey :: AutLabs Creating meaningful employment for people with Autism http://www.autlabs.com ph: 817.385.0301 http://onTap.riaforge.org/blog ~| 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:328398 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Access Data Source Problem with ColdFusion 9 on 64 bit Windows 7
I'm having trouble creating Access Data Sources in ColdFusion 9 (same issue with CF8) on a 64 bit Windows 7 box. Two questions: 1) Does anyone know when a 64 bit version of ColdFusion 9 will be released that is compatible with Windows 7? 2) Does anyone have a workaround for this issue, apart from installing ColdFusion on the XP Virtual PC? The specific errors are: When I create the Data Source in ColdFusion I get Unable to update the NT registry. Variable DRIVERPATH is undefined. When I try to use the Data Source I get Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket]internal error: The specified DSN contains an architecture mismatch between the Driver and Application There apparently is no 64 bit Access ODBC driver but I can get to the 32 bit drivers at C:\Windows\SysWOW64\odbcad32.exe. Setting up the Access DSN there first, or replacing it after the Data Source is created in ColdFusion does not solve the problem. Thanks for any help you can offer. Bob Mack cantabr...@comcast.net ~| 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:328399 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Access Data Source Problem with ColdFusion 9 on 64 bit Windows 7
1) I have been running CF9 an Windows 7 sine the Beta without issue for months. 2) Pretty sure access dosn't work in 64bit ColdFusion 8 or 9 Paul. On Nov 15, 2009, at 7:16 PM, Robert Mack wrote: I'm having trouble creating Access Data Sources in ColdFusion 9 (same issue with CF8) on a 64 bit Windows 7 box. Two questions: 1) Does anyone know when a 64 bit version of ColdFusion 9 will be released that is compatible with Windows 7? 2) Does anyone have a workaround for this issue, apart from installing ColdFusion on the XP Virtual PC? The specific errors are: When I create the Data Source in ColdFusion I get Unable to update the NT registry. Variable DRIVERPATH is undefined. When I try to use the Data Source I get Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket]internal error: The specified DSN contains an architecture mismatch between the Driver and Application There apparently is no 64 bit Access ODBC driver but I can get to the 32 bit drivers at C:\Windows\SysWOW64\odbcad32.exe. Setting up the Access DSN there first, or replacing it after the Data Source is created in ColdFusion does not solve the problem. Thanks for any help you can offer. Bob Mack cantabr...@comcast.net ~| 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:328400 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Access Data Source Problem with ColdFusion 9 on 64 bit Windows 7
I'm having trouble creating Access Data Sources in ColdFusion 9 (same issue with CF8) on a 64 bit Windows 7 box. Two questions: 1) Does anyone know when a 64 bit version of ColdFusion 9 will be released that is compatible with Windows 7? The current version is compatible with Windows 7. 2) Does anyone have a workaround for this issue, apart from installing ColdFusion on the XP Virtual PC? If you want to use 32-bit stuff, use the 32-bit version of CF. It'll work fine on a 64-bit OS. There will be some issues if you want to hook it to IIS, but those issues are documented and pretty easy to fix. On the other hand, maybe this is the divine hand of Providence pushing you away from Access and towards, well, anything else. 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 information ~| 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:328401 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Regex help with invalid HTML
Calling all regex gurus. I've spent a little time on this so now it's time to seek advice from the professionals. Here is an example of the content I'm working with: trtd class=la href=/abc.co.nz/atd52 363td73 815td5 122 265td2 166 760td471.47 MB trtd class=la href=/xyz.co.nz/atd31 622td23 443td193 645td840 642td1.8 GB trtd class=la href=/blah.com/atd31 622td25 623td193 645td840 642td1.9 GB And what I want to do is remove everything between the first td (after the closing /a) and the last td BEFORE the next tr. E.G. This trtd class=la href=/abc.co.nz/atd52 363td73 815td5 122 265td2 166 760td471.47 MB becomes trtd class=la href=/abc.co.nz/a 471.47 MB At that point I will then strip all the remaining HTML tags (which I can do) and I should be good to go. Unfortunately I have no control over this code as it is generated by a stats program, and if indeed it used the correct closing tags and validated I could probably fumble around and eventually achieve what I want, as I've done in the past. And just in case anyone out there can do all this in one hit, ultimately I want the output from above to look like this: abc.co.nz 471.47 MB xyz.co.nz 1.8 GB blah.com 1.9 GB etc. I hope that makes sense. TIA Mark ~| 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:328402 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Regex help with invalid HTML
Will it always be a domain name you want to keep? And will the file size always be at the very end of the line? -Original Message- From: Mark Henderson [mailto:m...@cwc.co.nz] Sent: Sunday, November 15, 2009 8:38 PM To: cf-talk Subject: Regex help with invalid HTML Calling all regex gurus. I've spent a little time on this so now it's time to seek advice from the professionals. Here is an example of the content I'm working with: trtd class=la href=/abc.co.nz/atd52 363td73 815td5 122 265td2 166 760td471.47 MB trtd class=la href=/xyz.co.nz/atd31 622td23 443td193 645td840 642td1.8 GB trtd class=la href=/blah.com/atd31 622td25 623td193 645td840 642td1.9 GB And what I want to do is remove everything between the first td (after the closing /a) and the last td BEFORE the next tr. E.G. This trtd class=la href=/abc.co.nz/atd52 363td73 815td5 122 265td2 166 760td471.47 MB becomes trtd class=la href=/abc.co.nz/a 471.47 MB At that point I will then strip all the remaining HTML tags (which I can do) and I should be good to go. Unfortunately I have no control over this code as it is generated by a stats program, and if indeed it used the correct closing tags and validated I could probably fumble around and eventually achieve what I want, as I've done in the past. And just in case anyone out there can do all this in one hit, ultimately I want the output from above to look like this: abc.co.nz 471.47 MB xyz.co.nz 1.8 GB blah.com 1.9 GB etc. I hope that makes sense. TIA Mark ~| 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:328403 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Regex help with invalid HTML
lists wrote: Will it always be a domain name you want to keep? And will the file size always be at the very end of the line? Yes, and yes (confirmed all the TRs start on a new line). Regards Mark ~| 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:328404 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Regex help with invalid HTML
you can do it with something like this: cfset line='trtd class=la href=/blah.com/atd31 622td25 623td193 645td840 642td1.9 GB' cfset cleanline = rereplace(line, 't[^]+', '|', 'all') cfoutput#listfirst(cleanline, '|')# #listlast(cleanline, '|')#/cfoutput and if you do not want any html in final result (not even a tag), then use: cfset cleanline = rereplace(line, '[^]+', '|', 'all') Azadi Saryev On 16/11/2009 10:37, Mark Henderson wrote: Calling all regex gurus. I've spent a little time on this so now it's time to seek advice from the professionals. Here is an example of the content I'm working with: trtd class=la href=/abc.co.nz/atd52 363td73 815td5 122 265td2 166 760td471.47 MB trtd class=la href=/xyz.co.nz/atd31 622td23 443td193 645td840 642td1.8 GB trtd class=la href=/blah.com/atd31 622td25 623td193 645td840 642td1.9 GB And what I want to do is remove everything between the first td (after the closing /a) and the last td BEFORE the next tr. E.G. This trtd class=la href=/abc.co.nz/atd52 363td73 815td5 122 265td2 166 760td471.47 MB becomes trtd class=la href=/abc.co.nz/a 471.47 MB At that point I will then strip all the remaining HTML tags (which I can do) and I should be good to go. Unfortunately I have no control over this code as it is generated by a stats program, and if indeed it used the correct closing tags and validated I could probably fumble around and eventually achieve what I want, as I've done in the past. And just in case anyone out there can do all this in one hit, ultimately I want the output from above to look like this: abc.co.nz 471.47 MB xyz.co.nz 1.8 GB blah.com 1.9 GB etc. I hope that makes sense. TIA Mark ~| 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:328405 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
