RE: Select statement with a max() of 2 values
Yeah, simplest way always wins! Lol I just wondered if anyone else had ever come across this before? Cheers Tristan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ed Leafe Posted At: 20 June 2007 14:26 Posted To: Profox Archive Conversation: Select statement with a max() of 2 values Subject: Re: Select statement with a max() of 2 values Why not create a little UDF, and use that in the SQL? FUNCTION FoxMax(p1, p2) RETURN MAX(p1, p2) SELECT f1, f2, FoxMax(f5,f6) as test FROM t1.. -- Ed Leafe -- http://leafe.com -- http://dabodev.com [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Select statement with a max() of 2 values
Tristan Leask wrote: Afternoon all, I have got a strange one here. I have written a SELECT statement which is something similar to SELECT f1, f2, max(f5,f6) as test FROM t1.. This looks ok to me, however when I try to run it, foxpro doesn't like the fact that I am using the MAX() function. Apparently when MAX() is used in a SELECT statement it wants to calculate the maximum of the specified column. Thus can you not use MAX() to calculate the maximum of some fields in a row, E.g. MAX(f1, f2, f3, f4)? I havn't tried it but what about SELECT f1, f2, iif(f5f6,f5,f6) as test FROM t1. Peter ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Select statement with a max() of 2 values
On 6/20/07, Tristan Leask [EMAIL PROTECTED] wrote: SELECT f1, f2, max(f5,f6) as test FROM t1.. This looks ok to me, however when I try to run it, foxpro doesn't like the fact that I am using the MAX() function. MAX() is one of the few times there's a name collision between a native VFP command and the SQL language. Ed's suggestiion of a UDF will work. You could also include an inline IIF(f5f6, f5, f6) to get a similar effect. You're not the first to find this. I suspect it might be in the Hacker's Guide. -- Ted Roche Ted Roche Associates, LLC http://www.tedroche.com ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Select statement with a max() of 2 values
Using evaluate() may impact less on performance. SELECT f1, f2, evaluate(max(f5,f6)) as test FROM t1.. Gianni - Original Message - From: Ed Leafe [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wed, 20 Jun 2007 09:26:20 -0400 Subject: Re: Select statement with a max() of 2 values On Jun 20, 2007, at 9:20 AM, Tristan Leask wrote: I have got a strange one here. I have written a SELECT statement which is something similar to SELECT f1, f2, max(f5,f6) as test FROM t1.. This looks ok to me, however when I try to run it, foxpro doesn't like the fact that I am using the MAX() function. Apparently when MAX() is used in a SELECT statement it wants to calculate the maximum of the specified column. Thus can you not use MAX() to calculate the maximum of some fields in a row, E.g. MAX(f1, f2, f3, f4)? Why not create a little UDF, and use that in the SQL? FUNCTION FoxMax(p1, p2) RETURN MAX(p1, p2) SELECT f1, f2, FoxMax(f5,f6) as test FROM t1.. -- Ed Leafe -- http://leafe.com -- http://dabodev.com [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Select statement with a max() of 2 values
Ah, that makes sense. Thanks Ted. Tristan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ted Roche Posted At: 20 June 2007 14:43 Posted To: Profox Archive Conversation: Select statement with a max() of 2 values Subject: Re: Select statement with a max() of 2 values MAX() is one of the few times there's a name collision between a native VFP command and the SQL language. Ed's suggestiion of a UDF will work. You could also include an inline IIF(f5f6, f5, f6) to get a similar effect. You're not the first to find this. I suspect it might be in the Hacker's Guide. -- Ted Roche Ted Roche Associates, LLC http://www.tedroche.com -- This email was sent to [EMAIL PROTECTED] Unless otherwise agreed expressly in writing by Marine Software Limited, this communication and attachments are to be treated as confidential and the information in it may not be used or disclosed except for the purpose for which it was sent. If you are not the intended recipient of this communication you should destroy it without copying, disclosing or otherwise using its contents. Please notify the sender immediately of the error. [EMAIL PROTECTED] Marine Software Limited. Registered in England Wales. No 2576494 Regsitered Office. Unit3, Aylesham Business Pk, Aylesham. Kent. UK -- ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Select statement with a max() of 2 values
Ted Roche wrote: On 6/20/07, Tristan Leask [EMAIL PROTECTED] wrote: SELECT f1, f2, max(f5,f6) as test FROM t1.. This looks ok to me, however when I try to run it, foxpro doesn't like the fact that I am using the MAX() function. MAX() is one of the few times there's a name collision between a native VFP command and the SQL language. Ed's suggestiion of a UDF will work. You could also include an inline IIF(f5f6, f5, f6) to get a similar effect. You're not the first to find this. I suspect it might be in the Hacker's Guide. I'm pretty sure I posted a workaround for this on ProFox that ended up as a tip in FPA... Search my name and MAX in the archives and see what comes up... Bill ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Select statement with a max() of 2 values
Bill Anderson wrote: Ted Roche wrote: On 6/20/07, Tristan Leask [EMAIL PROTECTED] wrote: SELECT f1, f2, max(f5,f6) as test FROM t1.. This looks ok to me, however when I try to run it, foxpro doesn't like the fact that I am using the MAX() function. MAX() is one of the few times there's a name collision between a native VFP command and the SQL language. Ed's suggestiion of a UDF will work. You could also include an inline IIF(f5f6, f5, f6) to get a similar effect. You're not the first to find this. I suspect it might be in the Hacker's Guide. I'm pretty sure I posted a workaround for this on ProFox that ended up as a tip in FPA... Search my name and MAX in the archives and see what comes up... Bill http://leafe.com/archives/showMsg/201851 Bill ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Select statement with a max() of 2 values
Bill Anderson wrote: Ted Roche wrote: On 6/20/07, Tristan Leask [EMAIL PROTECTED] wrote: SELECT f1, f2, max(f5,f6) as test FROM t1.. This looks ok to me, however when I try to run it, foxpro doesn't like the fact that I am using the MAX() function. MAX() is one of the few times there's a name collision between a native VFP command and the SQL language. Ed's suggestiion of a UDF will work. You could also include an inline IIF(f5f6, f5, f6) to get a similar effect. You're not the first to find this. I suspect it might be in the Hacker's Guide. I'm pretty sure I posted a workaround for this on ProFox that ended up as a tip in FPA... Search my name and MAX in the archives and see what comes up... Bush, Cheney, Jews. Stephen Russell DBA / .Net Developer Memphis TN 38115 901.246-0159 A good way to judge people is by observing how they treat those who can do them absolutely no good. ---Unknown http://spaces.msn.com/members/srussell/ No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.472 / Virus Database: 269.9.1/854 - Release Date: 6/19/2007 1:12 PM ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
