Re: [SQL] Query regarding to MS reporting services (Grand total problem)
On Tue, Sep 05, 2006 at 10:30:19PM -0700, Dinesh Tiwari wrote: > If any one have idea about this please help me. My idea is that you ask on a list that supports MS SQL Server. This isn't one. A -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Substitute a Character
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
Re: [SQL] Substitute a Character
Judith <[EMAIL PROTECTED]> schrieb:
> 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???
Perhaps something like this:
test=# select regexp_replace('A98526', '^.', '0');
regexp_replace
098526
(1 row)
http://www.postgresql.org/docs/8.1/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."(unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Substitute a Character
> 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!!!
You should look at these functions:
http://www.postgresql.org/docs/8.1/static/functions-string.html
This for example might work for you:
select translate('A98526', 'A', '0');
Bye, Chris.
--
Chris Mair
http://www.1006.org
---(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
Re: [SQL] Substitute a Character
On 9/6/06 12:53 PM, "Judith" <[EMAIL PROTECTED]> 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??? select translate( 'A98526', 'A', '0' ); translate 098526 1 record(s) selected [Fetch MetaData: 1/ms] [Fetch Data: 0/ms] [Executed: 9/6/06 4:18:44 PM EDT ] [Execution: 99/ms] > > Thanks in advanced!!! > > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend -- Daryl Email *my = [ daryl at: @"eddl" dot: @"us" ]; Weblog *blog = @²http://itsallsemantics.com²; ---(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] Evaluation of if conditions
Hi, How does the IF statement evaluate conditions? Does it evaluate conditions following their declaration order from left to right? In case of or-conditions, does the IF statement stop evaluating conditions whenever a first or-condition is true? The following snippet seems to be invalid, which let me think that PL/PGSQL evaluates all the conditions: IF (TG_OP = 'INSERT') OR (OLD.bar = ...) THEN statement END IF; Should be rewritten as (for example): IF (TG_OP = 'INSERT') THEN statement ELSIF (OLD.bar = ...) THEN statement END IF; Regards, -- Daniel ---(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
Re: [SQL] Evaluation of if conditions
Daniel CAUNE wrote: Hi, How does the IF statement evaluate conditions? Does it evaluate conditions following their declaration order from left to right? In case of or-conditions, does the IF statement stop evaluating conditions whenever a first or-condition is true? The following snippet seems to be invalid, which let me think that PL/PGSQL evaluates all the conditions: IF (TG_OP = 'INSERT') OR (OLD.bar = ...) THEN statement END IF; Should be rewritten as (for example): IF (TG_OP = 'INSERT') THEN statement ELSIF (OLD.bar = ...) THEN statement END IF; Regards, -- Daniel ---(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 The following is working fine for me on 8.1.x on Windows. I am not sure what order it is evaluating the if statement in but it is working correctly. Oisin CREATE OR REPLACE FUNCTION zfunc_testor(bool, bool) RETURNS "varchar" AS $BODY$DECLARE v_1 boolean; v_2 boolean; BEGIN v_1 :=$1; v_2 := $2; if (v_1 = TRUE) OR (v_2 = TRUE) then return 'At least 1 true'; else return 'neither true'; end if; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION zfunc_testor(bool, bool) OWNER TO postgres; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Evaluation of if conditions
Daniel, AFAIK there is no short-circuiting of evaluations in postgres and I don't think you can depend on the order they appear in to determine the order in which they are checked, although more knowledgable people may have better info than I ... so the rewritten form is the way to go. Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTECTED] on behalf of Daniel CAUNE Sent: Wed 9/6/2006 3:32 PM To: pgsql-sql@postgresql.org Cc: Subject:[SQL] Evaluation of if conditions Hi, How does the IF statement evaluate conditions? Does it evaluate conditions following their declaration order from left to right? In case of or-conditions, does the IF statement stop evaluating conditions whenever a first or-condition is true? The following snippet seems to be invalid, which let me think that PL/PGSQL evaluates all the conditions: IF (TG_OP = 'INSERT') OR (OLD.bar = ...) THEN statement END IF; Should be rewritten as (for example): IF (TG_OP = 'INSERT') THEN statement ELSIF (OLD.bar = ...) THEN statement END IF; Regards, -- Daniel ---(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 --- Click link below if it is SPAM [EMAIL PROTECTED] "https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=44ff5167171391789821027&[EMAIL PROTECTED]&retrain=spam&template=history&history_page=1" !DSPAM:44ff5167171391789821027! --- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Evaluation of if conditions
Daniel CAUNE wrote: Hi, How does the IF statement evaluate conditions? Does it evaluate conditions following their declaration order from left to right? In case of or-conditions, does the IF statement stop evaluating conditions whenever a first or-condition is true? Please see 4.2.12. Expression Evaluation Rules of the manual: The order of evaluation of subexpressions is not defined. In particular, the inputs of an operator or function are not necessarily evaluated left-to-right or in any other fixed order. There's more examples there too. Joe ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Evaluation of if conditions
Daniel CAUNE <[EMAIL PROTECTED]> writes: > How does the IF statement evaluate conditions? Does it evaluate conditions > following their declaration order from left to right? In case of > or-conditions, does the IF statement stop evaluating conditions whenever a > first or-condition is true? > The following snippet seems to be invalid, which let me think that PL/PGSQL > evaluates all the conditions: > IF (TG_OP = 'INSERT') OR > (OLD.bar = ...) THEN It's not that all the conditions get evaluated by an OR, it's that plpgsql needs to send all the parameter values that the IF-expression needs down to the core SQL engine. So it fails on "OLD.bar" not being defined, long before the expression evaluator gets to think about whether TG_OP = 'INSERT' or not. So, yeah, you want to rewrite it as two separate IF-tests. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
