[SQL] About Div
Dear All,I have a problem with this case :I have 10 candy for 7 child (A, B, C, D, E, F, G).Table X :code value --- A 0 B 0 C 0 D 0 E 0 F 0 G 0 And I want divide it with this algorithm : A = 10 / 7 = 1 B = (10-1) / (7-1) = 9 / 6 = 1 C = (10-2) / (7-2) = 8 / 5 = 1 D = (10-3) / (7-3) = 7 / 4 = 1 E = (10-4) / (7-4) = 6 / 3 = 2 F = (10-6) / (7-5) = 4 / 2 = 2 G = (10-8) / (7-6) = 2 / 2 = 2In my first solution i use loop - for each record in my function.But it is too slow in a lot of data.Did postgresql have a function for my case?Thanks All.-- "He who is quick to become angry will commit folly, and a crafty man is hated" Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail Beta.
Re: [SQL] About Div
Mr. Aaron. I am sorry, your solution didn't match in my case. Example for your solution :A = 1B = 1C = 1D = 1E = 1F = 1G = 4G have 4 candy. Its too much for G.In my case, the solution is :A = 1B = 1C = 1D = 1E = 2F = 2G = 2The extra candy is given to three child.Do you have the other solution? I need function in postgresql for my case.Because my loop is too slow.Btw thanks for your solution.Aaron Bono <[EMAIL PROTECTED]> wrote: On 7/25/06, Otniel Michael <[EMAIL PROTECTED]> wrote: Dear All,I have a problem with this case :I have 10 candy for 7 child (A, B, C, D, E, F, G).Table X :code value --- A 0 B 0 C 0 D 0 E 0 F 0 G 0 And I want divide it with this algorithm : A = 10 / 7 = 1 B = (10-1) / (7-1) = 9 / 6 = 1 C = (10-2) / (7-2) = 8 / 5 = 1 D = (10-3) / (7-3) = 7 / 4 = 1 E = (10-4) / (7-4) = 6 / 3 = 2 F = (10-6) / (7-5) = 4 / 2 = 2 G = (10-8) / (7-6) = 2 / 1 = 2In my first solution i use loop - for each record in my function. But it is too slow in a lot of data.Did postgresql have a function for my case? No loop necessary. This is a simple math problem:dividedamount := candy / childcount;extra = candy % childcount; So the first (childcount - extra) get (dividedamount) pieces of candy and the last (extra) get (dividedamount + 1) pieces of candy.== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com== -- "He who is quick to become angry will commit folly, and a crafty man is hated" __Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[SQL] Ask To Optimize Looping
Hi guys, can anyone help to optimize this loop : for i in select kodedivisi,kodeseksi,kodewip,nobatch,ket1,ket2,ket3,ket4,NILAIPROP as nilaiygdibagi from EDP040_07_23 order by kodedivisi,kodeseksi,kodewip,nobatch,ket1,ket2,ket3,ket4 loop jmlData2 = jmlData2 + 1; if (jmlData2 = 500) then jmlData3 = jmlData3 + jmlData2; jmlData2 = 0; Raise Notice '' 12 - 2 - EDP040_07_23 = %'', jmlData3; end if; Update EP_ES06_N_TEMP2 Set NilPakai = NilPakai + case when i.nilaiygdibagi > 0 then VNilaiPembagi else -1*VNilaiPembagi end Where (THBLTR,DIVCOD,WIPCOD,NBATCH,SKSFOH,MAINAC,SUB_AC,BAGCOD,SLGIDC,DSRTRF, JAMPAKAI,NILPAKAI,KodeHsl,NoBatchHsl) in (Select THBLTR,DIVCOD,WIPCOD,NBATCH,SKSFOH,MAINAC,SUB_AC,BAGCOD,SLGIDC,DSRTRF, JAMPAKAI,NILPAKAI,KodeHsl,NoBatchHsl From EP_ES06_N_TEMP2 Where DIVCOD = i.kodedivisi and WIPCOD = i.kodewip and NBATCH = i.nobatch and SKSFOH = i.kodeseksi and trim(MAINAC) = trim(i.ket1) and trim(SUB_AC) = trim(i.ket2) and trim(BAGCOD) = trim(i.ket3) and trim(SLGIDC) = trim(i.ket4) Order by thbltr desc,divcod desc,wipcod desc,nbatch desc, SKSFOH desc,mainac desc,sub_ac desc,bagcod desc,slgidc desc,dsrtrf desc, jampakai desc,nilpakai desc,kodehsl desc,nobatchhsl desc limit (abs(i.nilaiygdibagi) / VNilaiPembagi)); end loop; This loop need 30 minutes (18.000 record data), and the raise notice will show every 50 second (500 record data). Thanks anyway. -- --- "He who is quick to become angry will commit folly, and a crafty man is hated"
[SQL] Ask About SQL
Hi All. Can help to create sql queries for this data : tabel A field1, field2, field3 x1, y1, 5 x1, y2, 1 x2, y1, 2 x2, y3, 4 x1, y3, 4 I want to get 2 record with the max value at field3 for each kombination of field1 : tabel B field1, field2, field3 x1, y1, 5 x1, y3, 4 x2, y3, 4 x2, y1, 2 Anyone have an ideas? Thanks anyway. -- --- "He who is quick to become angry will commit folly, and a crafty man is hated"
[SQL] Error while vacuumdb
Hi guys, I already vacuumdb postgresql, and i found error : ERROR : failed to re-find parent key in "pg_attribute_relid_attnam_index" What this error about? Anybody know what should i do? Thanks before. -- --- "He who is quick to become angry will commit folly, and a crafty man is hated"
[SQL] ERROR: failed to re-find parent key in "pk_ep07"
When i was vacuum the database, the vacuum if failed. And I get this error. Any ideas an to fix this? ERROR: failed to re-find parent key in "pk_ep07" Thanks before. Note : EP07 is name of tables. -- "He who is quick to become angry will commit folly, and a crafty man is hated" __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [SQL] ERROR: failed to re-find parent key in "pk_ep07"
Note: forwarded message attached. -- "He who is quick to become angry will commit folly, and a crafty man is hated" __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com --- Begin Message --- OC. I will try to upgrade the postgres. What version that can fix this problem? Postgres 8.2.5? Postgres 8.1? Now, i use postgres 8.0.3, what effect when i upgrade the postgres version? I heared that i should check my sql? In postgres 8.0.3 show as warning, in postgres 8.1 as error? Thanks. Tom Lane <[EMAIL PROTECTED]> wrote: Otniel Michael writes: > When i was vacuum the database, the vacuum if failed. And I get this error. > Any ideas an to fix this? >ERROR: failed to re-find parent key in "pk_ep07" Update to a newer PG version, possibly? This symptom has been seen before... regards, tom lane -- "He who is quick to become angry will commit folly, and a crafty man is hated" __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com --- End Message --- ---(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] Run Function With First Priority
Dear All, How i can run function with first priority on postgres? In my case, when i run function that need a lot of time. Then the other person run the other function, i think my function run on the below priority of the other function. Thanks. -- "He who is quick to become angry will commit folly, and a crafty man is hated" Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
[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
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"
