Re: [SQL] select vs. select count
> select order_id from > (select o.order_id from orders o join order_lines ol using (order_id) > where o.time > '2006-01-01T00:00' and o.time < '2007-01-01T00:00' > and (ol.item_id = 10 or ol.item_id = 11 or ol.item_id = 12) group by > o.order_id) as prints > inner join > (select ho.order_id from orders ho join order_lines hol using (order_id) > where o.time > '2006-01-01T00:00' and o.time < '2007-01-01T00:00' > and (ol.item_id = 20 or ol.item_id = 21 or ol.item_id = 22) group by > o.order_id) as gifts > using (order_id) > except select order_id from > (select ho.order_id from orders ho join order_lines hol using (order_id) >where o.time > '2006-01-01T00:00' and o.time < '2007-01-01T00:00' >and (ol.item_id = 30 or ol.item_id = 31 or ol.item_id = 32) group > by o.order_id) as extra; > When I do the 'select order_id' I get (after scrolling down): > (1960 rows) > But when I do a 'select count(order_id) I get: > 2063 You did not show us exactly what you did, but if you simply blindly replaced "select order_id" with "select count(order_id)" in the first line above, then what you would have is select count(order_id) from some-stuff except select order_id from some-other-stuff so what you would get is either the count of some-stuff, or nothing at all if any of the order_ids in some-other-stuff chanced to equal the count. In any case it would not be the count of what the original EXCEPT query returned, unless the EXCEPT wasn't eliminating any rows. You need to wrap SELECT count(order_id) FROM ( ... ) around the entire EXCEPT query to get what you want. Thank you for your advise. It was the except-clause which gave me some "headache". The query now looks like what you suggest: select count(order_id) (select order_id from (select o.order_id from ... join orderlines ol using (order_id) where ... as prints inner join (select o.order_id from ... join orderlines ol using (order_id) where ... as extra using (order_id) except select order_id from (select o.order_id from ... join orderlines ol using (order_id) where ... as gifts) as orders; regards Claus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] using sql on v7.4 server to feed stored procedure
I just found out that I posted this to the wrong list before, so if
this ends up as a cross-post, I'm sorry.
I've created some sql scripts to update a database in version 8.x and
now I'm trying to get them to run on servers using v7.4
The following statement works on 8.x servers, but not on 7.4 and I
can't figure out how to get it to operate in the earlier version:
select calc_cum_gpa_mp(marking_period_id::TEXT)
from (select distinct marking_period_id from student_mp_stats) as
sms1;
I get the error:
ERROR: syntax error at or near "("
SQL state: 42601
Context: compile of PL/pgSQL function "calc_cum_gpa_mp" near line 8
Can anyone point me in the right direction? I've searched everywhere
for how to use stored procedures in sql queries, and haven't found how
to use the subquery to feed values to the stored procedure. It almost
looks like I'd need to create another plpgsql function with a loop to
do the same thing.
Brian
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] using sql on v7.4 server to feed stored procedure
"Brian Jarrett" <[EMAIL PROTECTED]> writes:
> The following statement works on 8.x servers, but not on 7.4 and I
> can't figure out how to get it to operate in the earlier version:
> select calc_cum_gpa_mp(marking_period_id::TEXT)
> from (select distinct marking_period_id from student_mp_stats) as
> sms1;
> I get the error:
> ERROR: syntax error at or near "("
> SQL state: 42601
> Context: compile of PL/pgSQL function "calc_cum_gpa_mp" near line 8
> Can anyone point me in the right direction?
You need to look into the referenced plpgsql function; this statement in
itself is not at fault.
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] function return array
Hi, I am using Postgres 8.1.0. I have a requirement. I will create a function accepting few parameters. This will check into various tables and give back an array of values. Is it possible to return an array from the function? Please guide me. Regards skarth _ Your friends are close to you. Keep them that way. http://spaces.live.com/signup.aspx
[SQL] plpgsql function return array
Hi, I am using Postgres 8.1.0. I have a requirement. I will create a function accepting few parameters. This will check into various tables and give back an array of values. I want to use the pgpsql block. I know that we can create using language sql. Is it possible to return an array from the function? Please guide me. Regardsskarthi _ Take a break and play crossword puzzles - FREE! http://games.msn.com/en/flexicon/default.htm?icid=flexicon_ wlmemailtaglinemarch07
