Re: SQL Query Problem
*thumbs up* On Tue, Jun 21, 2011 at 7:27 PM, Jenny Gavin-Wear < jenn...@fasttrackonline.co.uk> wrote: > > Hi Michael, > > The (very old) web site is about to be completely redeveloped, so I'm > really > not too worried. > > Appreciate your concern though :) > > Jenny > > >>-Original Message- > >>From: Michael Grant [mailto:mgr...@modus.bz] > >>Sent: 21 June 2011 23:27 > >>To: cf-talk > >>Subject: Re: SQL Query Problem > >> > >> > >> > >>Right, but if that table grows and columns are added, the overhead on > your > >>query will grow. Having a small table makes it even easier to define the > >>columns in your select list. And, if you have any dynamic code that > relies > >>on your column list it will likely break if you end up adding > >>columns to the > >>table. Or if you have to hand off your code to another developer > >>they can't > >>simply look at your query statement and glean what's being > >>returned without > >>having access to the db or without dumping the query.columnList. There's > >>just so many reasons why defining your column list is a good > >>idea, and none > >>where not defining it is. > >> > >>Not trying to preach, but this bit of "convenience" is just so > unnecessary > >>and has such potential for problems that it's not even worth > >>considering in > >>my opinion. It's just such a horrible habit. > >> > >>Anyway, as you were. :) > >> > >> > >> > >> > >> > >>On Tue, Jun 21, 2011 at 5:15 PM, Jenny Gavin-Wear < > >>jenn...@fasttrackonline.co.uk> wrote: > >> > >>> > >>> I was waiting for a comment on that. > >>> > >>> It's a very small table :) > >>> > >>> >>-Original Message- > >>> >>From: Michael Grant [mailto:mgr...@modus.bz] > >>> >>Sent: 21 June 2011 19:46 > >>> >>To: cf-talk > >>> >>Subject: Re: SQL Query Problem > >>> >> > >>> >> > >>> >> > >>> >>Off topic, but the "Select *" made me shudder. > >>> >> > >>> >> > >>> > >>> > >>> > >>> > >> > >> > > ~| 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:345512 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Query Problem
Hi Michael, The (very old) web site is about to be completely redeveloped, so I'm really not too worried. Appreciate your concern though :) Jenny >>-Original Message- >>From: Michael Grant [mailto:mgr...@modus.bz] >>Sent: 21 June 2011 23:27 >>To: cf-talk >>Subject: Re: SQL Query Problem >> >> >> >>Right, but if that table grows and columns are added, the overhead on your >>query will grow. Having a small table makes it even easier to define the >>columns in your select list. And, if you have any dynamic code that relies >>on your column list it will likely break if you end up adding >>columns to the >>table. Or if you have to hand off your code to another developer >>they can't >>simply look at your query statement and glean what's being >>returned without >>having access to the db or without dumping the query.columnList. There's >>just so many reasons why defining your column list is a good >>idea, and none >>where not defining it is. >> >>Not trying to preach, but this bit of "convenience" is just so unnecessary >>and has such potential for problems that it's not even worth >>considering in >>my opinion. It's just such a horrible habit. >> >>Anyway, as you were. :) >> >> >> >> >> >>On Tue, Jun 21, 2011 at 5:15 PM, Jenny Gavin-Wear < >>jenn...@fasttrackonline.co.uk> wrote: >> >>> >>> I was waiting for a comment on that. >>> >>> It's a very small table :) >>> >>> >>-Original Message- >>> >>From: Michael Grant [mailto:mgr...@modus.bz] >>> >>Sent: 21 June 2011 19:46 >>> >>To: cf-talk >>> >>Subject: Re: SQL Query Problem >>> >> >>> >> >>> >> >>> >>Off topic, but the "Select *" made me shudder. >>> >> >>> >> >>> >>> >>> >>> >> >> ~| 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:345511 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Query Problem
Right, but if that table grows and columns are added, the overhead on your query will grow. Having a small table makes it even easier to define the columns in your select list. And, if you have any dynamic code that relies on your column list it will likely break if you end up adding columns to the table. Or if you have to hand off your code to another developer they can't simply look at your query statement and glean what's being returned without having access to the db or without dumping the query.columnList. There's just so many reasons why defining your column list is a good idea, and none where not defining it is. Not trying to preach, but this bit of "convenience" is just so unnecessary and has such potential for problems that it's not even worth considering in my opinion. It's just such a horrible habit. Anyway, as you were. :) On Tue, Jun 21, 2011 at 5:15 PM, Jenny Gavin-Wear < jenn...@fasttrackonline.co.uk> wrote: > > I was waiting for a comment on that. > > It's a very small table :) > > >>-Original Message- > >>From: Michael Grant [mailto:mgr...@modus.bz] > >>Sent: 21 June 2011 19:46 > >>To: cf-talk > >>Subject: Re: SQL Query Problem > >> > >> > >> > >>Off topic, but the "Select *" made me shudder. > >> > >> > > > > ~| 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:345510 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Query Problem
I was waiting for a comment on that. It's a very small table :) >>-Original Message- >>From: Michael Grant [mailto:mgr...@modus.bz] >>Sent: 21 June 2011 19:46 >>To: cf-talk >>Subject: Re: SQL Query Problem >> >> >> >>Off topic, but the "Select *" made me shudder. >> >> ~| 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:345507 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Query Problem
She didn't provide column names... On Tue, Jun 21, 2011 at 1:45 PM, Michael Grant wrote: > > Off topic, but the "Select *" made me shudder. > > > On Tue, Jun 21, 2011 at 2:25 PM, Jenny Gavin-Wear < > jenn...@fasttrackonline.co.uk> wrote: > > > > > Looks like I went with the vote, lol > > > > Many thanks for all replies, and fast too :) > > > > Some payments from Paypal transactions, some manually entered on > profiles. > > Legacy code :/ > > > > Jenny > > > > select * from tbl_members > > where > > (datepart(m,paid) = #session.month# and datepart(,paid) = > > #session.year# > > AND > > memberID not in > > (select memberID from tbl_paypal > > where datepart(m, payment_date) = #session.month# and > > datepart(,payment_date) = #session.year# > > )) > > order by paid > > > > > > > > > > > > ~| 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:345503 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Query Problem
+420 On Tue, Jun 21, 2011 at 2:45 PM, Michael Grant wrote: > > Off topic, but the "Select *" made me shudder. > > > On Tue, Jun 21, 2011 at 2:25 PM, Jenny Gavin-Wear < > jenn...@fasttrackonline.co.uk> wrote: > >> >> Looks like I went with the vote, lol >> >> Many thanks for all replies, and fast too :) >> >> Some payments from Paypal transactions, some manually entered on profiles. >> Legacy code :/ >> >> Jenny >> >> select * from tbl_members >> where >> (datepart(m,paid) = #session.month# and datepart(,paid) = >> #session.year# >> AND >> memberID not in >> (select memberID from tbl_paypal >> where datepart(m, payment_date) = #session.month# and >> datepart(,payment_date) = #session.year# >> )) >> order by paid >> >> >> >> >> > > ~| 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:345502 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Query Problem
Off topic, but the "Select *" made me shudder. On Tue, Jun 21, 2011 at 2:25 PM, Jenny Gavin-Wear < jenn...@fasttrackonline.co.uk> wrote: > > Looks like I went with the vote, lol > > Many thanks for all replies, and fast too :) > > Some payments from Paypal transactions, some manually entered on profiles. > Legacy code :/ > > Jenny > > select * from tbl_members > where > (datepart(m,paid) = #session.month# and datepart(,paid) = > #session.year# > AND > memberID not in > (select memberID from tbl_paypal > where datepart(m, payment_date) = #session.month# and > datepart(,payment_date) = #session.year# > )) > order by paid > > > > > ~| 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:345501 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Query Problem
Looks like I went with the vote, lol Many thanks for all replies, and fast too :) Some payments from Paypal transactions, some manually entered on profiles. Legacy code :/ Jenny select * from tbl_members where (datepart(m,paid) = #session.month# and datepart(,paid) = #session.year# AND memberID not in (select memberID from tbl_paypal where datepart(m, payment_date) = #session.month# and datepart(,payment_date) = #session.year# )) order by paid ~| 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:345500 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Query Problem
That looks familiar! :-) On Tue, Jun 21, 2011 at 1:09 PM, Stephane Vantroyen wrote: > > I would do it this way : > > select b.* > from b > where b.id not in (select a.id from a) > > > > >How about: > > > >select b.* > >from b > >left outer join a on b.id = a.id > >where a.id is null > > > >Carl > > > >On 6/21/2011 10:37 AM, Jenny Gavin-Wear wrote: > >> > > ~| 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:345499 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Query Problem
I would do it this way : select b.* from b where b.id not in (select a.id from a) >How about: > >select b.* >from b >left outer join a on b.id = a.id >where a.id is null > >Carl > >On 6/21/2011 10:37 AM, Jenny Gavin-Wear wrote: >> ~| 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:345498 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Query Problem
How about: select b.* from b left outer join a on b.id = a.id where a.id is null Carl On 6/21/2011 10:37 AM, Jenny Gavin-Wear wrote: > Two tables each containing a shared primary key ID. > > I am trying to create a query that lists records from table B that are not > in table A. > > Many thanks, > > Jenny > > > > > ~| 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:345497 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Query Problem
Thanks John and Greg :) >>-Original Message- >>From: Greg Morphis [mailto:gmorp...@gmail.com] >>Sent: 21 June 2011 18:45 >>To: cf-talk >>Subject: Re: SQL Query Problem >> >> >> >>if your tables are large, you'll probably see a better performance from >>select id from TableA a >>where not exists >>(select 1 from TableB b >>where a.id = b.id) >> >> >>On Tue, Jun 21, 2011 at 12:41 PM, John M Bliss >> wrote: >>> >>> select * from b where id not in (select id from a) >>> >>> On Tue, Jun 21, 2011 at 12:37 PM, Jenny Gavin-Wear < >>> jenn...@fasttrackonline.co.uk> wrote: >>> >>>> >>>> Two tables each containing a shared primary key ID. >>>> >>>> I am trying to create a query that lists records from table B >>that are not >>>> in table A. >>>> >>>> Many thanks, >>>> >>>> Jenny >>>> >>>> >>>> >>>> >>>> >>> >>> >> >> ~| 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:345496 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Query Problem
if your tables are large, you'll probably see a better performance from select id from TableA a where not exists (select 1 from TableB b where a.id = b.id) On Tue, Jun 21, 2011 at 12:41 PM, John M Bliss wrote: > > select * from b where id not in (select id from a) > > On Tue, Jun 21, 2011 at 12:37 PM, Jenny Gavin-Wear < > jenn...@fasttrackonline.co.uk> wrote: > >> >> Two tables each containing a shared primary key ID. >> >> I am trying to create a query that lists records from table B that are not >> in table A. >> >> Many thanks, >> >> Jenny >> >> >> >> >> > > ~| 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:345495 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Query Problem
select * from b where id not in (select id from a) On Tue, Jun 21, 2011 at 12:37 PM, Jenny Gavin-Wear < jenn...@fasttrackonline.co.uk> wrote: > > Two tables each containing a shared primary key ID. > > I am trying to create a query that lists records from table B that are not > in table A. > > Many thanks, > > Jenny > > > > > ~| 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:345494 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Query Problem
>>My problem is with is piece of code in QueryB. It is creating an ODBC error. If your query contains quotes, you have to use #preserveSingleQuotes(SQLQUERY)# in your query "B" -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query Problem
post the error [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Query problem - SOLVED
Marc, YES! - that worked - thank you thank you many times for helping me. mARK _ From: Marc Lowe [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 4:14 PM To: CF-Talk Subject: Re: SQL Query problem This should work.. I hope Lets get away from the idea of an aggregate and just use a subquery then SELECT m.memberID, m.firstname, m.lastname, t.transactionID as maxTransactionID, t.paidthru as paidthru FROM members m INNER JOIN trans t ON m.memberID = t.memberID WHERE transactionID = (SELECT MAX(transactionID) from trans where memberID = m.memberID) _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Query problem - SOLVED
Marc, YES! - that worked - thank you thank you many times for helping me. mARK _ From: Marc Lowe [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 4:14 PM To: CF-Talk Subject: Re: SQL Query problem This should work.. I hope Lets get away from the idea of an aggregate and just use a subquery then SELECT m.memberID, m.firstname, m.lastname, t.transactionID as maxTransactionID, t.paidthru as paidthru FROM members m INNER JOIN trans t ON m.memberID = t.memberID WHERE transactionID = (SELECT MAX(transactionID) from trans where memberID = m.memberID) _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query problem
This should work.. I hope Lets get away from the idea of an aggregate and just use a subquery then SELECT m.memberID, m.firstname, m.lastname, t.transactionID as maxTransactionID, t.paidthru as paidthru FROM members m INNER JOIN trans t ON m.memberID = t.memberID WHERE transactionID = (SELECT MAX(transactionID) from trans where memberID = m.memberID) [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query problem
I know why you are getting the results you are getting but I do not have enough information regarding your data to provide a solution yet. If you add t.paidthru in your group by clause then it will get multiple entries (a cross-join), but if you leave it out you will get invalid group by errors. Questions that I have for you are: What fields do you need from the transaction table? Will the paidthru field always be the latest date? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Query problem
Still didn't work, returning multiple transactions for any given individual. I clipped this from the results (there should only be one individual and the paid thru should be 7/31/2004, which is the newest (highest) transactionID memberlevelID | email | firstname | lastname | paidthru M | [EMAIL PROTECTED] | Andy Granger | 07/31/2004 M | [EMAIL PROTECTED] | Andy Granger | 12/31/2004 Here's how I posted it: SELECT M.memberID, M.firstName, M.middleName, M.lastName, M.email, M.company, M.city, M.zip, M.memberLevelID, MAX(T.transactionID) as maxID, T.paidThru FROM #REQUEST.prefix#_Members_List M INNER JOIN #REQUEST.prefix#_Members_TransactionLog T ON t.memberID = m.memberID WHERE ... GROUP BY M.memberID, M.firstName, M.middleName, M.lastName, M.email, M.company, M.city, M.zip, M.memberLevelID, T.paidThru Thanks for hanging in there with me on this one... _ From: Mark Leder [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 1:09 PM To: CF-Talk Subject: RE: SQL Query problem I'll give this a try and let you know. Thanks for your response. _ From: Marc Lowe [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 12:54 PM To: CF-Talk Subject: Re: SQL Query problem I left a lot of your query out but you should be able to look at this and see the biggest differences. SELECT m.memberID, m.firstname, m.lastname, MAX(t.transactionID) as maxID FROM members m INNER JOIN trans t ON t.memberID = m.memberID GROUP BY m.memberID, m.firstname, m.lastname Hope that helps, Marc _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Query problem
I'll give this a try and let you know. Thanks for your response. _ From: Marc Lowe [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 12:54 PM To: CF-Talk Subject: Re: SQL Query problem I left a lot of your query out but you should be able to look at this and see the biggest differences. SELECT m.memberID, m.firstname, m.lastname, MAX(t.transactionID) as maxID FROM members m INNER JOIN trans t ON t.memberID = m.memberID GROUP BY m.memberID, m.firstname, m.lastname Hope that helps, Marc _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Query problem
??? _ From: Tangorre, Michael [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 12:58 PM To: CF-Talk Subject: RE: SQL Query problem All the time you spent searching different solutions you could have fixed the table strcuture and rewrote the code 5x over. Suck it up Gel :-) Take the high road. Michael T. Tangorre _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Query problem
Sorry about that. Wrong list!!! That was suppose to go to cf-community. :-) Michael T. Tangorre > All the time you spent searching different solutions you > could have fixed the table strcuture and rewrote the code 5x > over. Suck it up Gel > :-) Take the high road. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Query problem
All the time you spent searching different solutions you could have fixed the table strcuture and rewrote the code 5x over. Suck it up Gel :-) Take the high road. Michael T. Tangorre [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query problem
I left a lot of your query out but you should be able to look at this and see the biggest differences. SELECT m.memberID, m.firstname, m.lastname, MAX(t.transactionID) as maxID FROM members m INNER JOIN trans t ON t.memberID = m.memberID GROUP BY m.memberID, m.firstname, m.lastname Hope that helps, Marc [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Query problem
Will this work ? Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'WHERE'. The error occurred in C:\Inetpub\REIAColumbus\admin\members\searchFunction\searchOneOfTwo.cfm: line 198 Called from C:\Inetpub\REIAColumbus\admin\members\main.cfm: line 29 Called from C:\Inetpub\REIAColumbus\admin\members\searchFunction\searchOneOfTwo.cfm: line 198 Called from C:\Inetpub\REIAColumbus\admin\members\main.cfm: line 29 196 : AND T.transactionDate <= #CreateODBCDate(SESSION.memberList.DateThru)# 197 : 198 : 199 : 200 : GROUP BY M.memberID, M.firstName, M.middleName, M.lastName, M.email, M.company, M.city, M.zip, M.memberLevelID, T.paidThru, T.transactionDate, T.memberID SQL SELECT M.firstName, M.middleName, M.lastName, M.email, M.company, M.city, M.zip, M.memberID, M.memberLevelID FROM reiacolumbus_Members_List M WHERE M.memberID = (SELECT TOP 1 T.transactionID, T.paidThru, T.transactionDate, T.memberID FROM reiacolumbus_Members_TransactionLog T) AND WHERE (M.firstName LIKE '%%' OR M.lastName LIKE '%%' OR M.email LIKE '%%' OR M.company LIKE '%%' OR M.city LIKE '%%' OR M.zip LIKE '%%' ) GROUP BY M.memberID, M.firstName, M.middleName, M.lastName, M.email, M.company, M.city, M.zip, M.memberLevelID, T.paidThru, T.transactionDate, T.memberID DATASOURCE dedb49 VENDORERRORCODE 156 SQLSTATE HY000 _ From: Alexander Sherwood [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 12:15 PM To: CF-Talk Subject: RE: SQL Query problem At 12:05 PM 8/18/2004, you wrote: >OK, a couple of things: >1) Below is the complete code for the query page. SorryI meant the actual SQL code that gets passed to the DB. CF will display the actual, parsed SQL code in the debugging output. Could you post this? Thanks! -- Alex _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Query problem
At 12:05 PM 8/18/2004, you wrote: >OK, a couple of things: >1) Below is the complete code for the query page. SorryI meant the actual SQL code that gets passed to the DB. CF will display the actual, parsed SQL code in the debugging output. Could you post this? Thanks! -- Alex [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Query problem
OK, a couple of things: 1) Below is the complete code for the query page. 2) Go to www.markleder.com/query.cfm There are two results screenshots: Shot 1 = the results from this query Shot 2 = a detail zoom for one of the members (this is included for clarification), in this case, I'm getting duplicates, I only want to show the most resent transactionID (eg, transaction date), not paid thru date. The most recent transaction ID is the one with the paid thru as 7/31/2004, not 12/31/2004. So, on Shot 1, the query results should show the paid through for this individual as 7/31/2004, not 12/31/2004 as what is occuring now HTH === default="containing"> username="#REQUEST.dsnUID#" password="#REQUEST.dsnPWD#" CACHEDWITHIN="#CreateTimeSpan(0,0,15,0)#"> SELECT M.firstName, M.middleName, M.lastName, M.email, M.company, M.city, M.zip, M.memberID, M.memberLevelID FROM #REQUEST.prefix#_Members_List M WHERE M.memberID = (SELECT TOP 1 T.transactionID, T.paidThru, T.transactionDate, T.memberID FROM #REQUEST.prefix#_Members_TransactionLog T) AND (M.firstName "containing">LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' OR M.lastName "containing">LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' OR M.email LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' OR M.company LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' OR M.city LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' OR M.zip LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#') AND M.firstname "containing">LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' AND M.lastname "containing">LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' AND M.email LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' AND M.company "containing">LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' AND M.city LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' AND M.zip LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' AND '#SESSION.memberList.FilterC#' = M.memberLevelID AND T.paidThru >= #CreateODBCDate(SESSION.memberList.DateFrom)# AND T.paidThru <= #CreateODBCDate(SESSION.memberList.DateThru)# AND T.transactionDate >= #CreateODBCDate(SESSION.memberList.DateFrom)# AND T.transactionDate <= #CreateODBCDate(SESSION.memberList.DateThru)# GROUP BY M.memberID, M.firstName, M.middleName, M.lastName, M.email, M.company, M.city, M.zip, M.memberLevelID, T.paidThru, T.transactionDate, T.memberID _ From: Alexander Sherwood [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 11:20 AM To: CF-Talk Subject: RE: SQL Query problem At 11:08 AM 8/18/2004, you wrote: >OK, you asked for it :o) -- the select statement was my latest try at >retrieving just the highest numbered transaction ID and corresponding data >for each member. FYI - the filtering statements are only invoked after >someone does a new search from a form. The inital results, which is what I >am after, uses the . The default URL.sortorder >is "M.lastname" and URL.sortDir is "ASC". > > >Thanks for helping me with this. Can you post the actual, parsed SQL that is being executed against your DB? This will be a little easier to troubleshoot with all of the variables and such filled in... Thanks! -- Ale _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Query problem
At 11:08 AM 8/18/2004, you wrote: >OK, you asked for it :o) -- the select statement was my latest try at >retrieving just the highest numbered transaction ID and corresponding data >for each member. FYI - the filtering statements are only invoked after >someone does a new search from a form. The inital results, which is what I >am after, uses the . The default URL.sortorder >is "M.lastname" and URL.sortDir is "ASC". > > >Thanks for helping me with this. Can you post the actual, parsed SQL that is being executed against your DB? This will be a little easier to troubleshoot with all of the variables and such filled in... Thanks! -- Ale [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Query problem
OK, you asked for it :o) -- the select statement was my latest try at retrieving just the highest numbered transaction ID and corresponding data for each member. FYI - the filtering statements are only invoked after someone does a new search from a form. The inital results, which is what I am after, uses the . The default URL.sortorder is "M.lastname" and URL.sortDir is "ASC". Thanks for helping me with this. === username="#REQUEST.dsnUID#" password="#REQUEST.dsnPWD#" CACHEDWITHIN="#CreateTimeSpan(0,0,15,0)#"> SELECT M.firstName, M.middleName, M.lastName, M.email, M.company, M.city, M.zip, M.memberID, M.memberLevelID FROM #REQUEST.prefix#_Members_List M WHERE M.memberID = (SELECT TOP 1 T.transactionID, T.paidThru, T.transactionDate, T.memberID FROM #REQUEST.prefix#_Members_TransactionLog T) AND (M.firstName "containing">LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' OR M.lastName "containing">LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' OR M.email LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' OR M.company LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' OR M.city LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' OR M.zip LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#') AND M.firstname "containing">LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' AND M.lastname "containing">LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' AND M.email LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' AND M.company "containing">LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' AND M.city LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' AND M.zip LIKE '%#SESSION.memberList.ffText#%' with">LIKE '#SESSION.memberList.ffText#%' = '#SESSION.memberList.ffText#' AND '#SESSION.memberList.FilterC#' = M.memberLevelID AND T.paidThru >= #CreateODBCDate(SESSION.memberList.DateFrom)# AND T.paidThru <= #CreateODBCDate(SESSION.memberList.DateThru)# AND T.transactionDate >= #CreateODBCDate(SESSION.memberList.DateFrom)# AND T.transactionDate <= #CreateODBCDate(SESSION.memberList.DateThru)# GROUP BY M.memberID, M.firstName, M.middleName, M.lastName, M.email, M.company, M.city, M.zip, M.memberLevelID, T.paidThru, T.transactionDate, T.memberID _ From: Alexander Sherwood [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 10:23 AM To: CF-Talk Subject: RE: SQL Query problem At 10:14 AM 8/18/2004, you wrote: >MemberID from the members table corresponds to the memberID in the >transaction table. So I must be writing the join wrong? Seems that way. Post the whole query, just as you have it the code. -- Alex _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Query problem
At 10:14 AM 8/18/2004, you wrote: >MemberID from the members table corresponds to the memberID in the >transaction table. So I must be writing the join wrong? Seems that way. Post the whole query, just as you have it the code. -- Alex [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Query problem
MemberID from the members table corresponds to the memberID in the transaction table. So I must be writing the join wrong? _ From: Alexander Sherwood [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 10:02 AM To: CF-Talk Subject: Re: SQL Query problem At 10:00 AM 8/18/2004, you wrote: >This is giving me fits. In a membership listing, each member could have >many transactions (one to many relationship). I want to retrieve each member >ID, and their corresponding most recent (MAX) transactionID. The memberID >joins the two tables. > >I've tried several ways without success. The statement below below returns >ALL the transaction records for any given individual member, not just the >most recent transactionID. What am I doing wrong? > >SELECT M.firstName, M.middleName, M.lastName, M.email, M.company, M.city, >M.zip, M.memberID, M.memberLevelID >FROM #REQUEST.prefix#_Members_List M >WHERE M.memberID = Looks like you're trying to join a MemberID on a TransactionID? >(SELECT MAX(T.transactionID) AS transactionID, T.paidThru, >T.transactionDate, T.memberID) >FROM #REQUEST.prefix#_Members_TransactionLog T >WHERE T.memberID = M.memberID) AND > >...more filters ... > >GROUP BY M.memberID, M.firstName, M.middleName, M.lastName, M.email, >M.company, M.city, M.zip, M.memberLevelID, T.paidThru, T.transactionDate, >T.memberID _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query problem
At 10:00 AM 8/18/2004, you wrote: >This is giving me fits. In a membership listing, each member could have >many transactions (one to many relationship). I want to retrieve each member >ID, and their corresponding most recent (MAX) transactionID. The memberID >joins the two tables. > >I've tried several ways without success. The statement below below returns >ALL the transaction records for any given individual member, not just the >most recent transactionID. What am I doing wrong? > >SELECT M.firstName, M.middleName, M.lastName, M.email, M.company, M.city, >M.zip, M.memberID, M.memberLevelID >FROM #REQUEST.prefix#_Members_List M >WHERE M.memberID = Looks like you're trying to join a MemberID on a TransactionID? >(SELECT MAX(T.transactionID) AS transactionID, T.paidThru, >T.transactionDate, T.memberID) >FROM #REQUEST.prefix#_Members_TransactionLog T >WHERE T.memberID = M.memberID) AND > >...more filters ... > >GROUP BY M.memberID, M.firstName, M.middleName, M.lastName, M.email, >M.company, M.city, M.zip, M.memberLevelID, T.paidThru, T.transactionDate, >T.memberID [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Query Problem
I didn't check out the table design. He is using 0's. I thought he was using nulls instead of 0's. My bad. Steven D Dworman - Web Consultant Systems Administrator ComSpec International - http://www.comspec-intnl.com phone: 248.647.8841 cell: 734.972.9676 - EMPOWER-XL ***Software for Higher Education*** http://www.empower-xl.com -Original Message- From: Dave Carabetta [mailto:[EMAIL PROTECTED]] Sent: Friday, November 16, 2001 9:55 AM To: CF-Talk Subject: RE: SQL Query Problem No, you would not want to use isnull(BrandID,0). This would cause the number of IDs by which to divide the price total to be incorrect. For example, if you have 10 brands, but 2 of them have NULL fields, the total price should be divided by 8. However, by your proposed method, substituting a 0 (which is what the isnull function would do) would cause the total price to be divided by 10 because technically there is a value for that brand now, hence skewing your average. I would definitely use an IS NOT NULL clause to filter out the wrong fields. Be very careful about this, as I have seen that mistake before, and it wreaks havoc! Dave. Original Message Follows From: "Steven Dworman" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: CF-Talk <[EMAIL PROTECTED]> Subject: RE: SQL Query Problem Date: Fri, 16 Nov 2001 08:13:37 -0500 Can't you just use isnull(BrandID,0)? Then you'll have 0's replacing null values, and your average won't be messed up. Steven D Dworman - Web Consultant Systems Administrator ComSpec International - http://www.comspec-intnl.com phone: 248.647.8841 cell: 734.972.9676 - EMPOWER-XL ***Software for Higher Education*** http://www.empower-xl.com -Original Message- From: Bryan Stevenson [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 15, 2001 5:49 PM To: CF-Talk Subject: Re: SQL Query Problem I'd add in some AND "WHATEVER BrandIF Field" IS NOT NULL to weed out the NULL records Bryan Stevenson VP & Director of E-Commerce Development Electric Edge Systems Group Inc. p. 250.920.8830 e. [EMAIL PROTECTED] - Allaire Alliance Partner www.allaire.com - Original Message - From: "Brian Ferrigno" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, November 15, 2001 2:39 PM Subject: SQL Query Problem > I am having a problem trying to think of the correct SQL statement I need. > It's late in the day and my brain is completely fried. > > What I am tring to do is get the average price of one column based a an ID > that is passed to the SQL query. For example if a BrandID of 1 is passed > into the query it should output an average price of $4.00 > > The problem arises when one of the BrandID(BNID) columns doesn't contain any > of the #ID# numbers in it. The value that it returns is NULL which causes > the rest of the SQL statement to return NULL as a result even if the other > columns contain at least one #ID# in the BNID column. For example passing a > BrandID of 24 will return a resultset of NULL because it is not in the > second or third BRANDID column. > > Hopefully I made some sense. Below is the partial table design and SQL > statements I am using in my script. Any help would be great. > > > TABLE DESIGN > BNID1_3 Price1_3 BNID2_3 Price2_3 BNID3_3 Price3_3 > 1 $2.0010 $0.001 $5.00 > 24 $0.001 $5.0023 $0.00 > > SQL STATEMENT > select > (select avg(Price1_3) from temp1 where BNID1_3=#ID#)+ > (select avg(Price2_3) from temp1 where BNID2_3=#ID#)+ > (select avg(Price3_3) from temp1 where BNID3_3=#ID#) AS AVGPRICE > > > > ~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Query Problem
No, you would not want to use isnull(BrandID,0). This would cause the number of IDs by which to divide the price total to be incorrect. For example, if you have 10 brands, but 2 of them have NULL fields, the total price should be divided by 8. However, by your proposed method, substituting a 0 (which is what the isnull function would do) would cause the total price to be divided by 10 because technically there is a value for that brand now, hence skewing your average. I would definitely use an IS NOT NULL clause to filter out the wrong fields. Be very careful about this, as I have seen that mistake before, and it wreaks havoc! Dave. Original Message Follows From: "Steven Dworman" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: CF-Talk <[EMAIL PROTECTED]> Subject: RE: SQL Query Problem Date: Fri, 16 Nov 2001 08:13:37 -0500 Can't you just use isnull(BrandID,0)? Then you'll have 0's replacing null values, and your average won't be messed up. Steven D Dworman - Web Consultant Systems Administrator ComSpec International - http://www.comspec-intnl.com phone: 248.647.8841 cell: 734.972.9676 - EMPOWER-XL ***Software for Higher Education*** http://www.empower-xl.com -Original Message- From: Bryan Stevenson [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 15, 2001 5:49 PM To: CF-Talk Subject: Re: SQL Query Problem I'd add in some AND "WHATEVER BrandIF Field" IS NOT NULL to weed out the NULL records Bryan Stevenson VP & Director of E-Commerce Development Electric Edge Systems Group Inc. p. 250.920.8830 e. [EMAIL PROTECTED] - Allaire Alliance Partner www.allaire.com - Original Message - From: "Brian Ferrigno" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, November 15, 2001 2:39 PM Subject: SQL Query Problem > I am having a problem trying to think of the correct SQL statement I need. > It's late in the day and my brain is completely fried. > > What I am tring to do is get the average price of one column based a an ID > that is passed to the SQL query. For example if a BrandID of 1 is passed > into the query it should output an average price of $4.00 > > The problem arises when one of the BrandID(BNID) columns doesn't contain any > of the #ID# numbers in it. The value that it returns is NULL which causes > the rest of the SQL statement to return NULL as a result even if the other > columns contain at least one #ID# in the BNID column. For example passing a > BrandID of 24 will return a resultset of NULL because it is not in the > second or third BRANDID column. > > Hopefully I made some sense. Below is the partial table design and SQL > statements I am using in my script. Any help would be great. > > > TABLE DESIGN > BNID1_3 Price1_3 BNID2_3 Price2_3 BNID3_3 Price3_3 > 1 $2.0010 $0.001 $5.00 > 24 $0.001 $5.0023 $0.00 > > SQL STATEMENT > select > (select avg(Price1_3) from temp1 where BNID1_3=#ID#)+ > (select avg(Price2_3) from temp1 where BNID2_3=#ID#)+ > (select avg(Price3_3) from temp1 where BNID3_3=#ID#) AS AVGPRICE > > > > ~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Query Problem
Can't you just use isnull(BrandID,0)? Then you'll have 0's replacing null values, and your average won't be messed up. Steven D Dworman - Web Consultant Systems Administrator ComSpec International - http://www.comspec-intnl.com phone: 248.647.8841 cell: 734.972.9676 - EMPOWER-XL ***Software for Higher Education*** http://www.empower-xl.com -Original Message- From: Bryan Stevenson [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 15, 2001 5:49 PM To: CF-Talk Subject: Re: SQL Query Problem I'd add in some AND "WHATEVER BrandIF Field" IS NOT NULL to weed out the NULL records Bryan Stevenson VP & Director of E-Commerce Development Electric Edge Systems Group Inc. p. 250.920.8830 e. [EMAIL PROTECTED] - Allaire Alliance Partner www.allaire.com - Original Message - From: "Brian Ferrigno" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, November 15, 2001 2:39 PM Subject: SQL Query Problem > I am having a problem trying to think of the correct SQL statement I need. > It's late in the day and my brain is completely fried. > > What I am tring to do is get the average price of one column based a an ID > that is passed to the SQL query. For example if a BrandID of 1 is passed > into the query it should output an average price of $4.00 > > The problem arises when one of the BrandID(BNID) columns doesn't contain any > of the #ID# numbers in it. The value that it returns is NULL which causes > the rest of the SQL statement to return NULL as a result even if the other > columns contain at least one #ID# in the BNID column. For example passing a > BrandID of 24 will return a resultset of NULL because it is not in the > second or third BRANDID column. > > Hopefully I made some sense. Below is the partial table design and SQL > statements I am using in my script. Any help would be great. > > > TABLE DESIGN > BNID1_3 Price1_3 BNID2_3 Price2_3 BNID3_3 Price3_3 > 1 $2.0010 $0.001 $5.00 > 24 $0.001 $5.0023 $0.00 > > SQL STATEMENT > select > (select avg(Price1_3) from temp1 where BNID1_3=#ID#)+ > (select avg(Price2_3) from temp1 where BNID2_3=#ID#)+ > (select avg(Price3_3) from temp1 where BNID3_3=#ID#) AS AVGPRICE > > > > ~~ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL Query Problem
Try adding: WHERE BrandID IS NOT NULL This will eliminate any rows where the value is null. It will have impact on the calculation of AVG, of course. If you have multiple BrandID columns, just add "AND BrandID2 IS NOT NULL", etc. Stephen > I am having a problem trying to think of the correct SQL statement I > need. It's late in the day and my brain is completely fried. > > What I am tring to do is get the average price of one column based a > an ID that is passed to the SQL query. For example if a BrandID of 1 > is passed into the query it should output an average price of $4.00 > > The problem arises when one of the BrandID(BNID) columns doesn't > contain any of the #ID# numbers in it. The value that it returns is > NULL which causes the rest of the SQL statement to return NULL as a > result even if the other columns contain at least one #ID# in the > BNID column. For example passing a BrandID of 24 will return a > resultset of NULL because it is not in the second or third BRANDID > column. > > Hopefully I made some sense. Below is the partial table design and > SQL statements I am using in my script. Any help would be great. > > > TABLE DESIGN > BNID1_3 Price1_3 BNID2_3 Price2_3 BNID3_3 Price3_3 > 1 $2.0010 $0.001 $5.00 > 24 $0.001 $5.0023 $0.00 > > SQL STATEMENT > select > (select avg(Price1_3) from temp1 where BNID1_3=#ID#)+ > (select avg(Price2_3) from temp1 where BNID2_3=#ID#)+ > (select avg(Price3_3) from temp1 where BNID3_3=#ID#) AS AVGPRICE > > > > ~~ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Query Problem
You can use Coalesce() or ISNULL() to avoid the NULL values, but you'll still have to keep from including that column in the equation or your division will come out wrong. i.e. you need to add only those columns that have values, then divide by the number of columns that had values to get the average. You should look into using CASE for the conditional logic. Eric -Original Message- From: Brian Ferrigno [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 15, 2001 2:40 PM To: CF-Talk Subject: SQL Query Problem I am having a problem trying to think of the correct SQL statement I need. It's late in the day and my brain is completely fried. What I am tring to do is get the average price of one column based a an ID that is passed to the SQL query. For example if a BrandID of 1 is passed into the query it should output an average price of $4.00 The problem arises when one of the BrandID(BNID) columns doesn't contain any of the #ID# numbers in it. The value that it returns is NULL which causes the rest of the SQL statement to return NULL as a result even if the other columns contain at least one #ID# in the BNID column. For example passing a BrandID of 24 will return a resultset of NULL because it is not in the second or third BRANDID column. Hopefully I made some sense. Below is the partial table design and SQL statements I am using in my script. Any help would be great. TABLE DESIGN BNID1_3 Price1_3 BNID2_3 Price2_3 BNID3_3 Price3_3 1 $2.0010 $0.001 $5.00 24 $0.001 $5.0023 $0.00 SQL STATEMENT select (select avg(Price1_3) from temp1 where BNID1_3=#ID#)+ (select avg(Price2_3) from temp1 where BNID2_3=#ID#)+ (select avg(Price3_3) from temp1 where BNID3_3=#ID#) AS AVGPRICE ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL Query Problem
I'd add in some AND "WHATEVER BrandIF Field" IS NOT NULL to weed out the NULL records Bryan Stevenson VP & Director of E-Commerce Development Electric Edge Systems Group Inc. p. 250.920.8830 e. [EMAIL PROTECTED] - Allaire Alliance Partner www.allaire.com - Original Message - From: "Brian Ferrigno" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, November 15, 2001 2:39 PM Subject: SQL Query Problem > I am having a problem trying to think of the correct SQL statement I need. > It's late in the day and my brain is completely fried. > > What I am tring to do is get the average price of one column based a an ID > that is passed to the SQL query. For example if a BrandID of 1 is passed > into the query it should output an average price of $4.00 > > The problem arises when one of the BrandID(BNID) columns doesn't contain any > of the #ID# numbers in it. The value that it returns is NULL which causes > the rest of the SQL statement to return NULL as a result even if the other > columns contain at least one #ID# in the BNID column. For example passing a > BrandID of 24 will return a resultset of NULL because it is not in the > second or third BRANDID column. > > Hopefully I made some sense. Below is the partial table design and SQL > statements I am using in my script. Any help would be great. > > > TABLE DESIGN > BNID1_3 Price1_3 BNID2_3 Price2_3 BNID3_3 Price3_3 > 1 $2.0010 $0.001 $5.00 > 24 $0.001 $5.0023 $0.00 > > SQL STATEMENT > select > (select avg(Price1_3) from temp1 where BNID1_3=#ID#)+ > (select avg(Price2_3) from temp1 where BNID2_3=#ID#)+ > (select avg(Price3_3) from temp1 where BNID3_3=#ID#) AS AVGPRICE > > > > ~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL query problem...
It's an old-school bass synthesizer. (yeah, I'd love to get my hands on one too...) veering blindly off-topic, Jamie -- Jamie Keane Programmer SolutionMasters, Inc. 9111 Monroe Rd., Suite 100 Charlotte, NC 28270 www.solutionmasters.com 704.563.5559 x 228 Voice 704.849.9291 Fax -Original Message- From: Chapman, Katrina <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Date: Thursday, August 17, 2000 3:01 PM Subject: RE: SQL query problem... >What's a TB303? > >--K > >-Original Message- >From: Rich Wild [mailto:[EMAIL PROTECTED]] >Sent: Thursday, August 17, 2000 10:57 AM >To: [EMAIL PROTECTED] >Subject: RE: SQL query problem... > > > >OK, this is probably wrong, but its all I can glean from SQL BOL > >ALTER TABLE table_name NOCHECK CONSTRAINT your_primary_key > >...do your stuff... > >ALTER TABLE table_name CHECK CONSTRAINT your_primary_key > > > >If I'm right, you owe me a mint condition TB303! > >*me sits down and crosses fingers in hope* > >-- >Rich Wild >Senior Web Designer > >--- >e-mango.com ltd Tel: 01202 587 400 >Lansdowne Place Fax: 01202 587 401 >17 Holdenhurst Road >Bournemouth Mailto:[EMAIL PROTECTED] >BH8 8EW, UK http://www.e-mango.com >--- >This message may contain information which is legally >privileged and/or confidential. If you are not the >intended recipient, you are hereby notified that any >unauthorised disclosure, copying, distribution or use >of this information is strictly prohibited. Such >notification notwithstanding, any comments, opinions, >information or conclusions expressed in this message >are those of the originator, not of e-mango.com ltd, >unless otherwise explicitly and independently indicated >by an authorised representative of e-mango.com ltd. >--------------- > > >-Original Message- >From: Ryan Williams [mailto:[EMAIL PROTECTED]] >Sent: 17 August 2000 18:15 >To: [EMAIL PROTECTED] >Subject: Re: SQL query problem... > > > > > > Oops. I just stumbled onto the answer by accident. I just removed the >"-2" > and > associated parentheses from the sub-query and I got my results. My > sub-query > now looks like: > select max(right(wire_id,len(wire_id))) from wire_list) > and it works too !! :-) > > My sincerest apologies to everyone in the list. > > Ryan Williams > [EMAIL PROTECTED] > > > >> - Original Message - >> From: "Ryan Williams" <[EMAIL PROTECTED]> >> To: <[EMAIL PROTECTED]> >> Sent: Thursday, August 17, 2000 12:45 PM >> Subject: SQL query problem... >> >> >> > I have a SQL problem. I know what information I want to retrieve >from >> > the DB, >> > however, I do not know what precisely is the proper SQL syntax/function >> is >> > in order to >> > achieve it. >> > >> >The query as it is currently written in my CF template is: >> > select wl.*,p.*,wlg.*,wb.*,wsnd.*,wrcvd.*,ws.* >> > from wire_list wl >> > left join P_officers p on wl.reg_by = p.off_id >> > left join wire_status ws on wl.status_code = ws.status_code >> > left join wire_log wlg on wl.wire_id = wlg.wire_id >> > left join wire_senders wsnd on wl.sender_id = wsnd.sender_id >> > left join wire_rcv_modes wrcvd on wl.rcv_mode = wrcvd.rcv_mode >> > left join wire_banks wb on wl.bank_id = wb.bank_id >> > where wl.wire_id = ( >> > select max(right(wire_id,(len(wire_id)-2))) >> > from wire_list) >> > >> > The error this code generates when I attempt to run it in SQL query >> > analyzer: >> > Server: Msg 536, Level 16, State 1, Line 1 >> > Invalid length parameter passed to the substring function. >> > >> > The content of the wire_id column looks like this format: >> > ab999 and is of varchar datatype. >> > >> > I did not code these tables and I am in the middle of re-coding the >> > entire site to be FuseBox compliant. I am trying to select the top >(max) >> > wire_id from the wire_list table, and display all records related >> > to the returned value of the max function, but the result that >> areretrieved >> > to date, >> > with the code "Select max(wire_id)" and no WHERE clause, >> > is ab99 when I know there are wire_id's in the table
RE: SQL query problem...
What's a TB303? --K -Original Message- From: Rich Wild [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 17, 2000 10:57 AM To: [EMAIL PROTECTED] Subject: RE: SQL query problem... OK, this is probably wrong, but its all I can glean from SQL BOL ALTER TABLE table_name NOCHECK CONSTRAINT your_primary_key ...do your stuff... ALTER TABLE table_name CHECK CONSTRAINT your_primary_key If I'm right, you owe me a mint condition TB303! *me sits down and crosses fingers in hope* -- Rich Wild Senior Web Designer --- e-mango.com ltd Tel: 01202 587 400 Lansdowne Place Fax: 01202 587 401 17 Holdenhurst Road Bournemouth Mailto:[EMAIL PROTECTED] BH8 8EW, UK http://www.e-mango.com --- This message may contain information which is legally privileged and/or confidential. If you are not the intended recipient, you are hereby notified that any unauthorised disclosure, copying, distribution or use of this information is strictly prohibited. Such notification notwithstanding, any comments, opinions, information or conclusions expressed in this message are those of the originator, not of e-mango.com ltd, unless otherwise explicitly and independently indicated by an authorised representative of e-mango.com ltd. --- -Original Message- From: Ryan Williams [mailto:[EMAIL PROTECTED]] Sent: 17 August 2000 18:15 To: [EMAIL PROTECTED] Subject: Re: SQL query problem... Oops. I just stumbled onto the answer by accident. I just removed the "-2" and associated parentheses from the sub-query and I got my results. My sub-query now looks like: select max(right(wire_id,len(wire_id))) from wire_list) and it works too !! :-) My sincerest apologies to everyone in the list. Ryan Williams [EMAIL PROTECTED] > - Original Message - > From: "Ryan Williams" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, August 17, 2000 12:45 PM > Subject: SQL query problem... > > > > I have a SQL problem. I know what information I want to retrieve from > > the DB, > > however, I do not know what precisely is the proper SQL syntax/function > is > > in order to > > achieve it. > > > >The query as it is currently written in my CF template is: > > select wl.*,p.*,wlg.*,wb.*,wsnd.*,wrcvd.*,ws.* > > from wire_list wl > > left join P_officers p on wl.reg_by = p.off_id > > left join wire_status ws on wl.status_code = ws.status_code > > left join wire_log wlg on wl.wire_id = wlg.wire_id > > left join wire_senders wsnd on wl.sender_id = wsnd.sender_id > > left join wire_rcv_modes wrcvd on wl.rcv_mode = wrcvd.rcv_mode > > left join wire_banks wb on wl.bank_id = wb.bank_id > > where wl.wire_id = ( > > select max(right(wire_id,(len(wire_id)-2))) > > from wire_list) > > > > The error this code generates when I attempt to run it in SQL query > > analyzer: > > Server: Msg 536, Level 16, State 1, Line 1 > > Invalid length parameter passed to the substring function. > > > > The content of the wire_id column looks like this format: > > ab999 and is of varchar datatype. > > > > I did not code these tables and I am in the middle of re-coding the > > entire site to be FuseBox compliant. I am trying to select the top (max) > > wire_id from the wire_list table, and display all records related > > to the returned value of the max function, but the result that > areretrieved > > to date, > > with the code "Select max(wire_id)" and no WHERE clause, > > is ab99 when I know there are wire_id's in the table that have values > > of ab999, etc. It seems that SQL does not recognize that ab999 is the > > larger value when compared to ab99. > > > > The solution I am trying to apply with the code at the top of this email > is > > to "get" > > the "number" part of the wire_id column, minus the two letters, and then > use > > the max function on > > the remainder to get the ab999 wire_id i know is in the table. I looked > > through the > > SQL books online and it seems that SQL Sever does not have the ability to > > convert > > the varchar datatype (of the wire_id) column to the int data type that > > the max function seems to need in order to work properly. > > > > Does anyone know of a work around for this problem? Or am I going totally > > in the wrong > > direction with this logic? > > > > Any help/ideas/comments
RE: SQL query problem...
OK, this is probably wrong, but its all I can glean from SQL BOL ALTER TABLE table_name NOCHECK CONSTRAINT your_primary_key ...do your stuff... ALTER TABLE table_name CHECK CONSTRAINT your_primary_key If I'm right, you owe me a mint condition TB303! *me sits down and crosses fingers in hope* -- Rich Wild Senior Web Designer --- e-mango.com ltd Tel: 01202 587 400 Lansdowne Place Fax: 01202 587 401 17 Holdenhurst Road Bournemouth Mailto:[EMAIL PROTECTED] BH8 8EW, UK http://www.e-mango.com --- This message may contain information which is legally privileged and/or confidential. If you are not the intended recipient, you are hereby notified that any unauthorised disclosure, copying, distribution or use of this information is strictly prohibited. Such notification notwithstanding, any comments, opinions, information or conclusions expressed in this message are those of the originator, not of e-mango.com ltd, unless otherwise explicitly and independently indicated by an authorised representative of e-mango.com ltd. --- -Original Message- From: Ryan Williams [mailto:[EMAIL PROTECTED]] Sent: 17 August 2000 18:15 To: [EMAIL PROTECTED] Subject: Re: SQL query problem... Oops. I just stumbled onto the answer by accident. I just removed the "-2" and associated parentheses from the sub-query and I got my results. My sub-query now looks like: select max(right(wire_id,len(wire_id))) from wire_list) and it works too !! :-) My sincerest apologies to everyone in the list. Ryan Williams [EMAIL PROTECTED] > - Original Message - > From: "Ryan Williams" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, August 17, 2000 12:45 PM > Subject: SQL query problem... > > > > I have a SQL problem. I know what information I want to retrieve from > > the DB, > > however, I do not know what precisely is the proper SQL syntax/function > is > > in order to > > achieve it. > > > >The query as it is currently written in my CF template is: > > select wl.*,p.*,wlg.*,wb.*,wsnd.*,wrcvd.*,ws.* > > from wire_list wl > > left join P_officers p on wl.reg_by = p.off_id > > left join wire_status ws on wl.status_code = ws.status_code > > left join wire_log wlg on wl.wire_id = wlg.wire_id > > left join wire_senders wsnd on wl.sender_id = wsnd.sender_id > > left join wire_rcv_modes wrcvd on wl.rcv_mode = wrcvd.rcv_mode > > left join wire_banks wb on wl.bank_id = wb.bank_id > > where wl.wire_id = ( > > select max(right(wire_id,(len(wire_id)-2))) > > from wire_list) > > > > The error this code generates when I attempt to run it in SQL query > > analyzer: > > Server: Msg 536, Level 16, State 1, Line 1 > > Invalid length parameter passed to the substring function. > > > > The content of the wire_id column looks like this format: > > ab999 and is of varchar datatype. > > > > I did not code these tables and I am in the middle of re-coding the > > entire site to be FuseBox compliant. I am trying to select the top (max) > > wire_id from the wire_list table, and display all records related > > to the returned value of the max function, but the result that > areretrieved > > to date, > > with the code "Select max(wire_id)" and no WHERE clause, > > is ab99 when I know there are wire_id's in the table that have values > > of ab999, etc. It seems that SQL does not recognize that ab999 is the > > larger value when compared to ab99. > > > > The solution I am trying to apply with the code at the top of this email > is > > to "get" > > the "number" part of the wire_id column, minus the two letters, and then > use > > the max function on > > the remainder to get the ab999 wire_id i know is in the table. I looked > > through the > > SQL books online and it seems that SQL Sever does not have the ability to > > convert > > the varchar datatype (of the wire_id) column to the int data type that > > the max function seems to need in order to work properly. > > > > Does anyone know of a work around for this problem? Or am I going totally > > in the wrong > > direction with this logic? > > > > Any help/ideas/comments would be greatly appreciated. Thanks. > > > > Ryan Williams > > [EMAIL PROTECTED] > > > - > -- > > ---
Re: SQL query problem...
Thanks for the info. That indeed clarified the problem.
My code now works as intended. :-)
Ryan Williams
[EMAIL PROTECTED]
- Original Message -
From: "Andy Ewings" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, August 17, 2000 12:59 PM
Subject: RE: SQL query problem...
> If you are using SQL 6.5 then I think I know what's causing this.
>
> This part of your code: (len(wire_id)-2) could well be producing a
negative
> result and hence when you wrap the right function around it you will get a
> 536 errorfor more info have a look at what Microsoft say about update
> statements using the substring function (same thing will happen with the
> right function)
>
>
> BUG: Update Query Causes an Access Violation if a SUBSTRING Encounters
Error
> 536
> --
--
>
> BUG #: 17963 (SQLBUG_65)
>
> SYMPTOMS
> An Update query may cause an Access Violation (AV) if a SUBSTRING function
> generates this error:
>
> Msg 536, Level 16, State 1
> Invalid length parameter passed to the substring function.
>
> WORKAROUND
> Make sure that you pass a valid length to the SUBSTRING function. When you
> pass a valid length to the SUBSTRING function, the Access Violation no
> longer occurs.
>
> STATUS
> Microsoft has confirmed this to be a problem in SQL Server version 6.5.
>
> MORE INFORMATION
> This problem is further illustrated by this example:
>
> create table tbl_1
> (col1 varchar(16) NULL,
> col2 varchar(16) NULL
> )
> go
> insert into tbl_1 values('123:456:789', null)
> insert into tbl_1 values('123', null)
> go
>
> UPDATE tbl_1
> SET col2 = substring(col1,1,charindex(":",col1)-1)
> If you look at the details of the preceding UPDATE statement, you find
that
> the "charindex(":",col1)" returns a value of 0 for one of the rows. You
then
> proceed to subtract 1 from this value, which results in a value of -1.
This
> is the value that you end up passing to the SUBSTRING function, which then
> creates the 536 error.
>
>
>
>
>
> -Original Message-
> From: Ryan Williams [mailto:[EMAIL PROTECTED]]
> Sent: 17 August 2000 17:45
> To: [EMAIL PROTECTED]
> Subject: SQL query problem...
>
>
> I have a SQL problem. I know what information I want to retrieve from
> the DB,
> however, I do not know what precisely is the proper SQL syntax/function
is
> in order to
> achieve it.
>
>The query as it is currently written in my CF template is:
> select wl.*,p.*,wlg.*,wb.*,wsnd.*,wrcvd.*,ws.*
> from wire_list wl
> left join P_officers p on wl.reg_by = p.off_id
> left join wire_status ws on wl.status_code = ws.status_code
> left join wire_log wlg on wl.wire_id = wlg.wire_id
> left join wire_senders wsnd on wl.sender_id = wsnd.sender_id
> left join wire_rcv_modes wrcvd on wl.rcv_mode = wrcvd.rcv_mode
> left join wire_banks wb on wl.bank_id = wb.bank_id
> where wl.wire_id = (
> select max(right(wire_id,(len(wire_id)-2)))
> from wire_list)
>
> The error this code generates when I attempt to run it in SQL query
> analyzer:
> Server: Msg 536, Level 16, State 1, Line 1
> Invalid length parameter passed to the substring function.
>
> The content of the wire_id column looks like this format:
> ab999 and is of varchar datatype.
>
> I did not code these tables and I am in the middle of re-coding the
> entire site to be FuseBox compliant. I am trying to select the top (max)
> wire_id from the wire_list table, and display all records related
> to the returned value of the max function, but the result that
areretrieved
> to date,
> with the code "Select max(wire_id)" and no WHERE clause,
> is ab99 when I know there are wire_id's in the table that have values
> of ab999, etc. It seems that SQL does not recognize that ab999 is the
> larger value when compared to ab99.
>
> The solution I am trying to apply with the code at the top of this email
is
> to "get"
> the "number" part of the wire_id column, minus the two letters, and then
use
> the max function on
> the remainder to get the ab999 wire_id i know is in the table. I looked
> through the
> SQL books online and it seems that SQL Sever does not have the ability to
> convert
> the varchar datatype (of the wire_id) column to the int data type that
> the max function seems to need in order to work properly.
>
> Does anyone know of a work around for this problem? Or am I going totally
> in the wrong
> direction with this logic?
>
> Any help/ideas/comments would be greatly appreciated. Thanks.
>
> Ryan Williams
>
Re: SQL query problem...
Oops. I just stumbled onto the answer by accident. I just removed the "-2" and associated parentheses from the sub-query and I got my results. My sub-query now looks like: select max(right(wire_id,len(wire_id))) from wire_list) and it works too !! :-) My sincerest apologies to everyone in the list. Ryan Williams [EMAIL PROTECTED] > - Original Message - > From: "Ryan Williams" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, August 17, 2000 12:45 PM > Subject: SQL query problem... > > > > I have a SQL problem. I know what information I want to retrieve from > > the DB, > > however, I do not know what precisely is the proper SQL syntax/function > is > > in order to > > achieve it. > > > >The query as it is currently written in my CF template is: > > select wl.*,p.*,wlg.*,wb.*,wsnd.*,wrcvd.*,ws.* > > from wire_list wl > > left join P_officers p on wl.reg_by = p.off_id > > left join wire_status ws on wl.status_code = ws.status_code > > left join wire_log wlg on wl.wire_id = wlg.wire_id > > left join wire_senders wsnd on wl.sender_id = wsnd.sender_id > > left join wire_rcv_modes wrcvd on wl.rcv_mode = wrcvd.rcv_mode > > left join wire_banks wb on wl.bank_id = wb.bank_id > > where wl.wire_id = ( > > select max(right(wire_id,(len(wire_id)-2))) > > from wire_list) > > > > The error this code generates when I attempt to run it in SQL query > > analyzer: > > Server: Msg 536, Level 16, State 1, Line 1 > > Invalid length parameter passed to the substring function. > > > > The content of the wire_id column looks like this format: > > ab999 and is of varchar datatype. > > > > I did not code these tables and I am in the middle of re-coding the > > entire site to be FuseBox compliant. I am trying to select the top (max) > > wire_id from the wire_list table, and display all records related > > to the returned value of the max function, but the result that > areretrieved > > to date, > > with the code "Select max(wire_id)" and no WHERE clause, > > is ab99 when I know there are wire_id's in the table that have values > > of ab999, etc. It seems that SQL does not recognize that ab999 is the > > larger value when compared to ab99. > > > > The solution I am trying to apply with the code at the top of this email > is > > to "get" > > the "number" part of the wire_id column, minus the two letters, and then > use > > the max function on > > the remainder to get the ab999 wire_id i know is in the table. I looked > > through the > > SQL books online and it seems that SQL Sever does not have the ability to > > convert > > the varchar datatype (of the wire_id) column to the int data type that > > the max function seems to need in order to work properly. > > > > Does anyone know of a work around for this problem? Or am I going totally > > in the wrong > > direction with this logic? > > > > Any help/ideas/comments would be greatly appreciated. Thanks. > > > > Ryan Williams > > [EMAIL PROTECTED] > > > - > -- > > --- > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > > To Unsubscribe visit > > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or > > send a message to [EMAIL PROTECTED] with 'unsubscribe' in > > the body. > > > > > > -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: SQL query problem...
If you are using SQL 6.5 then I think I know what's causing this.
This part of your code: (len(wire_id)-2) could well be producing a negative
result and hence when you wrap the right function around it you will get a
536 errorfor more info have a look at what Microsoft say about update
statements using the substring function (same thing will happen with the
right function)
BUG: Update Query Causes an Access Violation if a SUBSTRING Encounters Error
536
BUG #: 17963 (SQLBUG_65)
SYMPTOMS
An Update query may cause an Access Violation (AV) if a SUBSTRING function
generates this error:
Msg 536, Level 16, State 1
Invalid length parameter passed to the substring function.
WORKAROUND
Make sure that you pass a valid length to the SUBSTRING function. When you
pass a valid length to the SUBSTRING function, the Access Violation no
longer occurs.
STATUS
Microsoft has confirmed this to be a problem in SQL Server version 6.5.
MORE INFORMATION
This problem is further illustrated by this example:
create table tbl_1
(col1 varchar(16) NULL,
col2 varchar(16) NULL
)
go
insert into tbl_1 values('123:456:789', null)
insert into tbl_1 values('123', null)
go
UPDATE tbl_1
SET col2 = substring(col1,1,charindex(":",col1)-1)
If you look at the details of the preceding UPDATE statement, you find that
the "charindex(":",col1)" returns a value of 0 for one of the rows. You then
proceed to subtract 1 from this value, which results in a value of -1. This
is the value that you end up passing to the SUBSTRING function, which then
creates the 536 error.
-Original Message-
From: Ryan Williams [mailto:[EMAIL PROTECTED]]
Sent: 17 August 2000 17:45
To: [EMAIL PROTECTED]
Subject: SQL query problem...
I have a SQL problem. I know what information I want to retrieve from
the DB,
however, I do not know what precisely is the proper SQL syntax/function is
in order to
achieve it.
The query as it is currently written in my CF template is:
select wl.*,p.*,wlg.*,wb.*,wsnd.*,wrcvd.*,ws.*
from wire_list wl
left join P_officers p on wl.reg_by = p.off_id
left join wire_status ws on wl.status_code = ws.status_code
left join wire_log wlg on wl.wire_id = wlg.wire_id
left join wire_senders wsnd on wl.sender_id = wsnd.sender_id
left join wire_rcv_modes wrcvd on wl.rcv_mode = wrcvd.rcv_mode
left join wire_banks wb on wl.bank_id = wb.bank_id
where wl.wire_id = (
select max(right(wire_id,(len(wire_id)-2)))
from wire_list)
The error this code generates when I attempt to run it in SQL query
analyzer:
Server: Msg 536, Level 16, State 1, Line 1
Invalid length parameter passed to the substring function.
The content of the wire_id column looks like this format:
ab999 and is of varchar datatype.
I did not code these tables and I am in the middle of re-coding the
entire site to be FuseBox compliant. I am trying to select the top (max)
wire_id from the wire_list table, and display all records related
to the returned value of the max function, but the result that areretrieved
to date,
with the code "Select max(wire_id)" and no WHERE clause,
is ab99 when I know there are wire_id's in the table that have values
of ab999, etc. It seems that SQL does not recognize that ab999 is the
larger value when compared to ab99.
The solution I am trying to apply with the code at the top of this email is
to "get"
the "number" part of the wire_id column, minus the two letters, and then use
the max function on
the remainder to get the ab999 wire_id i know is in the table. I looked
through the
SQL books online and it seems that SQL Sever does not have the ability to
convert
the varchar datatype (of the wire_id) column to the int data type that
the max function seems to need in order to work properly.
Does anyone know of a work around for this problem? Or am I going totally
in the wrong
direction with this logic?
Any help/ideas/comments would be greatly appreciated. Thanks.
Ryan Williams
[EMAIL PROTECTED]
---
---
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
