agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Lew <noone@lewscanon.com>
To: pgsql-sql@postgresql.org
Subject: Re: Query question
Date: Fri, 27 Jan 2012 11:04:25 -0800
Message-ID: <jfusfr$uu8$1@news.albasani.net> (raw)
In-Reply-To: <4F214065.8080201@htechcorp.net>
References: <4F214065.8080201@htechcorp.net>

On 01/26/2012 04:00 AM, John Tuliao wrote:
> I seem to have a problem with a specific query:
>
> The inside query seems to work on it's own:
>
> select prefix
> from john_prefix
> where strpos(jpt_test.number,john_prefix.prefix) = '1'
> order by char_length(john_prefix.prefix) desc limit 1
>
> but when I execute it with this:
>
> UPDATE
>     jpt_test
> set
>     number = substring(number from length(john_prefix.prefix)+1)
> from
>     john_prefix
> where
>     prefix in (
>         select prefix
>         from john_prefix
>         where strpos(jpt_test.number,john_prefix.prefix) = '1'
>         order by char_length(john_prefix.prefix) desc limit 1
>     ) ;
>
> table contents are as follows
>
> john_prefix table:
>
> prefix
> ---------
> 123
> 234
>
> jpt_test table:
>
> number
> -----------
> 1237999999
> 0234999999 <<< supposed to have no match
> 2349999999
>
> Am I missing something here? Any help will be appreciated.

I'm going to guess that it's because you didn't use a separate alias for the 
FROM in the correlated subquery.

Doesn't STRPOS() return INTEGER, not TEXT?

-- 
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg

view thread (15+ messages)  latest in thread

Message-ID: <jfusfr$uu8$1@news.albasani.net>
Permalink:  ../jfusfr$uu8$1@news.albasani.net/
Also on:    postgresql.org/message-id/jfusfr$uu8$1@news.albasani.net

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: noone@lewscanon.com
  Subject: Re: Query question
  In-Reply-To: <jfusfr$uu8$1@news.albasani.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox