Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 16 total |
Tricky SQL help |
Mon, May 7 2007 12:07 PM | Permanent Link |
Bryan | Hello all,
I have a table that contains the following fields. ObLocation : String ObTime : TimeStamp ObTemp : Integer; ObWind : Integer; 1. The query must return all fields. 2. The query must return records within a given date/time range. 3. The tricky part for me... The query must only return the latest record based on ObTime (so there's only 1 record per ObLocation...) There are occasions when 1 ObLocation will have multiple records per hour, but I only want the latest within the time range. I hope that makes sense... Thanks for any help you can provide. Kind regards, Bryan |
Mon, May 7 2007 1:03 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Bryan,
"Bryan" <bryanray67@hotmail.com> wrote in message news:5AA1E100-CAE5-48EE-B5BA-BDC7E722FCD7@news.elevatesoft.com... > Hello all, > > I have a table that contains the following fields. > > ObLocation : String > ObTime : TimeStamp > ObTemp : Integer; > ObWind : Integer; > << 1. The query must return all fields. 2. The query must return records within a given date/time range. 3. The tricky part for me... The query must only return the latest record based on ObTime (so there's only 1 record per ObLocation...) >> You'll have to use two queries in a script: SELECT ObLocation, Max(ObTime) AS ObTime INTO "\Memory\Temp" FROM MyTable WHERE ObTime BETWEEN <BeginTimeStamp> AND <EndTimeStamp> GROUP BY ObLocation; SELECT T.ObLocation, T.ObTime, M.Obtemp, M.ObWind FROM "\Memory\Temp" T INNER JOIN MyTable M ON T.ObLocation = M.ObLocation; Be sure to drop the in-memory Temp table when you're done, also. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, May 7 2007 1:54 PM | Permanent Link |
Bryan | "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:
You'll have to use two queries in a script: SELECT ObLocation, Max(ObTime) AS ObTime INTO "\Memory\Temp" FROM MyTable WHERE ObTime BETWEEN <BeginTimeStamp> AND <EndTimeStamp> GROUP BY ObLocation; SELECT T.ObLocation, T.ObTime, M.Obtemp, M.ObWind FROM "\Memory\Temp" T INNER JOIN MyTable M ON T.ObLocation = M.ObLocation; Be sure to drop the in-memory Temp table when you're done, also. -- Tim Young Elevate Software www.elevatesoft.com Hi Tim, The query executed, but I didn't get the results I thought I should... It seems that the ObTime returned was the same for 2 different records, but in fact, the table shows different times. I've attached a screen shot that shows the query results and the actual table. "BIKF" is the record I'm looking at and it shows differnet WindS values for exact same time, but the table shows the times as 2:00 PM and 2:30 PM. Am I doing something wrong? The table does not contain any repeat data where the ICAO and the ObTime are the same. Here is the SQL I'm using (slightly modified based on my field names and table name....) SELECT ICAO, Max(ObTime) AS ObTime INTO "\Memory\Temp" FROM CurrentObs WHERE ObTime BETWEEN '2007-05-07 1:00 PM' AND '2007-05-07 2:00 PM' GROUP BY ICAO; SELECT T.ICAO, T.ObTime, M.Temp, M.WindS FROM "\Memory\Temp" T INNER JOIN CurrentObs M ON T.ICAO = M.ICAO; Thanks! Bryan Attachments: sql_vs_table.gif |
Mon, May 7 2007 2:04 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Bryan,
<< The query executed, but I didn't get the results I thought I should... It seems that the ObTime returned was the same for 2 different records, but in fact, the table shows different times. I've attached a screen shot that shows the query results and the actual table. "BIKF" is the record I'm looking at and it shows differnet WindS values for exact same time, but the table shows the times as 2:00 PM and 2:30 PM. Am I doing something wrong? The table does not contain any repeat data where the ICAO and the ObTime are the same. >> The issue is that the ICAO field isn't unique, and I was under the impression it was based upon what you said about there being one record per location. Try this instead: SELECT ICAO, Max(ObTime) AS ObTime INTO "\Memory\Temp" FROM CurrentObs WHERE ObTime BETWEEN '2007-05-07 1:00 PM' AND '2007-05-07 2:00 PM' GROUP BY ICAO; SELECT T.ICAO, T.ObTime, M.Temp, M.WindS FROM "\Memory\Temp" T INNER JOIN CurrentObs M ON T.ICAO = M.ICAO AND T.ObTime = M.ObTime; -- Tim Young Elevate Software www.elevatesoft.com |
Mon, May 7 2007 2:17 PM | Permanent Link |
Bryan | Tim,
That did the trick. Thanks for your outstanding support. Bryan "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote: Bryan, << The query executed, but I didn't get the results I thought I should... It seems that the ObTime returned was the same for 2 different records, but in fact, the table shows different times. I've attached a screen shot that shows the query results and the actual table. "BIKF" is the record I'm looking at and it shows differnet WindS values for exact same time, but the table shows the times as 2:00 PM and 2:30 PM. Am I doing something wrong? The table does not contain any repeat data where the ICAO and the ObTime are the same. >> The issue is that the ICAO field isn't unique, and I was under the impression it was based upon what you said about there being one record per location. Try this instead: SELECT ICAO, Max(ObTime) AS ObTime INTO "\Memory\Temp" FROM CurrentObs WHERE ObTime BETWEEN '2007-05-07 1:00 PM' AND '2007-05-07 2:00 PM' GROUP BY ICAO; SELECT T.ICAO, T.ObTime, M.Temp, M.WindS FROM "\Memory\Temp" T INNER JOIN CurrentObs M ON T.ICAO = M.ICAO AND T.ObTime = M.ObTime; -- Tim Young Elevate Software www.elevatesoft.com |
Tue, May 8 2007 3:35 PM | Permanent Link |
Bryan Ray | Tim,
What do you mean by "Be sure to drop the in-memory Temp table when you're done, also." I'm just using the SQL statement you gave... How do I drop the in-memory temp table? Bryan Bryan <bryanray67@hotmail.com> wrote: Tim, That did the trick. Thanks for your outstanding support. Bryan "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote: Bryan, << The query executed, but I didn't get the results I thought I should... It seems that the ObTime returned was the same for 2 different records, but in fact, the table shows different times. I've attached a screen shot that shows the query results and the actual table. "BIKF" is the record I'm looking at and it shows differnet WindS values for exact same time, but the table shows the times as 2:00 PM and 2:30 PM. Am I doing something wrong? The table does not contain any repeat data where the ICAO and the ObTime are the same. >> The issue is that the ICAO field isn't unique, and I was under the impression it was based upon what you said about there being one record per location. Try this instead: SELECT ICAO, Max(ObTime) AS ObTime INTO "\Memory\Temp" FROM CurrentObs WHERE ObTime BETWEEN '2007-05-07 1:00 PM' AND '2007-05-07 2:00 PM' GROUP BY ICAO; SELECT T.ICAO, T.ObTime, M.Temp, M.WindS FROM "\Memory\Temp" T INNER JOIN CurrentObs M ON T.ICAO = M.ICAO AND T.ObTime = M.ObTime; -- Tim Young Elevate Software www.elevatesoft.com |
Tue, May 8 2007 7:27 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Bryan,
<< What do you mean by "Be sure to drop the in-memory Temp table when you're done, also." I'm just using the SQL statement you gave... How do I drop the in-memory temp table? >> You have to issue this statement when you're done accessing the result set (closed it) generated by the script that I gave you: DROP TABLE "\Memory\Temp" -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Jun 14 2007 1:36 PM | Permanent Link |
Bryan | Tim,
I have a couple questions about dropping the memory table... 1. Can I control where the memory table is created....? It seems that there is a table created with a random number number in the name (e.g. 2438.dat and 2438.idx). 2. I have the following code to remove the temp table: Procedure TFObservations.DropTempTable; begin MData.q.SQL.Clear; MData.q.SQL.Add('DROP TABLE "Memory\Temp";'); Try MData.q.Prepare; MData.q.ExecSQL; Except End; end; It seems to work and removes the temp table (numeric table name), but if I set breakpoints, I can see that the table is actually "removed" from the folder when the first line is executed "MData.q.SQL.Clear". I would think that the table would be removed/deleted when the MData.q.ExecSQL line was executed. Any thoughts...? Am I doing this correctly? Thanks! Bryan "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote: Bryan, << What do you mean by "Be sure to drop the in-memory Temp table when you're done, also." I'm just using the SQL statement you gave... How do I drop the in-memory temp table? >> You have to issue this statement when you're done accessing the result set (closed it) generated by the script that I gave you: DROP TABLE "\Memory\Temp" -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Jun 14 2007 1:40 PM | Permanent Link |
"Robert" | "Bryan" <bryan@extremewx.com> wrote in message news:F3CAC7C9-4152-47E5-B555-B23488188E13@news.elevatesoft.com... > Tim, > I have a couple questions about dropping the memory table... > > 1. Can I control where the memory table is created....? It seems that > there is a table created with a random number number in the name (e.g. > 2438.dat and 2438.idx). > > 2. I have the following code to remove the temp table: > Procedure TFObservations.DropTempTable; > begin > MData.q.SQL.Clear; > MData.q.SQL.Add('DROP TABLE "Memory\Temp";'); > Try > MData.q.Prepare; > MData.q.ExecSQL; > Except > > End; > end; > > It seems to work and removes the temp table (numeric table name), but if I > set breakpoints, I can see that the table is actually "removed" from the > folder when the first line > is executed "MData.q.SQL.Clear". I would think that the table would be > removed/deleted when the MData.q.ExecSQL line was executed. > That table is a memory table, it is not in any folder. You are looking at a different table. A memory table resides in memory, not on disk. Robert > Any thoughts...? Am I doing this correctly? > > Thanks! > Bryan > > > "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote: > > Bryan, > > << What do you mean by "Be sure to drop the in-memory Temp table when > you're > done, also." I'm just using the SQL statement you gave... How do I drop > the > in-memory temp table? >> > > You have to issue this statement when you're done accessing the result set > (closed it) generated by the script that I gave you: > > DROP TABLE "\Memory\Temp" > > -- > Tim Young > Elevate Software > www.elevatesoft.com > > |
Thu, Jun 14 2007 2:13 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Bryan
Just to expand on what Robert said. The numeric named tables are DBISAM's temporary tables created when you run a query resulting in a canned dataset. These are cleaned up whenever the query is closed. If you find them on your disk after closing an app (or the IDE) its because the system didn't shut down correctly. You are doing it correctly to remove the memory table, however, I'd ditch the .Prepare. In these circumstances I don't think its doing much and you certainly don't want to run the sql again to remove the memory table when its already gone I've put my alternative function below. Roy Lambert procedure ZapInMemoryTable(WhichTable: TDBISAMTable); begin try if Assigned(WhichTable) and WhichTable.Exists then begin WhichTable.DisableControls; WhichTable.Close; WhichTable.Exclusive := True; if not (WhichTable.Bof and WhichTable.Eof) then WhichTable.EmptyTable; WhichTable.DeleteTable; FreeAndNil(WhichTable); end; except end; end; |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Friday, September 20, 2024 at 05:39 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |