Quick Links

Re: Using a variable as a view name in a select

From:	Hilary Forbes <hforbes(at)dmr(dot)co(dot)uk>
To:	"Wilkinson, Jim" <Jim(dot)Wilkinson(at)cra-arc(dot)gc(dot)ca>
Cc:	pgsql-sql(at)postgresql(dot)org
Subject:	Re: Using a variable as a view name in a select
Date:	2007-04-03 16:45:00
Message-ID:	200704031638.l33GcPe3025200@tamar.dmr.co.uk
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-sql

<html>
<body>
Jim 
So let's suppose you have a "master" table of
incidents 
incident_no (serial) 
incident_date (timestamp) 
other fields 
My understanding is that you now want to eg count the incidents starting
in a given month and going forwards for 12 months, grouping the results
by month.  Have I understood the problem? 
If so here goes: 
Set up a table hftest 
incident serial 
incdate timestamp 
SELECT * from hftest; 
incident |       incdate 
----------+--------------------- 
     1000 | 2006-05-03 00:00:00 
     1001 | 2006-04-03 00:00:00 
     1002 | 2006-04-01 00:00:00 
     1003 | 2006-12-08 00:00:00 
     1004 | 2007-02-28 00:00:00 
     1005 | 2007-08-03 00:00:00 
Now: 
SELECT max(to_char(incdate,'Mon')) ,count(incident) from hftest WHERE
date_trunc('month',incdate) >='2006/04/01' AND
date_trunc('month',incdate)<=date_trunc('month',date '2006/04/01' +
interval '12 months') GROUP BY date_trunc('month',incdate) ORDER BY
date_trunc('month',incdate); 
 max | count 
-----+------- 
 Apr |     2 
 May |     1 
 Dec |     1 
 Feb |     1 
  
 which is almost what you want.  To get the missing months with
zeroes, I think you probably need a table of months and to use a left
outer join but you may have found a better way by now! 
 Now I have NO idea on the efficiency of this as I rather suspect
all those date_trunc functions may have an adverse effect! 
Best regards 
Hilary 
  
 
 
 
At 16:44 03/04/2007, you wrote: 
<blockquote type=cite class=cite cite="">Hi
Hilary, 
I am trying to produce reports where the user can select a different
fiscal year starting month.  From this I would select the correct
table view to produce the reports in the correct month order by
column 
  
Select * from table_view; 
  
Incident            
April      May     
June     July     
Aug      . 
=============================================== 
Falls                
1         
0         
1         
0          0 
. 
. 
. 
. 
  
Can you think of another way to do this ? 
  
  
<hr>
<div align="center"></div>
From: Hilary Forbes
[<a href="mailto:hforbes(at)dmr(dot)co(dot)uk" eudora="autourl">
mailto:hforbes(at)dmr(dot)co(dot)uk</a>] 
Sent: April 3, 2007 10:14 AM 
To: Wilkinson, Jim; pgsql-sql(at)postgresql(dot)org 
Subject: Re: [SQL] Using a variable as a view name in a
select 
  
Jim 
My initial reaction is what are you trying to achieve?  Surely you
could have one underlying table with dates in it and 
SELECT * from mytable WHERE date1>='2007/04/01' AND
date2<='2007/05/01'; 
but otherwise, like John, I would use an external scripting language to
create the table name. 
Hilary 
At 14:04 03/04/2007, Wilkinson, Jim wrote: 
 
I have
created a view, called april_may.   I need to select this view
by combineing to fields in the database to create the view name etc
 
  
Create view as select * from table_X; 
  
I need to do something like this 
  
Select * from (select table.start_month||_||table.end_month); 
================== 
Start_month  = april 
End_month = May 
  
What I what to pass to the select is the combination of the 2 fields as
the view name. 
  
Any ideas ? 
 
Hilary Forbes 
DMR Limited (UK registration 01134804) 
A DMR Information and Technology Group company (
<a href="http://www.dmr.co.uk/" eudora="autourl">www.dmr.co.uk</a>) 
Direct tel 01689 889950 Fax 01689 860330 
DMR is a UK registered trade mark of DMR Limited 
**********************************************************
</blockquote>
<x-sigsep></x-sigsep>
Hilary Forbes 
DMR Limited (UK registration 01134804) 
A DMR Information and Technology Group company
(<a href="http://www.dmr.co.uk/" eudora="autourl">
www.dmr.co.uk</a>) 
Direct tel 01689 889950 Fax 01689 860330 
DMR is a UK registered trade mark of DMR Limited 
**********************************************************</body>
</html>

Attachment	Content-Type	Size
unknown_filename	text/html	5.1 KB

In response to

Re: Using a variable as a view name in a select at 2007-04-03 14:14:22 from Hilary Forbes

Responses

Re: Using a variable as a view name in a select at 2007-04-03 17:18:02 from Wilkinson, Jim

Browse pgsql-sql by date

	From	Date	Subject
Next Message	Wilkinson, Jim	2007-04-03 17:18:02	Re: Using a variable as a view name in a select
Previous Message	Andrew Sullivan	2007-04-03 16:16:06	Re: Update problem.