[SQL] A sys func for a->b, b->c => a->c ?
Good Morning, Someone has better solution about the following query situation? table test with two columns with primary key (id1, id2) id1, id2 = 12 13 12 3 13 5 Query conditions: = (1) a->b => b->a (2) a->b and a->c => a->c Expected return: id1 id2 === 1 2 1 3 112 21 23 212 31 32 312 12 1 12 2 12 3 13 5 I did: create view v_test AS select id1 , id2 from test union select id2, id1 from test; ( SELECTa.id1 , b.id2 FROM v_test AS a left join v_test AS b ON (a.id2 = b.id1) WHERE a.id1 <> b.id2 ) UNION ( SELECT id1, id2 FROM v_test ) order by id1 ; The query is a bit complex, do we have a better system func or query for this? Thanks a lot! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] apparent RI bug
it appears I have a broken RI in my db. call_individual.clh_id references call_household.clh_id \d call_individual ... Foreign-key constraints: "call_individual_clh_id_fkey" FOREIGN KEY (clh_id) REFERENCES call_household(clh_id) ON DELETE CASCADE however: development=# select clh_id from call_individual cli where not exists( select 1 from call_household clh where clh.clh_id=cli.clh_id ); clh_id 14691 should not matter, but call_individual has a pre-delete trigger that simply raises an exception to prevent deletions: raise exception 'calls may not be deleted'; You rock. That's why Blockbuster's offering you one month of Blockbuster Total Access, No Cost. http://tc.deals.yahoo.com/tc/blockbuster/text5.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] update with join
I've create table types( typeid int, special boolean not null ); create table methods( methodid int, typeid references types(typeid), ); create table orders( orderid int ); create table order_payments( payid int orderid references order(orderid), methodid references method(methodid), issued boolean not null default false ); orderid payid methodid special 1 1 1t 1 2 2t 1 3 3t 1 4 4f 1 5 4f I'd like to chose one payid If the payid is "special" just set issued to true for that payid, leave the other unchanged. If the payid is not "special" set issued for all the payid in the same order. eg. So if payid=4 I'd have orderid payid methodid special issued 1 1 1t t 1 2 2t t 1 3 3t t 1 4 4f t 1 5 4f t and if payid=2 orderid payid methodid special issued 1 1 1t f 1 2 2t t 1 3 3t f 1 4 4f f 1 5 4f f This stuff below doesn't work: update order_payments set issued=true where payid in ( select p.payid from order_payments p join methods as m on m.methodid=p.methodid join types as t on m.typeid=t.typeid where (p.orderid=%d and not t.special) or p.payid=%d); and I can understand why but I can't rewrite it to make it work. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Asking GO on SQL SERVER
Hi All.. Anybody knows what is "GO" (SQL Server) on Postgres? Thanks. -- --- "He who is quick to become angry will commit folly, and a crafty man is hated"
Re: [SQL] Asking GO on SQL SERVER
Otniel Michael wrote: Hi All.. Anybody knows what is "GO" (SQL Server) on Postgres? Thanks. -- --- "He who is quick to become angry will commit folly, and a crafty man is hated" semi-colon. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Asking GO on SQL SERVER
On Thu, Apr 3, 2008 at 7:14 AM, Otniel Michael <[EMAIL PROTECTED]> wrote: > Hi All.. > > Anybody knows what is "GO" (SQL Server) on Postgres? > If you are using the interactive terminal psql, then you can use \g meta command select 1+2 \g expect results here. Remember that this is a feature of psql; so if you are using ODBC, JDBC etc, it won't work from those interfaces. Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
Re: [SQL] Asking GO on SQL SERVER
Hem... I think postgresql do not like that.
For this example in function spEAR470.
When "open Phasil for" not yet finishing, postgresql do "Delete from
ear470_02 where sessid = Psess;". And this function becoming ERROR.
But this case happen when this function call from 3 users in same time.
Any solution please?
Thanks before.
Create or Replace Function spEAR470 (refcursor,varchar,char,char,char)
returns refcursor
as '
declare
Phasilalias for $1;Psessalias for $2;
PTglFromalias for $3; PTglTo alias for $4;
Psbmscdalias for $5;
jumlah integer;
waktu text;
begin
Select '' Begin - spEAR470 = '' || timeofday() into waktu;
Raise Notice ''%'',waktu;
Delete from ear470_01 where sessid = Psess;
Delete from ear470_02 where sessid = Psess;
/* Rekap data transaksi proses produksi untuk bulan dan mesin yang
bersangkutan */
insert into ear470_01(sessid,sbmscd,sbmsnm,wipcod,bjcod,
wasgrpnm,wastcd,wastds,
qtywaste,unit,qtywstbesar,qtywstkecil,qtykonv,
running,runbesar,runkecil,runkonv)
select PSess,B.sbmscd,D.SBMSNM,B.wipcod,,
coalesce(C.wasgrpnm,),coalesce(A.wastcd,),coalesce(C.wastds,),
sum(coalesce(A.qtywst,0)),E.brunin,0,0,sum(A.qtywst),
0,0,0,0
from B
left outer join A on B.jentrn=A.jentrn and B.thbltr=A.thbltr and
B.nortrn=A.nortrn
and A.brgcod in (''WST')
left outer join C on C.wastcd=A.wastcd
inner Join D On D.SBMSCD = B.SBMSCD
inner join E On B.wipcod = E.brgcod
where B.TGLTRN between PTglFrom and PTglTo and Trim(B.sbmscd) like
(PSbmscd)
and B.gdskcd = ''S'' and B.sbskcd <> ''P''
group by
B.sbmscd,B.wipcod,A.wastcd,C.wastds,E.brunin,D.SBMSNM,C.wasgrpnm;
Select '' spEAR470 - 9 = '' || timeofday() into waktu;
Raise Notice ''%'',waktu;
insert into
ear470_02(sessid,sbmscd,sbmsnm,wasgrpnm,wastcd,wastds,qtywaste,unit,qtykonv,running,runkonv,activetime)
select
sessid,sbmscd,sbmsnm,wasgrpnm,wastcd,wastds,sum(qtywaste),unit,sum(qtykonv),0,0,0
from ear470_01
where sessid = Psess
group by sessid,sbmscd,sbmsnm,wasgrpnm,wastcd,wastds,unit;
Select '' spEAR470 - 10 = '' || timeofday() into waktu;
Raise Notice ''%'',waktu;
/* Put the result into hasil variable */
open Phasil for
select
SBMSCD,SBMSNM,WASGRPNM,WASTCD,WASTDS,Qtywaste,Unit,QtyKonv,Running,ActiveTime,RunKonv
from ear470_02
Where SESSID = PSess
Order By SBMSNM,WASGRPNM,WASTDS;
Select '' spEAR470 - 15 = '' || timeofday() into waktu;
Raise Notice ''%'',waktu;
/* Delete unused data */
Delete from ear470_01 where sessid = Psess;
Delete from ear470_02 where sessid = Psess;
Select '' End - spEAR470 = '' || timeofday() into waktu;
Raise Notice ''%'',waktu;
return Phasil;
end;
' language 'plpgsql';
On Thu, Apr 3, 2008 at 8:55 AM, paul rivers <[EMAIL PROTECTED]> wrote:
> Otniel Michael wrote:
>
> > Hi All..
> >
> > Anybody knows what is "GO" (SQL Server) on Postgres?
> >
> > Thanks.
> >
> > --
> > ---
> > "He who is quick to become angry will commit folly, and a crafty man is
> > hated"
> >
>
> semi-colon.
>
>
>
--
---
"He who is quick to become angry will commit folly, and a crafty man is
hated"
Re: [SQL] update with join
2008/4/2, Ivan Sergio Borgonovo <[EMAIL PROTECTED]>: > I've > > create table types( > typeid int, > special boolean not null > ); > > create table methods( > methodid int, > typeid references types(typeid), > ); > > create table orders( > orderid int > ); > > create table order_payments( > payid int > orderid references order(orderid), > methodid references method(methodid), > issued boolean not null default false > ); > > orderid payid methodid special > 1 1 1t > 1 2 2t > 1 3 3t > 1 4 4f > 1 5 4f > > I'd like to chose one payid > If the payid is "special" just set issued to true for that payid, > leave the other unchanged. > If the payid is not "special" set issued for all the payid in the > same order. > > eg. > So if payid=4 I'd have > > orderid payid methodid special issued > 1 1 1t t > 1 2 2t t > 1 3 3t t > 1 4 4f t > 1 5 4f t > > and if payid=2 > > orderid payid methodid special issued > 1 1 1t f > 1 2 2t t > 1 3 3t f > 1 4 4f f > 1 5 4f f > > This stuff below doesn't work: > > update order_payments > set issued=true where payid in ( > select p.payid > from order_payments p > join methods as m on m.methodid=p.methodid > join types as t on m.typeid=t.typeid > where (p.orderid=%d and not t.special) or p.payid=%d); > > and I can understand why but I can't rewrite it to make it work. > Try: UPDATE order_payments SET issued=true FROM methods m, types t WHERE m.methodid=p.methodid AND m.typeid=t.typeid AND ((order_payments.orderid=%d AND NOT t.special) OR order_payments.payid=%d)); Osvaldo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Asking GO on SQL SERVER
> Hem... I think postgresql do not like that. You may need to add a "COMMIT;" in there somewhere...? THINK BEFORE YOU PRINT - Save paper if you don't really need to print this ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments e-mail. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
