[SQL] unsubscribe
unsubscribe
[SQL] Selecting different views from a Boolean expression
Hi there, I have a web based reporting system that I am coding. I need to be able to view statistics by the calendar year or the fiscal year depending on what the user selects. My issue is how do I change y select statements to change the column output? Example IncidentJan Feb Mar Apr May June JulyAug Sep Oct Nov Dec === === === === === === Falls 1 1 0 0 0 0 0 0 0 0 0 0 Overdose0 1 0 1 1 1 0 0 0 1 1 0 But if the user selects fiscal year the output could be: IncidentJun Jul Aug septOct Nov Dec Jan Feb Mar Apr May === === === === === === Falls 1 1 0 0 0 0 0 0 0 0 0 0 Overdose0 1 0 1 1 1 0 0 0 1 1 0 I have created select views for each month possibility, but I don't know how to select the view I need from an IF/Then/else statement!! Any help would be appreciated. Thanks
[SQL] How too select different views using a IF/THEN/ELSE procedure ?
Below is a select statement that select incidents by month. I need a function or a method to select differents views that will show the Month columns is a different order. Say Apr - Mar for a fiscal year. I need to do something like an if/then/else statement that selects the correct view to use by a variable check. Any ideas what I can use or do ? select case public.incident.gender_code_id WHEN 31 THEN 'Male' WHEN 32 THEN 'Female' ELSE 'Non-Person' END, count (case extract ( month from public.incident.incident_date )WHEN 01 then 1 Else NULL END) as Jan, count (case extract ( month from public.incident.incident_date )WHEN 02 then 1 Else NULL END) as Feb, count (case extract ( month from public.incident.incident_date )WHEN 03 then 1 Else NULL END) as Mar, count (case extract ( month from public.incident.incident_date )WHEN 04 then 1 Else NULL END) as Apr, count (case extract ( month from public.incident.incident_date )WHEN 05 then 1 Else NULL END) as May, count (case extract ( month from public.incident.incident_date )WHEN 06 then 1 Else NULL END) as Jun, count (case extract ( month from public.incident.incident_date )WHEN 07 then 1 Else NULL END) as Jul, count (case extract ( month from public.incident.incident_date )WHEN 08 then 1 Else NULL END) as Aug, count (case extract ( month from public.incident.incident_date )WHEN 09 then 1 Else NULL END) as Sep, count (case extract ( month from public.incident.incident_date )WHEN 10 then 1 Else NULL END) as Oct, count (case extract ( month from public.incident.incident_date )WHEN 11 then 1 Else NULL END) as Nov, count (case extract ( month from public.incident.incident_date )WHEN 12 then 1 Else NULL END) as Dec, count (extract ( month from public.incident.incident_date )) as Total from public.incident GROUP BY public.incident.gender_code_id
[SQL] Using a variable as a view name in a select
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 ?
Re: [SQL] Using a variable as a view name in a select
Almost, in the table there are multiple different incidents.
Incident April May June July Aug
===
Falls1 0 1 0
0
Roof Area 0 1 0 0 2
Complaints.. 1 2 3 2 2
Etc ...
What I need to do is to be able to change the column heading to have a
different start and finish month
Etc ...
Incident Feb Mar Apr May June
==
Falls1 0 1 0
0
Roof Area 0 1 0 0 2
Complaints.. 1 2 3 2 2
The only way I can think of is to create 12 differents views with the
months in order and then concatenating the start_month and end_month
fields in the database to create the view name. Then do a select with
the created view name.
Select * from May_June;
.
.
.
From: Hilary Forbes [mailto:[EMAIL PROTECTED]
Sent: April 3, 2007 12:45 PM
To: Wilkinson, Jim
Cc: pgsql-sql@postgresql.org
Subject: RE: [SQL] Using a variable as a view name in a select
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:
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 ?
From: Hilary Forbes [ mailto:[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]> ]
Sent: April 3, 2007 10:14 AM
To: Wilkinson, Jim; pgsql-sql@postgresql.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 ( www.dmr.co.uk
<http://www.dmr.co.uk/> )
Direct tel 01689 889950 Fax 01689 860330
DMR is a UK registered trade mark of DMR Limited
**
Hilary Forbes
DMR Limited (UK registration 01134804)
A DMR Information and Technology Group company ( www.dmr.co.uk
<http://www.dmr.co.uk/> )
Direct tel 01689 889950 Fax 01689 860330
DMR is a UK registered trade mark of DMR Limited
**
[SQL] EXECUTE in a funtion to return a VIEW object ID
Hi there, I have tried many ideas to get this working but no luck. Can some show me or explain what is happening EXAMPLE == I am trying to read to 2 text fields to combine them togther to form the name of a VIEW. example SELECT * FROM ( 'april'||'may') ; I have tried the EXECUTE in a function to PREPARE a dynameic select call; EXECUTE 'SELECT * FROM ' || 'select tablename.text_field1 from tablename' || 'select tablename.text_field2 from tablename'; If tablename.text_field1 = "May" and tablename.text_field2 = "Aprl", this function only returns "MayApril" and not the columns and data that I expected. What am I doing wrong here? Has anyone done this before or have any ideas on how to do it ? Thanks
[SQL] Function to return a multiple colmn table or view
I am new to psql , so please be patient ! Can someone please provide a small quick example of a a function that take 1 paramater and based on that parameter, returns a table or view ? Etc Note this is just a abstract of the functon, not a working function call !!! Create function viewtest( start_month) If $1 = 'April' Then Select * from april_view; Return ; Let me know how to do this. Thanks
Re: [SQL] hi
Select sno AS "SNO", SELECT value AS "VALUE", get_sum(value) as SUM from temp; From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Penchalaiah P. Sent: April 24, 2007 8:09 AM To: pgsql-sql@postgresql.org Subject: [SQL] hi Hi I have the data like this in temp table SQL> Select sno, value from temp; SNO Value 1 650.00 2 850.00 3 640.00 3 985.00 5 987.00 9 9864.00 7 875.00 Now, I want display the result like this... SQL> Select sno, value, get_sum(value) as sum from temp; SNO Value SUM 1 650.00 650.00 2 850.00 1500.00 --650+850 3 640.00 2140.00 --1500+640 3 985.00 3125.00 -- 2140+985 5 987.00 4112.00 9 9864.00 13976.00 7 875.00 14851.00 Any one can tell me query for this .. I don't want any procedure or function... Thanks & Regards Penchal Reddy Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
