Re: comparing data in a loop
>Mass imports, world of pain! lol tell me about it!!! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300162 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: comparing data in a loop
Mass imports, world of pain! On 29/02/2008, Richard White <[EMAIL PROTECTED]> wrote: > > hi dominic, ok thanks for your help. unfortunately it is not a query built > from mysql that i could run the distinct on. it is a query that is built > from the contents of a ms excel spreadsheet. > > its ok ill look for another solution, your right, its a real shame QoQ > doesn't work as it should as i was starting to see loads of uses for it!!! > > > thanks again for your help > > > richard > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300161 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: comparing data in a loop
hi dominic, ok thanks for your help. unfortunately it is not a query built from mysql that i could run the distinct on. it is a query that is built from the contents of a ms excel spreadsheet. its ok ill look for another solution, your right, its a real shame QoQ doesn't work as it should as i was starting to see loads of uses for it!!! thanks again for your help richard ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300159 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: comparing data in a loop
I'm afraid I don't know the answer to the type issue. QoQ is problematic and in the past I have had to look to other solutions where, if it were working, QoQ would have been preferrable. There may be a solution to do with explicitly typing the columns that someone here can offer though. How is the data in the query got at? If it is from a single dynamic query that you have access to then the ideal solution would be to have the SELECT DISTINCT in the original query so no need for the additional query. Dominic On 29/02/2008, Richard White <[EMAIL PROTECTED]> wrote: > > >Another thing, building a query string isn't required with ColdFusion in > the > >way you are doing it, have you come from another language? > > > hi dominic, thanks for the response. i have come from VB but dont think > this explains the poor coding i still class myself as a newbie to coding > even though i have built a couple of full applications. i am trying hard to > pick as many good standards as possible as i go along so thanks very much > for this. this does help me to understand coldfusion further, and your right > - to make it more readable > > ill give it a go and see what happens, but sorry i didn't quite get what > you were saying on the QoQ, if its producing the error as it is typeless is > there a way i can get around this. i can obviously do it through a loop now > i have got it this far but, as you said, for readability i would like to do > it this way as it cuts down on alot of confusion > > thanks > > richard > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300158 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: comparing data in a loop
>Another thing, building a query string isn't required with ColdFusion in the >way you are doing it, have you come from another language? hi dominic, thanks for the response. i have come from VB but dont think this explains the poor coding i still class myself as a newbie to coding even though i have built a couple of full applications. i am trying hard to pick as many good standards as possible as i go along so thanks very much for this. this does help me to understand coldfusion further, and your right - to make it more readable ill give it a go and see what happens, but sorry i didn't quite get what you were saying on the QoQ, if its producing the error as it is typeless is there a way i can get around this. i can obviously do it through a loop now i have got it this far but, as you said, for readability i would like to do it this way as it cuts down on alot of confusion thanks richard ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300157 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: comparing data in a loop
QoQ (Query of queries) struggles with data types as ColdFusion is 'typeless' while the QoQ engine needs to know types. It tries to figure out the types of columns and often gets it wrong :( Another thing, building a query string isn't required with ColdFusion in the way you are doing it, have you come from another language? Instead of: #sqlQuery# You can (and probably should for readability) do: select distinct #uniqueColumnString# from massImport Cfquery is in essence a cfoutput block that turns the output into a query string before sending it to the server (doing other magic too I'm sure). HTH Dominic On 29/02/2008, Richard White <[EMAIL PROTECTED]> wrote: > > i have seen the documentation on the query on a query and it says that > distinct can be used and i have it exactly as in the example so i really > dont know why that error is showing! > > anyone seen this error before? > > thanks > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300156 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: comparing data in a loop
i have seen the documentation on the query on a query and it says that distinct can be used and i have it exactly as in the example so i really dont know why that error is showing! anyone seen this error before? thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300142 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: comparing data in a loop
you guys have really helped me out, thanks!!! i didnt even realise that i could do a query on a query. this will help tidy my code and business logic up no end. i am trying to do a combination of some of your suggestions as follows: <--- this is the unique column array ---> <--- massImport is the name of the query ---> <--- then simply run the query ---> #sqlQuery# i have tried various tests with the query on query and overall it is working fine except when i put the distinct part on the query it produces an error saying: java.lang.Long cannot be cast to java.lang.String however if i run a similar piece of code in mysql, it works fine. can anyone see what i have done wrong thanks again for your help ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300126 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: comparing data in a loop
thanks this is a good idea ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300120 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: comparing data in a loop
hi, thanks for all your replies and thanks charlie this is a very elegant solution :) i didn't even know about this function, thanks again richard ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300121 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: comparing data in a loop
On Thu, Feb 28, 2008 at 12:16 PM, Richard White <[EMAIL PROTECTED]> wrote: > hi, > > i know there must be a simple answer to this but i am braindead right now > and cannot think of it. this is more of a logic question, and i am lacking it > right now :) i would appreciate your help please > > i have data in a query, e.g. > > col1 col2 col3 > --- > 1 2 3 > 1 2 4 > 1 3 1 > etc > > i need to be able to check that no 2 rows have the same data > the problem is, is that i dont know how many columns there will be and i > dont know what the unique columns will be. but i have the query and i have an > array of unique columns > > for example i have 'col1' and 'col2' in an array called uniqueColumns() > > so i need to somehow search through the query to ensure that no 2 rows > contain the same data - but only in the unique columns. > > so on the above example if i did have col1 and col2 in the uniqueColumns > array then when searching through the query i need it to tell me that row 1 > and 2 have matching unique columns as they both contain: > > col1 = 1 > col2 = 2 create a valueList for each column in the array. use a compare() or compareNoCase() on the resulting lists. -- Evelyn the dog, having undergone further modification pondered the significance of short-person behaviour in pedal depressed, pan-chromatic resonance, and other highly ambient domains. "Arf," she said. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300118 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: comparing data in a loop
Combine the columns using an alias and query for DISTINCT? -Original Message- From: Richard White [mailto:[EMAIL PROTECTED] Sent: Thursday, February 28, 2008 3:16 PM To: CF-Talk Subject: comparing data in a loop hi, i know there must be a simple answer to this but i am braindead right now and cannot think of it. this is more of a logic question, and i am lacking it right now :) i would appreciate your help please i have data in a query, e.g. col1 col2 col3 --- 1 2 3 1 2 4 1 3 1 etc i need to be able to check that no 2 rows have the same data the problem is, is that i dont know how many columns there will be and i dont know what the unique columns will be. but i have the query and i have an array of unique columns for example i have 'col1' and 'col2' in an array called uniqueColumns() so i need to somehow search through the query to ensure that no 2 rows contain the same data - but only in the unique columns. so on the above example if i did have col1 and col2 in the uniqueColumns array then when searching through the query i need it to tell me that row 1 and 2 have matching unique columns as they both contain: col1 = 1 col2 = 2 i would be very grateful of your help thanks richard ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300117 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: comparing data in a loop
Copy and paste error: On Thu, Feb 28, 2008 at 3:37 PM, Sonny Savage <[EMAIL PROTECTED]> wrote: > It might be useful to know that you can reference query objects like a > struct of arrays: QueryName['ColumnName'][1] > > So > > > > QueryName[ColNameA][QueryName.CurrentRow]> > > > > > This might be something that would be accomplished better in the SQL > statement though. A more specific description of the issue would be > helpful. > > > On Thu, Feb 28, 2008 at 3:16 PM, Richard White <[EMAIL PROTECTED]> wrote: > > > hi, > > > > i know there must be a simple answer to this but i am braindead right > > now and cannot think of it. this is more of a logic question, and i am > > lacking it right now :) i would appreciate your help please > > > > i have data in a query, e.g. > > > > col1 col2 col3 > > --- > > 1 2 3 > > 1 2 4 > > 1 3 1 > > etc > > > > i need to be able to check that no 2 rows have the same data > > the problem is, is that i dont know how many columns there will be and i > > dont know what the unique columns will be. but i have the query and i have > > an array of unique columns > > > > for example i have 'col1' and 'col2' in an array called uniqueColumns() > > > > so i need to somehow search through the query to ensure that no 2 rows > > contain the same data - but only in the unique columns. > > > > so on the above example if i did have col1 and col2 in the uniqueColumns > > array then when searching through the query i need it to tell me that row 1 > > and 2 have matching unique columns as they both contain: > > > > col1 = 1 > > col2 = 2 > > > > i would be very grateful of your help > > > > thanks > > > > richard > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300116 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: comparing data in a loop
It might be useful to know that you can reference query objects like a struct of arrays: QueryName['ColumnName'][1] So This might be something that would be accomplished better in the SQL statement though. A more specific description of the issue would be helpful. On Thu, Feb 28, 2008 at 3:16 PM, Richard White <[EMAIL PROTECTED]> wrote: > hi, > > i know there must be a simple answer to this but i am braindead right now > and cannot think of it. this is more of a logic question, and i am lacking > it right now :) i would appreciate your help please > > i have data in a query, e.g. > > col1 col2 col3 > --- > 1 2 3 > 1 2 4 > 1 3 1 > etc > > i need to be able to check that no 2 rows have the same data > the problem is, is that i dont know how many columns there will be and i > dont know what the unique columns will be. but i have the query and i have > an array of unique columns > > for example i have 'col1' and 'col2' in an array called uniqueColumns() > > so i need to somehow search through the query to ensure that no 2 rows > contain the same data - but only in the unique columns. > > so on the above example if i did have col1 and col2 in the uniqueColumns > array then when searching through the query i need it to tell me that row 1 > and 2 have matching unique columns as they both contain: > > col1 = 1 > col2 = 2 > > i would be very grateful of your help > > thanks > > richard > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300115 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
