New topic: Sql Max(date) doesn't work. need help
<http://forums.realsoftware.com/viewtopic.php?t=46082> Page 1 of 1 [ 9 posts ] Previous topic | Next topic Author Message Antonio Post subject: Sql Max(date) doesn't work. need helpPosted: Thu Nov 29, 2012 2:58 pm Joined: Sat Feb 04, 2012 1:26 pm Posts: 37 Hi I have database with the column "data_1" containing dates; but not all the records contain dates. So i performed the following select extraction: rs=db.SQLSelect("SELECT data_1 FROM [db 2012] where data_1 IS NOT NULL") and it works without any problem. But what i really need is to extract a single value representing the most recent date contained in the database, so i performed the following select extraction, adding the Max function : rs=db.SQLSelect("SELECT MAX(data_1) FROM [db 2012] where data_1 IS NOT NULL") .. it does not works and returns me a Nilobjectexception message. i also tried the following: rs=db.SQLSelect("SELECT max(data_1) IN (select data_1 FROM [db 2012] where data_1 IS NOT NULL)") ..without success. Any suggestions ? Top DaveS Post subject: Re: Sql Max(date) doesn't work. need helpPosted: Thu Nov 29, 2012 3:56 pm Joined: Sun Aug 05, 2007 10:46 am Posts: 4309 Location: San Diego, CA What data type is DATA_1? if not a DATE/TIME datatype.. what format is it in? What database engine? Show some samples of what DATA_1 looks like _________________ Dave Sisemore MacPro, OSX Lion 10.7.4 RB2012r1 Note : I am not interested in any solutions that involve custom Plug-ins of any kind Top Antonio Post subject: Re: Sql Max(date) doesn't work. need helpPosted: Thu Nov 29, 2012 4:22 pm Joined: Sat Feb 04, 2012 1:26 pm Posts: 37 Thanks for helping, Dave. Data_1 column is in DATE/TIME Datatype format. Name Column always contains a name. Data_1 may contain a date. The Database Engine is Access; *.mdb file. Name | Data_1 Antony | 2012/11/29 Mike | Mary | 2010/02/04 John | 2011/08/01 Top DaveS Post subject: Re: Sql Max(date) doesn't work. need helpPosted: Thu Nov 29, 2012 4:25 pm Joined: Sun Aug 05, 2007 10:46 am Posts: 4309 Location: San Diego, CA try this SELECT MAX(data_1) as data_1 FROM [db 2012] WHERE data_1 IS NOT NULL That SHOULD work (all I did was add an alias to the MAX) _________________ Dave Sisemore MacPro, OSX Lion 10.7.4 RB2012r1 Note : I am not interested in any solutions that involve custom Plug-ins of any kind Top Antonio Post subject: Re: Sql Max(date) doesn't work. need helpPosted: Thu Nov 29, 2012 4:40 pm Joined: Sat Feb 04, 2012 1:26 pm Posts: 37 Dave, it doesn't work it returns me database error -3005. I also tryed to modify the alias from data_acc to data_acctry and it returns again the ....nilobjectexception was not handled.. Top Antonio Post subject: Re: Sql Max(date) doesn't work. need helpPosted: Thu Nov 29, 2012 4:54 pm Joined: Sat Feb 04, 2012 1:26 pm Posts: 37 It works, It works. I had to modify the second part of the code: label1.text=rs.field.... but why it did not work without the alias? Top timhare Post subject: Re: Sql Max(date) doesn't work. need helpPosted: Thu Nov 29, 2012 5:27 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 11851 Location: Portland, OR USA Without the alias, the column returned is literally named "max(data_1)", not "data_1". So if you try to access it by name, rs.field("data_1") will return nil, because the recordset does not contain a column named data_1, it contains a column named max(data_1). The aliasw resolves this issue. Top timhare Post subject: Re: Sql Max(date) doesn't work. need helpPosted: Thu Nov 29, 2012 5:31 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 11851 Location: Portland, OR USA BTW, you shouldn't need the WHERE clause in your selection. According to the sql standard, MAX() ignores null values anyway. Top Antonio Post subject: Re: Sql Max(date) doesn't work. need helpPosted: Thu Nov 29, 2012 5:46 pm Joined: Sat Feb 04, 2012 1:26 pm Posts: 37 Dave, Tim, thank you very much for help me to improve. ciao, Antonio Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 9 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
