[SQL] Problem with FOR UPDATE
Hi All, I have a query in which I want to SELECT FOR UPDATE same rows but only from one table. Firs I try just with SELECT FOR UPDATE but I receive an error because of the LEFT JOIN - "ERROR: SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join". So I decide to use SELECT FOR UPDATE OF but I then receive the error you can see. Can anyone help me with this query? Thanks in advance. Regards, Kaloyan Iliev rsr=# SELECT rsr-#DD.* rsr-# ( SELECT sum(-amount * saldo_sign(credit)) rsr(# FROM acc_debts ACD1 rsr(# WHERE ACD1.debtid = DD.debtid ) AS saldo, rsr-# C.custid, rsr-# S.descr_bg rsr-#FROM debts_desc DD LEFT JOIN config C ON (DD.conf_id = C.id), rsr-# acc_debts AD, rsr-# acc_clients AC, rsr-# services S rsr-#WHERE DD.debtid = AD.debtid rsr-# AND DD.closed AND NOT DD.paid AND DD.has_invoice AND DD.active AND DD.has_proform AND NOT DD.storned rsr-# AND AD.transact_no = AC.transact_no rsr-# AND AC.ino = 45 rsr-#FOR UPDATE OF debts_desc; ERROR: relation "debts_desc" in FOR UPDATE/SHARE clause not found in FROM clause rsr=# select version(); version PostgreSQL 8.1.4 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC) 3.4.4 [FreeBSD] 20050518 (1 row) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Substitute a Character
Hi, Try: UPDATE foe SET field = regexp_replace(field, '^.', '0'); OR UPDATE foe SET field = regexp_replace(field, 'A', '0'); This will replace in table "foe" in the column "field" 'A' with '0'; Regards, Kaloyan Iliev Judith wrote: Hello everybody!! I have a field type text with folios like this: A98526 but I want to change de A for a 0 like this: 098526, exists a way to do this in a query??? Thanks in advanced!!! ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Postgres regexp matching failure?
On Tue, 2006-09-05 at 11:22 -0400, Tom Lane wrote: > Mario Splivalo <[EMAIL PROTECTED]> writes: > > So, I guess it's obvious that postgres doesn't treat regular expressions > > the same way as java/perl/pyton/php/awk/sed do... > > When you get into stuff as arcane as word-boundary constraints, you'll > find that regexes are not NEARLY as well standardized as that flippant > complaint suggests. For penance, actually try it in all six of those > languages and report back. So I did, and I was mistaken the first time. Java, perl, python and php treat regexpes differently than awk/sed and differently than tcl. I can post source snippets and results here if anyone is interested in those. > Postgres' regexp code is the same as Tcl's (it's Henry Spencer's package) > and if you check TFM you will find out that \y, or possibly \M, is what > you want. Thnx, I missed that part that 'postgres has same regexp code as Tcl'. Mike -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(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] Is it possible to left join based on previous joins result
Hello, Is it possible to do something like: select ... from t1 inner join t2 ... left join t2.colN When t1 inner join with t2 I got unique result for t2.colN( colN's value is table name). Can I continue to left join with the column "colN" in table t2? Thanks ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Query regarding to MS reporting services (Grand total problem)
On 9/6/06, Dinesh Tiwari <[EMAIL PROTECTED]> wrote: Hi, I have a problem reagarding Grand Total in the table footer. I am showing only top 10 seeling for each department in report but i want sum of whole table seelings in table footer. not only 10 sellings which are shown in report. I want the sum of all 40 thousand rows. How i can do it. If any one have idea about this please help me. Thanks in advance. In general I would recommend you run two separate queries - one for the report and another for the footer - and then union the two. == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
Re: [SQL] Is it possible to left join based on previous joins result
I tried the example as the following: create table a(col1); create table b(col1, col2) select a.* from a inner join b using(col2) left join b.col2 as c on (c.col1 = a.col1) System notifies me that b is not a schema name. So, I guess the approach that I tried to do is not acceptable by Pgsql grammar. Is it possible to do something like: select ... from t1 inner join t2 ... left join t2.colN When t1 inner join with t2 I got unique result for t2.colN( colN's value is table name). Can I continue to left join with the column "colN" in table t2? Thanks ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Problem with FOR UPDATE
Kaloyan Iliev <[EMAIL PROTECTED]> writes: > rsr=# SELECT > ... > rsr-#FROM debts_desc DD LEFT JOIN config > C ON (DD.conf_id = C.id), > ... > rsr-#FOR UPDATE OF > debts_desc; > ERROR: relation "debts_desc" in FOR UPDATE/SHARE clause not found in > FROM clause Use the alias, ie, DD. Remember that an alias hides the real name of that table for all purposes in the current query. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problem with FOR UPDATE
On Thu, 7 Sep 2006, Kaloyan Iliev wrote: > Hi All, > I have a query in which I want to SELECT FOR UPDATE same rows but only > from one table. > Firs I try just with SELECT FOR UPDATE but I receive an error > because of the LEFT JOIN - "ERROR: SELECT FOR UPDATE/SHARE cannot be > applied to the nullable side of an outer join". > So I decide to use SELECT FOR UPDATE OF but I then receive > the error you can see. I think you'd want to use DD not debts_desc as you've renamed the from list entry. > Can anyone help me with this query? > > Thanks in advance. > > Regards, > Kaloyan Iliev > > rsr=# SELECT > rsr-#DD.* > rsr-# ( SELECT sum(-amount * > saldo_sign(credit)) > rsr(# FROM acc_debts ACD1 > rsr(# WHERE > ACD1.debtid = DD.debtid ) AS saldo, > rsr-# C.custid, > rsr-# S.descr_bg > rsr-#FROM debts_desc DD LEFT JOIN config > C ON (DD.conf_id = C.id), > rsr-# acc_debts AD, > rsr-# acc_clients AC, > rsr-# services S > rsr-#WHERE DD.debtid = AD.debtid > rsr-# AND DD.closed AND NOT > DD.paid AND DD.has_invoice AND DD.active AND DD.has_proform AND NOT > DD.storned > rsr-# AND AD.transact_no = > AC.transact_no > rsr-# > AND AC.ino = 45 > rsr-#FOR UPDATE OF > debts_desc; > ERROR: relation "debts_desc" in FOR UPDATE/SHARE clause not found in > FROM clause ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Is it possible to left join based on previous joins result
On Thu, 7 Sep 2006, Emi Lu wrote: > Hello, > > Is it possible to do something like: > > select ... > from t1 > inner join t2 ... > left join t2.colN > > When t1 inner join with t2 I got unique result for t2.colN( colN's value > is table name). > > Can I continue to left join with the column "colN" in table t2? No, in part because it'd have to actually evaluate the first join in order to even plan the remainder of the query. It might be possible to do something similar, albeit somewhat slowly, inside a set returning function, but you'd have to decide how to handle more than one row being returned from the first join even if the value is unique, is that one join against the table or multiple joins. ---(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
Re: [SQL] Is it possible to left join based on previous joins
On fim, 2006-09-07 at 10:02 -0400, Emi Lu wrote:
> I tried the example as the following:
>
> create table a(col1);
> create table b(col1, col2)
>
> select a.*
> from a inner join b using(col2)
> left join b.col2 as c on (c.col1 = a.col1)
>
> System notifies me that b is not a schema name.
>
> So, I guess the approach that I tried to do is not acceptable by Pgsql
> grammar.
it seems to me that basically you want to
select from a table whose name is stored in another
table.
one way to do that would be to use plpgsql or
some other procedural language to create
a set returning function, so that you
could do:
SELECT * from selfromtab('sometable');
after that is done,you might be able to use that
in joins
gnari
>
>
> >
> > Is it possible to do something like:
> >
> > select ...
> > from t1
> > inner join t2 ...
> > left join t2.colN
> >
> > When t1 inner join with t2 I got unique result for t2.colN( colN's value
> > is table name).
> >
> > Can I continue to left join with the column "colN" in table t2?
> >
> > Thanks
> >
> >
> >
> >
> > ---(end of broadcast)---
> > TIP 4: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
>
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
>
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] Thanks
Thanks a lot! It works! Regards, Kaloyan Iliev Tom Lane wrote: Kaloyan Iliev <[EMAIL PROTECTED]> writes: rsr=# SELECT ... rsr-#FROM debts_desc DD LEFT JOIN config C ON (DD.conf_id = C.id), ... rsr-#FOR UPDATE OF debts_desc; ERROR: relation "debts_desc" in FOR UPDATE/SHARE clause not found in FROM clause Use the alias, ie, DD. Remember that an alias hides the real name of that table for all purposes in the current query. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
