[SQL] Subquery problems
Hello,
I'm having another "problem", I have a function that declares 12
variable, one per month and each them execute a select like bellow:
*DIV_MES01 := (select count(distinct production_date) from production
where extract(month from production_date) = '01' and extract(year from
production_date) = EXTRACT(YEAR FROM current_date));
*Then, I need to check if the variable is equal 0:
*IF DIV_MES01 = 0 THEN
DIV_MES01 := 1;
END IF;
*Finally, I perform the following query:
*SELECTcast(((sum(A.qty_employees_total)
-(sum(A.qty_absence)
-sum(A.qty_vacation)
-sum(A.qty_diseased)
-sum(A.qty_indirect)
-sum(A.qty_transferred))
+sum(A.qty_received))/DIV_MES01) AS integer),
C.id_production_area,
cast('01' as text) AS mes
FROM head_count A, machine B, machine_type C
WHERE EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
current_date)
AND EXTRACT(MONTH FROM head_count_date) = '01'
AND A.id_machine = B.id_machine
AND B.id_machine_type = C.id_machine_type
GROUP BY C.id_production_area, B.id_machine_type
*Doing it, I need to perform 12 querys united by "UNION", what I want to
do is unify it in only one query, I tryed with the query bellow:
*SELECT date_trunc('month', A.head_count_date)::date as head_date,
cast(((sum(A.qty_employees_total)
-(sum(A.qty_absence)
-sum(A.qty_vacation)
-sum(A.qty_diseased)
-sum(A.qty_indirect)
-sum(A.qty_transferred))
+sum(A.qty_received))/(select count(distinct production_date)
from production
whereextract(month from
production_date) = EXTRACT(MONTH FROM date_trunc('month',
A.head_count_date)::date)
and extract(year from
production_date) = EXTRACT(YEAR FROM current_date))) AS integer),
C.id_production_area
FROM head_count A, machine B, machine_type C
WHEREdate_trunc('month', A.head_count_date)::date BETWEEN
date_trunc('month', current_date - (EXTRACT(MONTH FROM
current_date)-1) * interval '1 month')::date
AND date_trunc('month', current_date)::date
AND A.id_machine = B.id_machine
AND B.id_machine_type = C.id_machine_type
GROUP BY C.id_production_area, B.id_machine_type, head_count_date,head_date
ORDER BY id_production_area, head_count_date,head_date DESC
*But the results aren't what I want.
What I trying to do is possible?
I appreciate any help.
Thanks
Re: [SQL] tsearch2() trigger and domain types...
On Monday 18 June 2007 17:59:50 Tom Lane wrote: > "Michael D. Stemle, Jr." <[EMAIL PROTECTED]> writes: > > For standardization in my database I use a domain (login_t) for my login > > column in my profile table. > > > > Well, here's what I get upon every update and insert to the profile > > table: WARNING: TSearch: 'login' is not of character type > > The tsearch trigger seems to insist that the column be text, varchar(n), > or char(n) ... no domains need apply :-( > > I'm not real sure why it doesn't just invoke the column's output > function and be datatype-agnostic. Or at least do that when the > shortcut "I know what text looks like" path isn't applicable. Is there any way to cast these column datatypes in the trigger to fool it, or is the only way to fix this to reconstruct the table without domains? I'd really prefer not to abandon domains if at all possible. -- ~ manchicken <>< (A)bort, (R)etry, (I)nfluence with large hammer. 09 F9 11 02 9D 74 E3 5B D8 41 56 C5 63 56 88 C0 Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] tsearch2() trigger and domain types...
manchicken <[EMAIL PROTECTED]> writes:
> Is there any way to cast these column datatypes in the trigger to fool it,
No, you'd have to modify the trigger source code. It'd be a pretty
trivial change to allow domains over textual types:
continue;
}
oidtype = SPI_gettypeid(rel->rd_att, numattr);
+ oidtype = getBaseType(oidtype);
/* We assume char() and varchar() are binary-equivalent to text
*/
if (!(oidtype == TEXTOID ||
oidtype == VARCHAROID ||
oidtype == BPCHAROID))
{
elog(WARNING, "TSearch: '%s' is not of character type",
trigger->tgargs[i]);
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] [GENERAL] Subquery problems
Original Message
Subject: Re:[GENERAL] Subquery problems
From: Merlin Moncure <[EMAIL PROTECTED]>
To: Ranieri Mazili <[EMAIL PROTECTED]>
Date: 19/6/2007 10:40
On 6/19/07, Ranieri Mazili <[EMAIL PROTECTED]> wrote:
Hello,
I'm having another "problem", I have a function that declares 12
variable,
one per month and each them execute a select like bellow:
DIV_MES01 := (select count(distinct production_date) from production
where
extract(month from production_date) = '01' and extract(year from
production_date) = EXTRACT(YEAR FROM current_date));
Then, I need to check if the variable is equal 0:
IF DIV_MES01 = 0 THEN
DIV_MES01 := 1;
END IF;
Finally, I perform the following query:
SELECTcast(((sum(A.qty_employees_total)
-(sum(A.qty_absence)
-sum(A.qty_vacation)
-sum(A.qty_diseased)
-sum(A.qty_indirect)
-sum(A.qty_transferred))
+sum(A.qty_received))/DIV_MES01) AS integer),
C.id_production_area,
cast('01' as text) AS mes
FROM head_count A, machine B, machine_type C
WHERE EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
current_date)
AND EXTRACT(MONTH FROM head_count_date) = '01'
AND A.id_machine = B.id_machine
AND B.id_machine_type = C.id_machine_type
GROUP BY C.id_production_area, B.id_machine_type
Doing it, I need to perform 12 querys united by "UNION", what I want
to do
is unify it in only one query, I tryed with the query bellow:
SELECT date_trunc('month', A.head_count_date)::date as head_date,
cast(((sum(A.qty_employees_total)
-(sum(A.qty_absence)
-sum(A.qty_vacation)
-sum(A.qty_diseased)
-sum(A.qty_indirect)
-sum(A.qty_transferred))
+sum(A.qty_received))/(select count(distinct
production_date)
from production
whereextract(month from
production_date) = EXTRACT(MONTH FROM date_trunc('month',
A.head_count_date)::date)
and extract(year from
production_date) = EXTRACT(YEAR FROM current_date))) AS integer),
C.id_production_area
FROM head_count A, machine B, machine_type C
WHEREdate_trunc('month', A.head_count_date)::date BETWEEN
date_trunc('month', current_date - (EXTRACT(MONTH FROM
current_date)-1) * interval '1 month')::date
AND date_trunc('month', current_date)::date
AND A.id_machine = B.id_machine
AND B.id_machine_type = C.id_machine_type
GROUP BY C.id_production_area, B.id_machine_type,
head_count_date,head_date
ORDER BY id_production_area, head_count_date,head_date DESC
But the results aren't what I want.
What I trying to do is possible?
I appreciate any help.
Thanks
sure!.
SELECTcast(((sum(A.qty_employees_total)
-(sum(A.qty_absence)
-sum(A.qty_vacation)
-sum(A.qty_diseased)
-sum(A.qty_indirect)
-sum(A.qty_transferred))
+sum(A.qty_received))/DIV_MES01) AS integer),
C.id_production_area,
cast(DIV_MES01 as text) AS mes
FROM head_count A, machine B, machine_type C,
(
select case when ct = 0 then 1 else ct end as DIV_MES01 from
(
select count(distinct production_date) as ctfrom production where
extract(year from production_date) = EXTRACT(YEAR FROM current_date)
) q
) D
WHERE EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
current_date)
AND EXTRACT(MONTH FROM head_count_date) = DIV_MES01
AND A.id_machine = B.id_machine
AND B.id_machine_type = C.id_machine_type
GROUP BY C.id_production_area, B.id_machine_type;
ok, I didn't syntax check this monster, but it should give you a
start...the trick is to use an 'inline view' to expand your variable
list into a set.
merlin
---(end of broadcast)---
TIP 6: explain analyze is your friend
On this way didn't work, I wanna do only one query to return the data of
entire year, not only one month, but thanks for try.
If someone have an idea of how do it, please, help :D
Thanks
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] tsearch2() trigger and domain types...
On Tuesday 19 June 2007 09:08:23 Tom Lane wrote: > oidtype = getBaseType(oidtype); I patched this in a FreeBSD 6.2 port. Attached is the patch. Thanks a lot for the help on this. It works like a charm. -- ~ manchicken <>< (A)bort, (R)etry, (I)nfluence with large hammer. 09 F9 11 02 9D 74 E3 5B D8 41 56 C5 63 56 88 C0 Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html *** tsvector.c Tue Jun 19 12:39:06 2007 --- tsvector.c.patched Tue Jun 19 12:38:37 2007 *** tsearch2(PG_FUNCTION_ARGS) *** 890,895 --- 890,896 continue; } oidtype = SPI_gettypeid(rel->rd_att, numattr); + oidtype = getBaseType(oidtype); /* We assume char() and varchar() are binary-equivalent to text */ if (!(oidtype == TEXTOID || oidtype == VARCHAROID || ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] joining a table whose name is stored in the primary record
Andrew Sullivan wrote: On Sun, Jun 17, 2007 at 04:39:51AM -0400, John Gunther wrote: functions and using subqueries without success. I think I need someone to point me in the right conceptual direction. Well, the right SQL-esque conceptual direction is not to have different tables at all. That's not a very normal-form thing to do, because the data has been broken into pieces dependent on the data itself, rather than the kind of data it is. A Well, Andrew, you're certainly right but I made an exception because of the data needs. The real application is a sales tax table, where the tax jurisdiction can depend on any element of the address hierarchy. In different areas, it could depend on zip/postal code, city, state/province, nation, or even street address. I originally considered storing all address elements in one giant table with parent/child relationships (zip 11208's parent is Brooklyn, Brooklyn's parent is Kings County, Kings County's parent is NY, etc but brief analysis showed that address elements frequently have more than one parent. So I decided to keep the elements in separate tables (nation, city, street, zip, etc) to allow more complex relationships. That led me to the sales tax jurisdiction problem. I could just assign a tax jurisdiction to every street segment but that would create a daunting data maintenance problem as it requires propagating jurisdiction changes down through every segment and ensuring every valid street segment has a record. It's an interesting problem. Another respondent suggested a programmed function that will do the job. I was hoping for a pure SQL solution but his approach will definitely work. John
Re: [SQL] joining a table whose name is stored in the primary record
Andreas Kretschmer wrote: create or replace function zip_foo(OUT out_id int, OUT out_name text, OUT out_name2 text) returns setof record as $$ declare my_rec RECORD; my_name TEXT; begin for my_rec in select id, name, parent_tbl, parent_id from zip LOOP execute 'select name from ' || my_rec.parent_tbl || ' where id = ' || my_rec.parent_id || ';' into my_name; out_id := my_rec.id; out_name := my_rec.name; out_name2 := my_name; return next; end loop; end; $$ language plpgsql; Thanks, Andrew. I was hoping for a pure SQL solution but your idea will certainly work. John ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] joining a table whose name is stored in the primary record
On Tue, Jun 19, 2007 at 02:02:46PM -0400, John Gunther wrote: > Well, Andrew, you're certainly right but I made an exception because of > the data needs. Ah, well, in that case, you'll need something other than SQL for sure. A function as suggested is probably your friend. A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] separating improperly grouped page views
On Sun, 17 Jun 2007, Jeff Frost wrote: - Seq Scan on page_view pv1 (cost=0.00..11529031.34 rows=3580205 width=239) SubPlan -> Result (cost=1.58..1.59 rows=1 width=0) InitPlan -> Limit (cost=0.00..1.58 rows=1 width=8) -> Index Scan Backward using page_view_visit_id_stamp_idx on page_view pv2 (cost=0.00..625.39 rows=397 width=8) Index Cond: ((visit_id = $0) AND (stamp < $1)) Filter: (stamp IS NOT NULL) -> Result (cost=1.58..1.59 rows=1 width=0) InitPlan -> Limit (cost=0.00..1.58 rows=1 width=8) -> Index Scan Backward using page_view_visit_id_stamp_idx on page_view pv2 (cost=0.00..625.39 rows=397 width=8) Index Cond: ((visit_id = $0) AND (stamp < $1)) Filter: (stamp IS NOT NULL) (14 rows) Compared to: --- Seq Scan on page_view pv1 (cost=0.00..2622541458.55 rows=3596473 width=237) SubPlan -> Result (cost=364.56..364.57 rows=1 width=0) InitPlan -> Limit (cost=0.00..364.56 rows=1 width=8) -> Index Scan Backward using page_view_stamp_idx on page_view pv2 (cost=0.00..153481.58 rows=421 width=8) Index Cond: (stamp < $1) Filter: ((stamp IS NOT NULL) AND (visit_id = $0)) -> Result (cost=364.56..364.57 rows=1 width=0) InitPlan -> Limit (cost=0.00..364.56 rows=1 width=8) -> Index Scan Backward using page_view_stamp_idx on page_view pv2 (cost=0.00..153481.58 rows=421 width=8) Index Cond: (stamp < $1) Filter: ((stamp IS NOT NULL) AND (visit_id = $0)) (14 rows) And throwing the ORDER BY back in reduces the cost even more! QUERY PLAN --- Subquery Scan x (cost=0.00..5815824.15 rows=3629753 width=1186) -> Index Scan using page_view_visit_idx on page_view pv1 (cost=0.00..5743229.09 rows=3629753 width=237) SubPlan -> Result (cost=1.51..1.52 rows=1 width=0) InitPlan -> Limit (cost=0.00..1.51 rows=1 width=8) -> Index Scan Backward using page_view_visit_id_stamp_idx on page_view pv2 (cost=0.00..608.41 rows=402 width=8) Index Cond: ((visit_id = $0) AND (stamp < $1)) Filter: (stamp IS NOT NULL) (9 rows) Now we only have to do that index scan once. :-) I had foolishly taken that out to see if the sort was killing me and forgot to put it back in. So now it's: Subquery Scan x (cost=0.00..5815824.15 rows=3629753 width=1186) vs Seq Scan on page_view pv1 (cost=0.00..11529031.34 rows=3580205 width=239) vs Seq Scan on page_view pv1 (cost=0.00..2622541458.55 rows=3596473 width=237) -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] join problem
[Please don't top post as it makes the discussion more difficult to follow, and please reply to the list so that others may benefit from and participate in the discussion.] On Jun 19, 2007, at 14:17 , A. R. Van Hook wrote: Michael Glaesemann wrote: On Jun 13, 2007, at 8:19 , A. R. Van Hook wrote: I have join problem: "select i.ivid, v.eventdate, v.deposit, v.invdate, cai.db, sum(i.tax) as tax, sum(i.tax + i.rowtot) as totalP, (sum(i.tax + i.rowtot) - v.deposit) as balance from invoice v left outer join invoiceitems i on v.ivid = i.ivid where v.cusid = $cusid and v.cusid = cai.cusid group by i.ivid, v.eventdate, v.deposit, v.invdate, cai.db ERROR: missing FROM-clause entry for table "cai" If I add cai to the from clause "from invoice v, cai, I get ERROR: missing FROM-clause entry for table "cai" ERROR: invalid reference to FROM-clause entry for table "v" I think you may need to change the order of the JOIN clause. Does this work? SELECT i.ivid , v.eventdate , v.deposit , v.invdate , cai.db , sum(i.tax) as tax , sum(i.tax + i.rowtot) as totalP , (sum(i.tax + i.rowtot) - v.deposit) as balance FROM cai JOIN invoice v ON (cai.cusid = v.cusid) LEFT JOIN invoiceitems i ON (v.ivid = i.ivid) WHERE v.cusid = $cusid GROUP BY i.ivid , v.eventdate , v.deposit , v.invdate , cai.db Note I've also moved the cai.cusid = v.cusid into the JOIN condition (which is what it is). Also, if cai doesn't have a ivid column and invoiceitems doesn't have a cusid column, you can use USING (cusid) and USING (ivid) rather than ON (cai.cusid = v.cusid) and ON (v.ivid = i.ivid), which has the nice property of outputing only one join column rather than one column for each table, (i.e., only one cusid column rather than one each for cai and invoice). Michael Glaesemann grzm seespotcode net This solution works fine but the summations are reporting individual row data. i.e. ivid | eventdate | deposit | invdate | db | tax | totalp | balance --++-++--++- +- 7610 | 10/15/2005 |0.00 | 05/05/2005 | 0.00 | 11.490 | 170.490 | 170.490 7868 | 10/15/2005 | 85.25 | 06/04/2005 | 0.00 || | 8620 | 10/15/2005 | 85.24 | 09/07/2005 | 0.00 | 0.000 | 0.000 | -85.240 can the query be modified to get the overall totals of each (db,tax,totalp,balance)? If you want totals for db, tax, totalp, and balance, you'll need to modify the rows that are returned (the SELECT list) and the GROUP BY clause to group those together. I don't know what you want to the totals over: eventdate? ivid? Give it a try and if you still have questions, be sure to post what you've attempted. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Fwd: [pgsql-es-ayuda] Ejecutar \copy desde VB
-- Forwarded message -- From: Carlos Alberto Silva <[EMAIL PROTECTED]> Date: Jun 19, 2007 2:00 PM Subject: Re: [pgsql-es-ayuda] Ejecutar \copy desde VB To: Jaime Casanova <[EMAIL PROTECTED]> Yo lo harÃa de otra manera. Una rutina q lea el archivo de texto en VB (nada complicado de hacer) y luego los inserts correspondientes en la tabla via ODBC o como sea q me conecte a la base. IMHO.Carlos Jaime Casanova escribió: On 6/19/07, Gabriel Hermes Colina Zambra <[EMAIL PROTECTED]> wrote: > Otra burda manera pero menos complicada, es crear un .bat que maneje set pgpassword=clavedeusuariopostgres en vez de setear pgpassword deberias usar un archivo pgpass.conf http://www.postgresql.org/docs/8.2/static/libpq-pgpass.html y ejecutar el bat con la instruccion shell desde visual basic, quiza teniendo un lnk apuntando al bat. Es una idea burda pero funciona. pero creo, no estoy seguro porque Chapiliquen no lo dejo claro, que quiere hacer eso desde un cliente y no desde el servidor... y dudo mucho que quiera tener recursos compartidos en cada cliente solo para eso... ahora que si esta dispuesto esa seria una solucion (claro que, segun yo, necesitaria ejecutar el bat en el servidor) -- Atentamente, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
