Re: (ot) sql help
Figured it out. I was missing a join. Should have been: SELECT DISTINCT phases.phase AS phaseLabel, narratives.title, narratives.id, narratives.content, phases.id AS phaseId FROM( ( nsftool.phases2company phases2company INNER JOIN nsftool.phases phases ON (phases2company.phaseId = phases.id)) INNER JOIN nsftool.narratives narratives ON (narratives.phase = phases.id)) INNER JOIN nsftool.narratives2case narratives2case ON (narratives2case.narrativeId = narratives.id) AND (phases2company.caseId = narratives2case.caseId) WHERE (narratives2case.caseId = cfqueryparam cfsqltype=cf_sql_numeric null=no value=#trim(caseId)# /) ORDER BY phases2company.displayRank ASC, narratives.displayRank ASC On Fri, Oct 15, 2010 at 1:35 PM, Michael J. Sprague mikespra...@gmail.comwrote: I am having a SQL problem that I'm guessing amounts to me just being brain dead today but I'd really appreciate any help with this. Here is the query: SELECT phases.phase AS phaseLabel, narratives.title, narratives.id, narratives.content, phases.id AS phaseId FROM(( nsftool.narratives2case narratives2case INNER JOIN nsftool.narratives narratives ON (narratives2case.narrativeId = narratives.id)) INNER JOIN nsftool.phases2case phases2case ON (phases2case.caseId = narratives2case.caseId)) INNER JOIN nsftool.phases phases ON (phases2case.phaseId = phases.id) WHERE (narratives2case.caseId = cfqueryparam cfsqltype=cf_sql_numeric null=no value=#trim(caseId)# /) ORDER BY phases2case.displayRank ASC, narratives.displayRank ASC It is almost working but it is currently returning the same narrative titles and content for each phaseId. What I need to do is return the phases for a particular case ordered by their phase display rank and return the associated narrative content ordered by the narrative display rank. Currently it returns the same set of narrative content for each phase. Any ideas? Thanks in advance for any help with this. ~| 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:338239 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: OT - Sql Help
Mickael wrote: select * from table where date between Jan2005 and feb2005 ( so in essence not having to say Jan 1, 2005 and Feb 28, 2005) How about: WHERE MONTH(date) = 1 and YEAR(date) = 2005) and MONTH(date) = 2 and YEAR(date) = 2005 ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:195189 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT - Sql Help
try something like: cfset from_date = createDate(form.fromyear, form.fromMonth, 1) / cfset to_date = createDate(form.toyear, form.toMonth, daysInMonth(createDate(form.toyear, form.toMonth, 1))) / SELECT * FROM table WHERE date BETWEEN (#createODBCDate(variables.from_date))# AND #createODBCDate(variables.to_date)#) btw...i'm hoping 'date' isn't actually your column name. it's a reserved word and will cause you pain and heartache :) On Thu, 17 Feb 2005 11:19:29 -0500, Mickael [EMAIL PROTECTED] wrote: Hi All, I am a little stumped with the following task in SQL which seems simple enough to me. I have a form that my users can select a range of Dates for a particular report. The range is not day month year, but month and year and ending month and year. In my database I have a date field, it is only one date. The date is (dd/mm/) format. Let's say that I have 5 records for Jan 5, 5 records for Feb 1 and 5 records for feb 15. I would like to capture those 15 records by saying select * from table where date between Jan2005 and feb2005 ( so in essence not having to say Jan 1, 2005 and Feb 28, 2005) Can this be done? Thanks Mike ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:195190 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: OT - Sql Help
I created a table called test with two columns tid(identity int) and tdate(datetime filled it with date values. This is in SQL2k SELECT * FROM test where datepart(month,tdate) Between 1 and 2 and datepart(year,tdate) =2005 -Original Message- From: Mickael [mailto:[EMAIL PROTECTED] Sent: Thursday, February 17, 2005 10:19 AM To: CF-Talk Subject: OT - Sql Help Hi All, I am a little stumped with the following task in SQL which seems simple enough to me. I have a form that my users can select a range of Dates for a particular report. The range is not day month year, but month and year and ending month and year. In my database I have a date field, it is only one date. The date is (dd/mm/) format. Let's say that I have 5 records for Jan 5, 5 records for Feb 1 and 5 records for feb 15. I would like to capture those 15 records by saying select * from table where date between Jan2005 and feb2005 ( so in essence not having to say Jan 1, 2005 and Feb 28, 2005) Can this be done? Thanks Mike ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:195192 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT - Sql Help
oooh...that's cleaner than mine :) On Thu, 17 Feb 2005 11:26:52 -0500, Rick Root [EMAIL PROTECTED] wrote: Mickael wrote: select * from table where date between Jan2005 and feb2005 ( so in essence not having to say Jan 1, 2005 and Feb 28, 2005) How about: WHERE MONTH(date) = 1 and YEAR(date) = 2005) and MONTH(date) = 2 and YEAR(date) = 2005 ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:195193 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT - Sql Help
Hi Rick, That is simple. Thanks just what I was looking for - Original Message - From: Rick Root [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Thursday, February 17, 2005 11:26 AM Subject: Re: OT - Sql Help Mickael wrote: select * from table where date between Jan2005 and feb2005 ( so in essence not having to say Jan 1, 2005 and Feb 28, 2005) How about: WHERE MONTH(date) = 1 and YEAR(date) = 2005) and MONTH(date) = 2 and YEAR(date) = 2005 ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:195194 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT - Sql Help
Charlie Griefer wrote: oooh...that's cleaner than mine :) Maybe, some databases have the gall not to have Month() and Day() functions... postgres has neither... mysql didn't have Day() until 4.1.something.. although they had DayOfWeek(). Plus, if you want to use indexes, you can't do it the way I suggested (well, in oracle you can have function-based indexes). - Rick On Thu, 17 Feb 2005 11:26:52 -0500, Rick Root [EMAIL PROTECTED] wrote: Mickael wrote: select * from table where date between Jan2005 and feb2005 ( so in essence not having to say Jan 1, 2005 and Feb 28, 2005) How about: WHERE MONTH(date) = 1 and YEAR(date) = 2005) and MONTH(date) = 2 and YEAR(date) = 2005 ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:195198 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: OT - Sql Help
I think it's just as simple to say Select * from table where date = '1/1/2005' and date '3/1/2005' - Matt Small -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Thursday, February 17, 2005 11:50 AM To: CF-Talk Subject: Re: OT - Sql Help Charlie Griefer wrote: oooh...that's cleaner than mine :) Maybe, some databases have the gall not to have Month() and Day() functions... postgres has neither... mysql didn't have Day() until 4.1.something.. although they had DayOfWeek(). Plus, if you want to use indexes, you can't do it the way I suggested (well, in oracle you can have function-based indexes). - Rick On Thu, 17 Feb 2005 11:26:52 -0500, Rick Root [EMAIL PROTECTED] wrote: Mickael wrote: select * from table where date between Jan2005 and feb2005 ( so in essence not having to say Jan 1, 2005 and Feb 28, 2005) How about: WHERE MONTH(date) = 1 and YEAR(date) = 2005) and MONTH(date) = 2 and YEAR(date) = 2005 ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:195212 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT - Sql Help
Matthew Small wrote: I think it's just as simple to say Select * from table where date = '1/1/2005' and date '3/1/2005' The query is, but the way you get there dynamically is not, ecause you have to figure out what the next month is. So if your parameters are 1 2005 2 2005, you can't just add one to the month to make the query you posted, you have to create a date object 2/1/2005 then add a month to it using DateAdd()... Not a big deal, but the code is more complex. Realistically though, that's probably the better way of doing it, especially if you've got large amounts of data :) Simple code is not always the best solution. ;) - Rick ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:195230 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: [OT] SQL help
what is OT? -Original Message- From: Spectrum WebDesign [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 25, 2004 4:22 PM To: CF-Talk Subject: [OT] SQL help Hi I have an table were our client save your dob(mm/dd/) in My$QL DB. By now I need to aggrupate that table queries like this: Group 1: = 16 years Group 2: =17 E = 25 Group 3: =26 E = 40 Group 4: =41 years My$SQL give the ages but how to aggrupate in that groups? Thanx for your time. -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: [OT] SQL help
Off Topic -Original Message- From: Monique Boea [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 8:11 AM To: CF-Talk Subject: RE: [OT] SQL help what is OT? -Original Message- From: Spectrum WebDesign [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 25, 2004 4:22 PM To: CF-Talk Subject: [OT] SQL help Hi I have an table were our client save your dob(mm/dd/) in My$QL DB. By now I need to aggrupate that table queries like this: Group 1: = 16 years Group 2: =17 E = 25 Group 3: =26 E = 40 Group 4: =41 years My$SQL give the ages but how to aggrupate in that groups? Thanx for your time. -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: [OT] SQL help
From: Monique Boea what is OT? Off Topic [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: [OT] SQL help
Thanks :) -Original Message- From: John Stanley [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 8:13 AM To: CF-Talk Subject: RE: [OT] SQL help Off Topic -Original Message- From: Monique Boea [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 8:11 AM To: CF-Talk Subject: RE: [OT] SQL help what is OT? -Original Message- From: Spectrum WebDesign [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 25, 2004 4:22 PM To: CF-Talk Subject: [OT] SQL help Hi I have an table were our client save your dob(mm/dd/) in My$QL DB. By now I need to aggrupate that table queries like this: Group 1: = 16 years Group 2: =17 E = 25 Group 3: =26 E = 40 Group 4: =41 years My$SQL give the ages but how to aggrupate in that groups? Thanx for your time. -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm _ _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: [OT] SQL help
You are welcome. ;-) -Original Message- From: Monique Boea [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 8:17 AM To: CF-Talk Subject: RE: [OT] SQL help Thanks :) -Original Message- From: John Stanley [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 8:13 AM To: CF-Talk Subject: RE: [OT] SQL help Off Topic -Original Message- From: Monique Boea [mailto:[EMAIL PROTECTED] Sent: Thursday, May 27, 2004 8:11 AM To: CF-Talk Subject: RE: [OT] SQL help what is OT? -Original Message- From: Spectrum WebDesign [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 25, 2004 4:22 PM To: CF-Talk Subject: [OT] SQL help Hi I have an table were our client save your dob(mm/dd/) in My$QL DB. By now I need to aggrupate that table queries like this: Group 1: = 16 years Group 2: =17 E = 25 Group 3: =26 E = 40 Group 4: =41 years My$SQL give the ages but how to aggrupate in that groups? Thanx for your time. -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm _ _ _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: [OT] SQL help
Thnx Matthew works great brilliant! Success! Try this (where age is the formula you're already using). I'd recommend making a view that has the age as one of the columns. SELECT CASE WHEN age = 16 THEN 1 WHEN age BETWEEN 17 AND 25 THEN 2 WHEN age BETWEEN 26 AND 40 THEN 3 ELSE 4 ENDAS [group] From: Spectrum WebDesign [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 May 2004 8:22 a.m. To: CF-Talk Subject: [OT] SQL help Hi I have an table were our client save your dob(mm/dd/) in My$QL DB. By now I need to aggrupate that table queries like this: Group 1: = 16 years Group 2: =17 E = 25 Group 3: =26 E = 40 Group 4: =41 years My$SQL give the ages but how to aggrupate in that groups? Thanx for your time. -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: [OT] SQL help
Try this (where age is the formula you're already using). I'd recommend making a view that has the age as one of the columns. SELECT CASE WHEN age = 16 THEN 1 WHEN age BETWEEN 17 AND 25 THEN 2 WHEN age BETWEEN 26 AND 40 THEN 3 ELSE 4 ENDAS [group] From: Spectrum WebDesign [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 May 2004 8:22 a.m. To: CF-Talk Subject: [OT] SQL help Hi I have an table were our client save your dob(mm/dd/) in My$QL DB. By now I need to aggrupate that table queries like this: Group 1: = 16 years Group 2: =17 E = 25 Group 3: =26 E = 40 Group 4: =41 years My$SQL give the ages but how to aggrupate in that groups? Thanx for your time. -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: OT: SQL HELP
The ID your using have permissions on the table? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: OT: SQL HELP
yes.. permissions are there. -Original Message- From: Casey C Cook [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 18, 2004 4:40 PM To: CF-Talk Subject: Re: OT: SQL HELP The ID your using have permissions on the table? _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: OT: SQL help
What?? what about cursors? This seems pretty easy in SQL if you ask me. -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 26, 2003 4:08 PM To: CF-Talk Subject: Re: OT: SQL help Michael Hodgdon wrote: Before I begin, I know this is simple and remedial in CF, but I specifically want a SQL solution (if necessary, it can be a Transact SQL solution) Basically, I want to loop over a query using SQL syntax just like you would loop over a query using CFQUERY or CFLOOP QUERY tags. That is it. Looking for the same functionality using SQL instead of CF. I want to embed this functionality in a SQL script, but all the solutions I have tried report that the request has returned more than one possible match and errors out. Basically, SQL is treating the select as a whole object verses looking at each row one at a time. You can't do it in SQL. Jochem ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
Re: OT: SQL help
Mark A. Kruger - CFG wrote: What?? what about cursors? This seems pretty easy in SQL if you ask me. I should have been more specific. You can't do it in SQL in MS SQL Server. You need to use some non-standard T-SQL facilities. Jochem ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
Re: OT: SQL help
Michael Hodgdon wrote: Before I begin, I know this is simple and remedial in CF, but I specifically want a SQL solution (if necessary, it can be a Transact SQL solution) Basically, I want to loop over a query using SQL syntax just like you would loop over a query using CFQUERY or CFLOOP QUERY tags. That is it. Looking for the same functionality using SQL instead of CF. I want to embed this functionality in a SQL script, but all the solutions I have tried report that the request has returned more than one possible match and errors out. Basically, SQL is treating the select as a whole object verses looking at each row one at a time. You can't do it in SQL. Jochem ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: OT: SQL Help
I found the solution. I needed a where statement on the uid field. where table1.uid = table2.uid Thanks, JS __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: SQL Help
The problem is that you're using cursors. Cursors in SQL Server are very, very slow. Come up with an alternate way of doing your import. - Original Message - From: Duane Boudreau [EMAIL PROTECTED] Date: Thursday, January 23, 2003 9:02 am Subject: OT: SQL Help Hi All, I need the help of an SQL expert here. I am importing some rather large text files (250MB +) into a database. The structure I am working with looks like: tmp_vehix (~600,000 recs) Col001 - Col0067 (Col031 - Col065 contain option FK data) tmp_opts (~2900 recs) Col001, Col002 vehix vin (PK) vehix_options vin (PK) option_id (PK) options option_id (PK) ga_options_id (case sensitive, matches the values in tmp_vehix.Col031 - tmp_vehix.Col065) Everything is going pretty smooth save the last part where I copy the data in option fields Col031 - Col065 in tmp_vehix to their options equivalentsin the vehix_options table. The query I have written writes the 8.5 million records out to the table it is incredibly slow, estimated time is 24 hours. Would anyone mind taking a peak at the query to see if there are any glaring pain points that I could optimize and speed this beast up? Or possibly suggest improvements to the structure of vehix, vehix_options and options?Thanks in advance! Duane The query looks like this (some of the repetitious sections have been shortened). DECLARE @vin varchar(20), @Col031 varchar(255), @Col032 varchar(255), @Col033 varchar(255), @Col034 varchar(255), @Col035 varchar(255), @Col036 varchar(255), @Col037 varchar(255), @Col038 varchar(255), @Col039 varchar(255), @Col040 varchar(255), @Col041 varchar(255), @Col042 varchar(255), @Col043 varchar(255), @Col044 varchar(255), @Col045 varchar(255), @Col046 varchar(255), @Col047 varchar(255), @Col048 varchar(255), @Col049 varchar(255), @Col050 varchar(255), @Col051 varchar(255), @Col052 varchar(255), @Col053 varchar(255), @Col054 varchar(255), @Col055 varchar(255), @Col056 varchar(255), @Col057 varchar(255), @Col058 varchar(255), @Col059 varchar(255), @Col060 varchar(255), @Col061 varchar(255), @Col062 varchar(255), @Col063 varchar(255), @Col064 varchar(255), @Col065 varchar(255), @opt_id int, @counter as int DECLARE options_cursor CURSOR FOR SELECT Col002, Col031, Col032, Col033, Col034, Col035, Col036, Col037, Col038, Col039, Col040, Col041, Col042, Col043, Col044, Col045, Col046, Col047, Col048, Col049, Col050, Col051, Col052, Col053, Col054, Col055, Col056, Col057, Col058, Col059, Col060, Col061, Col062, Col063, Col064, Col065 FROMtmp_vehix OPEN options_cursor FETCH NEXT FROM options_cursor INTO @vin, @Col031, @Col032, @Col033, @Col034, @Col035, @Col036, @Col037,@Col038, @Col039, @Col040, @Col041, @Col042, @Col043, @Col044, @Col045, @Col046,@Col047, @Col048, @Col049, @Col050, @Col051, @Col052, @Col053, @Col054, @Col055,@Col056, @Col057, @Col058, @Col059, @Col060, @Col061, @Col062, @Col063, @Col064,@Col065 WHILE @@FETCH_STATUS = 0 BEGIN SET @counter = @counter + 1 IF LEN(@Col031) 0 BEGIN SET @opt_id = (SELECT option_id FROM options WHERE CAST(ga_option_id as varbinary(255)) = CAST(@Col031 as varbinary(255))) IF @opt_id IS NOT NULL BEGIN INSERT INTO vehix_options (vin, option_id) VALUES (@vin, @opt_id) END IF LEN(@Col032) 0 BEGIN SET @opt_id = (SELECT option_id FROM options WHERE CAST(ga_option_id as varbinary(255)) = CAST(@Col032 as varbinary(255))) IF @opt_id IS NOT NULL BEGIN INSERT INTO vehix_options (vin, option_id) VALUES (@vin, @opt_id) END IF LEN(@Col033) 0 BEGIN SET @opt_id = (SELECT option_id FROM options WHERE CAST(ga_option_id as varbinary(255)) = CAST(@Col033 as varbinary(255))) IF @opt_id IS NOT NULL BEGIN INSERT INTO vehix_options (vin, option_id)VALUES (@vin, @opt_id) END END END END FETCH NEXT FROM options_cursor INTO@vin, @Col031, @Col032, @Col033, @Col034, @Col035, @Col036, @Col037,@Col038, @Col039, @Col040, @Col041, @Col042, @Col043, @Col044, @Col045,
Re: OT - SQL Help
You ( ) around your OR statements Example: sql = SELECT * FROM SomeTable sql = sql WHERE (Email = ' Request(un) ' sql = sql OR SEmail = ' Request(un) ') sql = sql AND IsDeleted = 0 sql = sql AND Password = ' Request(pw) ' sql = sql AND Approved = 1 sql = sql AND Registered = 1 [EMAIL PROTECTED] 12/02/02 11:30AM Hi all, Sorry for the OT but this should be a quick answer. This is in ASP (sorry). If I have the following: sql = SELECT * FROM SomeTable sql = sql WHERE Email = ' Request(un) ' sql = sql OR SEmail = ' Request(un) ' sql = sql AND IsDeleted = 0 sql = sql AND Password = ' Request(pw) ' sql = sql AND Approved = 1 sql = sql AND Registered = 1 Where Email is the primary email and SEmail is a secondary email address (both are different addresses). If the submission doesn't match the first then it matches the second, how do I account for that? Right now, I am getting an error because the record doesn't match. Any ideas? Thanks, Tony ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: OT - SQL Help
I'm a bit confused - I'm not sure what you mean by the error you are getting. What scope are you trying to request the variable un from? Is it a form variable or url? Normally you'd access these using Request.Form(un) (for form vars) If they are local vars then just reference them as un. Not saying that what you have will cause errors - just not seen it written like that b4. -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: 02 December 2002 16:31 To: CF-Talk Subject: OT - SQL Help Hi all, Sorry for the OT but this should be a quick answer. This is in ASP (sorry). If I have the following: sql = SELECT * FROM SomeTable sql = sql WHERE Email = ' Request(un) ' sql = sql OR SEmail = ' Request(un) ' sql = sql AND IsDeleted = 0 sql = sql AND Password = ' Request(pw) ' sql = sql AND Approved = 1 sql = sql AND Registered = 1 Where Email is the primary email and SEmail is a secondary email address (both are different addresses). If the submission doesn't match the first then it matches the second, how do I account for that? Right now, I am getting an error because the record doesn't match. Any ideas? Thanks, Tony ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: OT - SQL Help
its using Structure notation. -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: 02 December 2002 16:46 To: CF-Talk Subject: RE: OT - SQL Help I'm a bit confused - I'm not sure what you mean by the error you are getting. What scope are you trying to request the variable un from? Is it a form variable or url? Normally you'd access these using Request.Form(un) (for form vars) If they are local vars then just reference them as un. Not saying that what you have will cause errors - just not seen it written like that b4. -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: 02 December 2002 16:31 To: CF-Talk Subject: OT - SQL Help Hi all, Sorry for the OT but this should be a quick answer. This is in ASP (sorry). If I have the following: sql = SELECT * FROM SomeTable sql = sql WHERE Email = ' Request(un) ' sql = sql OR SEmail = ' Request(un) ' sql = sql AND IsDeleted = 0 sql = sql AND Password = ' Request(pw) ' sql = sql AND Approved = 1 sql = sql AND Registered = 1 Where Email is the primary email and SEmail is a secondary email address (both are different addresses). If the submission doesn't match the first then it matches the second, how do I account for that? Right now, I am getting an error because the record doesn't match. Any ideas? Thanks, Tony ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: OT - SQL Help
No scope. It is written in ASP. -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 11:46 AM To: CF-Talk Subject: RE: OT - SQL Help I'm a bit confused - I'm not sure what you mean by the error you are getting. What scope are you trying to request the variable un from? Is it a form variable or url? Normally you'd access these using Request.Form(un) (for form vars) If they are local vars then just reference them as un. Not saying that what you have will cause errors - just not seen it written like that b4. -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: 02 December 2002 16:31 To: CF-Talk Subject: OT - SQL Help Hi all, Sorry for the OT but this should be a quick answer. This is in ASP (sorry). If I have the following: sql = SELECT * FROM SomeTable sql = sql WHERE Email = ' Request(un) ' sql = sql OR SEmail = ' Request(un) ' sql = sql AND IsDeleted = 0 sql = sql AND Password = ' Request(pw) ' sql = sql AND Approved = 1 sql = sql AND Registered = 1 Where Email is the primary email and SEmail is a secondary email address (both are different addresses). If the submission doesn't match the first then it matches the second, how do I account for that? Right now, I am getting an error because the record doesn't match. Any ideas? Thanks, Tony ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: OT - SQL Help
That is pretty funny, posting an SQL question wrapped in ASP on a Cold Fusion list. I have a question about using linked lists and malloc in C :) Rob http://treebeard.sourceforge.net http://ruinworld.sourceforge.net Scientia Est Potentia -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 9:25 AM To: CF-Talk Subject: RE: OT - SQL Help No scope. It is written in ASP. -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 11:46 AM To: CF-Talk Subject: RE: OT - SQL Help I'm a bit confused - I'm not sure what you mean by the error you are getting. What scope are you trying to request the variable un from? Is it a form variable or url? Normally you'd access these using Request.Form(un) (for form vars) If they are local vars then just reference them as un. Not saying that what you have will cause errors - just not seen it written like that b4. -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: 02 December 2002 16:31 To: CF-Talk Subject: OT - SQL Help Hi all, Sorry for the OT but this should be a quick answer. This is in ASP (sorry). If I have the following: sql = SELECT * FROM SomeTable sql = sql WHERE Email = ' Request(un) ' sql = sql OR SEmail = ' Request(un) ' sql = sql AND IsDeleted = 0 sql = sql AND Password = ' Request(pw) ' sql = sql AND Approved = 1 sql = sql AND Registered = 1 Where Email is the primary email and SEmail is a secondary email address (both are different addresses). If the submission doesn't match the first then it matches the second, how do I account for that? Right now, I am getting an error because the record doesn't match. Any ideas? Thanks, Tony ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: OT - SQL Help
Sorry - I wasn't particularly clear. What I meant to say was that the request object has a number of collections associated with it (form, querystring, etc...). When I have used the request object I have always referenced an element of one of these collections e,g, request.form(un). Not seen it as you had it but not to say it sdoesn't work! Besides, assuming you aren't getting an error (just no record returned) then I think that Randell's post gives you the answer. -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: 02 December 2002 17:25 To: CF-Talk Subject: RE: OT - SQL Help No scope. It is written in ASP. -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 11:46 AM To: CF-Talk Subject: RE: OT - SQL Help I'm a bit confused - I'm not sure what you mean by the error you are getting. What scope are you trying to request the variable un from? Is it a form variable or url? Normally you'd access these using Request.Form(un) (for form vars) If they are local vars then just reference them as un. Not saying that what you have will cause errors - just not seen it written like that b4. -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: 02 December 2002 16:31 To: CF-Talk Subject: OT - SQL Help Hi all, Sorry for the OT but this should be a quick answer. This is in ASP (sorry). If I have the following: sql = SELECT * FROM SomeTable sql = sql WHERE Email = ' Request(un) ' sql = sql OR SEmail = ' Request(un) ' sql = sql AND IsDeleted = 0 sql = sql AND Password = ' Request(pw) ' sql = sql AND Approved = 1 sql = sql AND Registered = 1 Where Email is the primary email and SEmail is a secondary email address (both are different addresses). If the submission doesn't match the first then it matches the second, how do I account for that? Right now, I am getting an error because the record doesn't match. Any ideas? Thanks, Tony ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: OT - SQL Help
Sorry - I wasn't particularly clear. What I meant to say was that the request object has a number of collections associated with it (form, querystring, etc...). When I have used the request object I have always referenced an element of one of these collections e,g, request.form(un). Not seen it as you had it but not to say it doesn't work! You can use either syntax in ASP. I've generally used the more explicit syntax, myself. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: OT - SQL Help
This and CFDJList are the only good lists that are out there where everyone is more than helpful and help/answers are extrememly quick. -Original Message- From: Rob Rohan [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 12:38 PM To: CF-Talk Subject: RE: OT - SQL Help That is pretty funny, posting an SQL question wrapped in ASP on a Cold Fusion list. I have a question about using linked lists and malloc in C :) Rob http://treebeard.sourceforge.net http://ruinworld.sourceforge.net Scientia Est Potentia -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 9:25 AM To: CF-Talk Subject: RE: OT - SQL Help No scope. It is written in ASP. -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 11:46 AM To: CF-Talk Subject: RE: OT - SQL Help I'm a bit confused - I'm not sure what you mean by the error you are getting. What scope are you trying to request the variable un from? Is it a form variable or url? Normally you'd access these using Request.Form(un) (for form vars) If they are local vars then just reference them as un. Not saying that what you have will cause errors - just not seen it written like that b4. -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: 02 December 2002 16:31 To: CF-Talk Subject: OT - SQL Help Hi all, Sorry for the OT but this should be a quick answer. This is in ASP (sorry). If I have the following: sql = SELECT * FROM SomeTable sql = sql WHERE Email = ' Request(un) ' sql = sql OR SEmail = ' Request(un) ' sql = sql AND IsDeleted = 0 sql = sql AND Password = ' Request(pw) ' sql = sql AND Approved = 1 sql = sql AND Registered = 1 Where Email is the primary email and SEmail is a secondary email address (both are different addresses). If the submission doesn't match the first then it matches the second, how do I account for that? Right now, I am getting an error because the record doesn't match. Any ideas? Thanks, Tony ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: OT - SQL Help
Dave's absolutely right. I am a lil' lazy. The less typing the better! 8^) -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 12:56 PM To: CF-Talk Subject: RE: OT - SQL Help Sorry - I wasn't particularly clear. What I meant to say was that the request object has a number of collections associated with it (form, querystring, etc...). When I have used the request object I have always referenced an element of one of these collections e,g, request.form(un). Not seen it as you had it but not to say it doesn't work! You can use either syntax in ASP. I've generally used the more explicit syntax, myself. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: OT - SQL Help
my ASP's not what it used to be! - so did Randells post sort it? -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: 02 December 2002 17:59 To: CF-Talk Subject: RE: OT - SQL Help Dave's absolutely right. I am a lil' lazy. The less typing the better! 8^) -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 12:56 PM To: CF-Talk Subject: RE: OT - SQL Help Sorry - I wasn't particularly clear. What I meant to say was that the request object has a number of collections associated with it (form, querystring, etc...). When I have used the request object I have always referenced an element of one of these collections e,g, request.form(un). Not seen it as you had it but not to say it doesn't work! You can use either syntax in ASP. I've generally used the more explicit syntax, myself. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: OT - SQL Help
No it did not. It's very bizarre. -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 1:04 PM To: CF-Talk Subject: RE: OT - SQL Help my ASP's not what it used to be! - so did Randells post sort it? -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: 02 December 2002 17:59 To: CF-Talk Subject: RE: OT - SQL Help Dave's absolutely right. I am a lil' lazy. The less typing the better! 8^) -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 12:56 PM To: CF-Talk Subject: RE: OT - SQL Help Sorry - I wasn't particularly clear. What I meant to say was that the request object has a number of collections associated with it (form, querystring, etc...). When I have used the request object I have always referenced an element of one of these collections e,g, request.form(un). Not seen it as you had it but not to say it doesn't work! You can use either syntax in ASP. I've generally used the more explicit syntax, myself. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: OT - SQL Help
What error are you getting? Matthew Small IT Supervisor Showstopper National Dance Competitions 3660 Old Kings Hwy Murrells Inlet, SC 29576 843-357-1847 http://www.showstopperonline.com -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:14 PM To: CF-Talk Subject: RE: OT - SQL Help No it did not. It's very bizarre. -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 1:04 PM To: CF-Talk Subject: RE: OT - SQL Help my ASP's not what it used to be! - so did Randells post sort it? -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: 02 December 2002 17:59 To: CF-Talk Subject: RE: OT - SQL Help Dave's absolutely right. I am a lil' lazy. The less typing the better! 8^) -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 12:56 PM To: CF-Talk Subject: RE: OT - SQL Help Sorry - I wasn't particularly clear. What I meant to say was that the request object has a number of collections associated with it (form, querystring, etc...). When I have used the request object I have always referenced an element of one of these collections e,g, request.form(un). Not seen it as you had it but not to say it doesn't work! You can use either syntax in ASP. I've generally used the more explicit syntax, myself. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: OT - SQL Help
What error are you getting? Matthew Small IT Supervisor Showstopper National Dance Competitions 3660 Old Kings Hwy Murrells Inlet, SC 29576 843-357-1847 http://www.showstopperonline.com -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:14 PM To: CF-Talk Subject: RE: OT - SQL Help No it did not. It's very bizarre. -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 1:04 PM To: CF-Talk Subject: RE: OT - SQL Help my ASP's not what it used to be! - so did Randells post sort it? -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: 02 December 2002 17:59 To: CF-Talk Subject: RE: OT - SQL Help Dave's absolutely right. I am a lil' lazy. The less typing the better! 8^) -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 12:56 PM To: CF-Talk Subject: RE: OT - SQL Help Sorry - I wasn't particularly clear. What I meant to say was that the request object has a number of collections associated with it (form, querystring, etc...). When I have used the request object I have always referenced an element of one of these collections e,g, request.form(un). Not seen it as you had it but not to say it doesn't work! You can use either syntax in ASP. I've generally used the more explicit syntax, myself. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: OT - SQL Help
Sorry for the OT but this should be a quick answer. This is in ASP (sorry). If I have the following: sql = SELECT * FROM SomeTable sql = sql WHERE Email = ' Request(un) ' sql = sql OR SEmail = ' Request(un) ' sql = sql AND IsDeleted = 0 sql = sql AND Password = ' Request(pw) ' sql = sql AND Approved = 1 sql = sql AND Registered = 1 Where Email is the primary email and SEmail is a secondary email address (both are different addresses). If the submission doesn't match the first then it matches the second, how do I account for that? Right now, I am getting an error because the record doesn't match. You shouldn't be getting an error if the record doesn't match, you should just not get any records back if none match. Randall's suggestion about using parentheses should take care of your stated problem (you want to compare the value to both fields, and if it matches either, you want to select the record, right?) Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: OT - SQL Help
I guess I must have not been clear in my initial email. I am not getting an error. The problem is I have 2 different email addresses that can be used to log into an extranet. If the primary email doesn't match the primary email address then it should check to see if it matches the secondary email address and log in otherwise it is not found. For example, let's say these fields are in the DB: PEmail,SEmail and let's say these are the email addresses respectively: [EMAIL PROTECTED],[EMAIL PROTECTED] And in the form I input [EMAIL PROTECTED] I should be able to log in but the problem is that it only accepts the primary email address. Hope this clears it up. Thanks, Tony -Original Message- From: Matthew Small [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:22 PM To: CF-Talk Subject: RE: OT - SQL Help What error are you getting? Matthew Small IT Supervisor Showstopper National Dance Competitions 3660 Old Kings Hwy Murrells Inlet, SC 29576 843-357-1847 http://www.showstopperonline.com -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:14 PM To: CF-Talk Subject: RE: OT - SQL Help No it did not. It's very bizarre. -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 1:04 PM To: CF-Talk Subject: RE: OT - SQL Help my ASP's not what it used to be! - so did Randells post sort it? -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: 02 December 2002 17:59 To: CF-Talk Subject: RE: OT - SQL Help Dave's absolutely right. I am a lil' lazy. The less typing the better! 8^) -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 12:56 PM To: CF-Talk Subject: RE: OT - SQL Help Sorry - I wasn't particularly clear. What I meant to say was that the request object has a number of collections associated with it (form, querystring, etc...). When I have used the request object I have always referenced an element of one of these collections e,g, request.form(un). Not seen it as you had it but not to say it doesn't work! You can use either syntax in ASP. I've generally used the more explicit syntax, myself. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: OT - SQL Help
Exactly!!! But it is not working that way. I just sent a previous email explaining this. -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:36 PM To: CF-Talk Subject: RE: OT - SQL Help Sorry for the OT but this should be a quick answer. This is in ASP (sorry). If I have the following: sql = SELECT * FROM SomeTable sql = sql WHERE Email = ' Request(un) ' sql = sql OR SEmail = ' Request(un) ' sql = sql AND IsDeleted = 0 sql = sql AND Password = ' Request(pw) ' sql = sql AND Approved = 1 sql = sql AND Registered = 1 Where Email is the primary email and SEmail is a secondary email address (both are different addresses). If the submission doesn't match the first then it matches the second, how do I account for that? Right now, I am getting an error because the record doesn't match. You shouldn't be getting an error if the record doesn't match, you should just not get any records back if none match. Randall's suggestion about using parentheses should take care of your stated problem (you want to compare the value to both fields, and if it matches either, you want to select the record, right?) Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: OT - SQL Help
You to see exactly what SQL Statement being sent to the db. How about displaying the sql string before it is processed and send that to the list. Matthew Small IT Supervisor Showstopper National Dance Competitions 3660 Old Kings Hwy Murrells Inlet, SC 29576 843-357-1847 http://www.showstopperonline.com -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:38 PM To: CF-Talk Subject: RE: OT - SQL Help Exactly!!! But it is not working that way. I just sent a previous email explaining this. -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:36 PM To: CF-Talk Subject: RE: OT - SQL Help Sorry for the OT but this should be a quick answer. This is in ASP (sorry). If I have the following: sql = SELECT * FROM SomeTable sql = sql WHERE Email = ' Request(un) ' sql = sql OR SEmail = ' Request(un) ' sql = sql AND IsDeleted = 0 sql = sql AND Password = ' Request(pw) ' sql = sql AND Approved = 1 sql = sql AND Registered = 1 Where Email is the primary email and SEmail is a secondary email address (both are different addresses). If the submission doesn't match the first then it matches the second, how do I account for that? Right now, I am getting an error because the record doesn't match. You shouldn't be getting an error if the record doesn't match, you should just not get any records back if none match. Randall's suggestion about using parentheses should take care of your stated problem (you want to compare the value to both fields, and if it matches either, you want to select the record, right?) Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: OT - SQL Help
I just removed all the asp stuff and this is what gets sent. SELECT * FROM SomeTable WHERE (Email = '[EMAIL PROTECTED]') OR (SEmail = '[EMAIL PROTECTED]') AND (IsDeleted = 0) AND (Password = 'somepassword') AND (Approved = 1) AND (Registered = 1) -Original Message- From: Matthew Small [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:48 PM To: CF-Talk Subject: RE: OT - SQL Help You to see exactly what SQL Statement being sent to the db. How about displaying the sql string before it is processed and send that to the list. Matthew Small IT Supervisor Showstopper National Dance Competitions 3660 Old Kings Hwy Murrells Inlet, SC 29576 843-357-1847 http://www.showstopperonline.com -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:38 PM To: CF-Talk Subject: RE: OT - SQL Help Exactly!!! But it is not working that way. I just sent a previous email explaining this. -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:36 PM To: CF-Talk Subject: RE: OT - SQL Help Sorry for the OT but this should be a quick answer. This is in ASP (sorry). If I have the following: sql = SELECT * FROM SomeTable sql = sql WHERE Email = ' Request(un) ' sql = sql OR SEmail = ' Request(un) ' sql = sql AND IsDeleted = 0 sql = sql AND Password = ' Request(pw) ' sql = sql AND Approved = 1 sql = sql AND Registered = 1 Where Email is the primary email and SEmail is a secondary email address (both are different addresses). If the submission doesn't match the first then it matches the second, how do I account for that? Right now, I am getting an error because the record doesn't match. You shouldn't be getting an error if the record doesn't match, you should just not get any records back if none match. Randall's suggestion about using parentheses should take care of your stated problem (you want to compare the value to both fields, and if it matches either, you want to select the record, right?) Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: OT - SQL Help
It looks like you need parentheses around your OR statement rather than individual qualifier statements. SELECT * FROM SomeTable WHERE (Email = '[EMAIL PROTECTED]' OR SEmail = '[EMAIL PROTECTED]') AND IsDeleted = 0 AND Password = 'somepassword' AND Approved = 1 AND Registered = 1 Matthew Small IT Supervisor Showstopper National Dance Competitions 3660 Old Kings Hwy Murrells Inlet, SC 29576 843-357-1847 http://www.showstopperonline.com -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:56 PM To: CF-Talk Subject: RE: OT - SQL Help I just removed all the asp stuff and this is what gets sent. SELECT * FROM SomeTable WHERE (Email = '[EMAIL PROTECTED]') OR (SEmail = '[EMAIL PROTECTED]') AND (IsDeleted = 0) AND (Password = 'somepassword') AND (Approved = 1) AND (Registered = 1) -Original Message- From: Matthew Small [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:48 PM To: CF-Talk Subject: RE: OT - SQL Help You to see exactly what SQL Statement being sent to the db. How about displaying the sql string before it is processed and send that to the list. Matthew Small IT Supervisor Showstopper National Dance Competitions 3660 Old Kings Hwy Murrells Inlet, SC 29576 843-357-1847 http://www.showstopperonline.com -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:38 PM To: CF-Talk Subject: RE: OT - SQL Help Exactly!!! But it is not working that way. I just sent a previous email explaining this. -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:36 PM To: CF-Talk Subject: RE: OT - SQL Help Sorry for the OT but this should be a quick answer. This is in ASP (sorry). If I have the following: sql = SELECT * FROM SomeTable sql = sql WHERE Email = ' Request(un) ' sql = sql OR SEmail = ' Request(un) ' sql = sql AND IsDeleted = 0 sql = sql AND Password = ' Request(pw) ' sql = sql AND Approved = 1 sql = sql AND Registered = 1 Where Email is the primary email and SEmail is a secondary email address (both are different addresses). If the submission doesn't match the first then it matches the second, how do I account for that? Right now, I am getting an error because the record doesn't match. You shouldn't be getting an error if the record doesn't match, you should just not get any records back if none match. Randall's suggestion about using parentheses should take care of your stated problem (you want to compare the value to both fields, and if it matches either, you want to select the record, right?) Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: OT - SQL Help
:) SELECT * FROM SomeTable WHERE (Email = '[EMAIL PROTECTED]' OR SEmail ='[EMAIL PROTECTED]') AND IsDeleted = 0 AND Password = 'somepassword' AND Approved = 1 AND Registered = 1 as I am sure everyone is going to say Rob http://treebeard.sourceforge.net http://ruinworld.sourceforge.net Scientia Est Potentia -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 11:56 AM To: CF-Talk Subject: RE: OT - SQL Help I just removed all the asp stuff and this is what gets sent. SELECT * FROM SomeTable WHERE (Email = '[EMAIL PROTECTED]') OR (SEmail = '[EMAIL PROTECTED]') AND (IsDeleted = 0) AND (Password = 'somepassword') AND (Approved = 1) AND (Registered = 1) -Original Message- From: Matthew Small [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:48 PM To: CF-Talk Subject: RE: OT - SQL Help You to see exactly what SQL Statement being sent to the db. How about displaying the sql string before it is processed and send that to the list. Matthew Small IT Supervisor Showstopper National Dance Competitions 3660 Old Kings Hwy Murrells Inlet, SC 29576 843-357-1847 http://www.showstopperonline.com -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:38 PM To: CF-Talk Subject: RE: OT - SQL Help Exactly!!! But it is not working that way. I just sent a previous email explaining this. -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:36 PM To: CF-Talk Subject: RE: OT - SQL Help Sorry for the OT but this should be a quick answer. This is in ASP (sorry). If I have the following: sql = SELECT * FROM SomeTable sql = sql WHERE Email = ' Request(un) ' sql = sql OR SEmail = ' Request(un) ' sql = sql AND IsDeleted = 0 sql = sql AND Password = ' Request(pw) ' sql = sql AND Approved = 1 sql = sql AND Registered = 1 Where Email is the primary email and SEmail is a secondary email address (both are different addresses). If the submission doesn't match the first then it matches the second, how do I account for that? Right now, I am getting an error because the record doesn't match. You shouldn't be getting an error if the record doesn't match, you should just not get any records back if none match. Randall's suggestion about using parentheses should take care of your stated problem (you want to compare the value to both fields, and if it matches either, you want to select the record, right?) Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: OT - SQL Help
ARGH! I figured it out... *lowering head* I forgot to change some code on an include page. DOH! Sorry folks! Thanks to everyone who helped. -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:56 PM To: CF-Talk Subject: RE: OT - SQL Help I just removed all the asp stuff and this is what gets sent. SELECT * FROM SomeTable WHERE (Email = '[EMAIL PROTECTED]') OR (SEmail = '[EMAIL PROTECTED]') AND (IsDeleted = 0) AND (Password = 'somepassword') AND (Approved = 1) AND (Registered = 1) -Original Message- From: Matthew Small [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:48 PM To: CF-Talk Subject: RE: OT - SQL Help You to see exactly what SQL Statement being sent to the db. How about displaying the sql string before it is processed and send that to the list. Matthew Small IT Supervisor Showstopper National Dance Competitions 3660 Old Kings Hwy Murrells Inlet, SC 29576 843-357-1847 http://www.showstopperonline.com -Original Message- From: Tony Carcieri [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:38 PM To: CF-Talk Subject: RE: OT - SQL Help Exactly!!! But it is not working that way. I just sent a previous email explaining this. -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Monday, December 02, 2002 2:36 PM To: CF-Talk Subject: RE: OT - SQL Help Sorry for the OT but this should be a quick answer. This is in ASP (sorry). If I have the following: sql = SELECT * FROM SomeTable sql = sql WHERE Email = ' Request(un) ' sql = sql OR SEmail = ' Request(un) ' sql = sql AND IsDeleted = 0 sql = sql AND Password = ' Request(pw) ' sql = sql AND Approved = 1 sql = sql AND Registered = 1 Where Email is the primary email and SEmail is a secondary email address (both are different addresses). If the submission doesn't match the first then it matches the second, how do I account for that? Right now, I am getting an error because the record doesn't match. You shouldn't be getting an error if the record doesn't match, you should just not get any records back if none match. Randall's suggestion about using parentheses should take care of your stated problem (you want to compare the value to both fields, and if it matches either, you want to select the record, right?) Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
