Re: URGENT!!! SELECT statement please help
От | Ben |
---|---|
Тема | Re: URGENT!!! SELECT statement please help |
Дата | |
Msg-id | Pine.LNX.4.64.0603151521510.1276@localhost.localdomain обсуждение исходный текст |
Ответ на | URGENT!!! SELECT statement please help (sconeek@gmail.com) |
Список | pgsql-general |
Have you considered using a stored proceedure? It seems like it might easily give you the logic you're after. http://www.postgresql.org/docs/8.1/static/xplang.html On Sun, 12 Mar 2006, sconeek@gmail.com wrote: > hi all, > > i have a web based java application with a postgres db. > > now i am trying to generate a temp table which contains all hour > records for a selected date range. eg. if the user selects 2006-03-14 > as from and 2006-03-14 as to, the system should insert 24 hour records > with their individual time stamps (eg 2006-03-14 12:00:00). > > now currently, i have a select statement which searches another table, > finds data and then inserts them. but the problem is that if there is > no data for an hour, it does not insert anything. > > now i can think of a couple of solutions, > 1. insert additional records using a second statement, which compares > the table and inserts any missing records > 2. insert data as usual and then using a second statement, compare the > content and insert any missing records. > > my current implementation is: > " INSERT INTO temp_table (edit_time,edit_time_count) " + > " SELECT to_timestamp(to_char (last_edit_timestamp,'YYYY-MM-DD > HH24:00:00'), > 'YYYY-MM-DD HH24:00:00')AS edit_time, " + > " count(to_char (last_edit_timestamp,'YYYY-MM-DD HH24:00:00')) as > edit_time_count " + > " FROM " + tableName + " " + sqlWhereStr + > " GROUP BY to_char (last_edit_timestamp,'YYYY-MM-DD HH24:00:00') " + > " ORDER BY to_char (last_edit_timestamp,'YYYY-MM-DD HH24:00:00'); "; > > the problem is that if the select statement does not return anything > for a particular hour record, that record is not inserted. now i would > like to have a table with all records including hours with 0 count. > > can somebody please help me urgently. i would really appreciate it. > thanks. > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >
В списке pgsql-general по дате отправления: