Thread: Overlapping Ranges- Query Alternative

Overlapping Ranges- Query Alternative

From

"Ozer, Pam"

Date:

11 November 2010, 18:07:40

<div class="WordSection1"><p class="MsoNormal">I have the following problem:<p class="MsoNormal"> <p
class="MsoNormal">Createtemp table ranges (Groups int, ColumnA int);<p class="MsoNormal">Insert into ranges<p
class="MsoNormal">Values(2,45);<pclass="MsoNormal"> <p class="MsoNormal">Select Groups, <p class="MsoNormal">Case when
ColumnAbetween 0 and 19 then 0<p class="MsoNormal">     when ColumnA >=20 then 20<p class="MsoNormal">     when
ColumnA>=30 then 30<p class="MsoNormal">     when ColumnA>=40 then 40<p class="MsoNormal">     when
ColumnA>=50then 50 end MinRange<p class="MsoNormal">from ranges<p class="MsoNormal"> <p class="MsoNormal">Results:
<pclass="MsoNormal">Groups minrange<p class="MsoNormal">2;20<p class="MsoNormal"> <p class="MsoNormal">What I want Is :
Onecolumn can fall into multiple ranges.  For example 45 >20, 30, and 40 so I want the following results<p
class="MsoNormal">2;20<pclass="MsoNormal">2;30<p class="MsoNormal">2;40<p class="MsoNormal"> <p class="MsoNormal">I
knowI could do a union with each range but is there any way to bring back all ranges in one query?  I need to bring
backthe values in one column so having separate columns for each range is not an option.<p class="MsoNormal"> <p
class="MsoNormal">Thankyou in advance for any help<p class="MsoNormal"> <div id="content"><p class="MsoNormal"><b><span
style="font-size:11.5pt;font-family:"Arial","sans-serif";
color:black">Pam Ozer</span></b></div><p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Times New
Roman","serif""><br/><br /></span></div>

Re: Overlapping Ranges- Query Alternative

From

Andreas Gaab

Date:

12 November 2010, 07:31:56

Hi,

the following works:

Create temp table ranges (Groups int, ColumnA int);

Insert into ranges Values(2,45);

Insert into ranges Values(3,15);

Insert into ranges Values(4,25);

Insert into ranges Values(5,35);

Select Groups,

Case when ColumnA between 0 and 19 then 0

when ColumnA >=20 AND ColumnA < 30 then generate_series(20,20,10)

when ColumnA >=30 AND ColumnA < 40 then generate_series(20,30,10)

when ColumnA>=40 AND ColumnA < 50 then generate_series(20,40,10)

when ColumnA>=50 then generate_series(20,50,10) end MinRange

from ranges;

--or even only

Select Groups,

CASE WHEN ColumnA < 20 then 0 ELSE

generate_series(20, (floor(ColumnA / 10.0) * 10)::integer ,10) END MinRange

from ranges;

Best, Andreas

Von: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] Im Auftrag von Ozer, Pam
Gesendet: Donnerstag, 11. November 2010 20:07
An: pgsql-sql@postgresql.org
Betreff: [SQL] Overlapping Ranges- Query Alternative

I have the following problem:

Create temp table ranges (Groups int, ColumnA int);

Insert into ranges

Values(2,45);

Select Groups,

Case when ColumnA between 0 and 19 then 0

when ColumnA >=20 then 20

when ColumnA >=30 then 30

when ColumnA>=40 then 40

when ColumnA>=50 then 50 end MinRange

from ranges

Results:

Groups minrange

2;20

What I want Is : One column can fall into multiple ranges. For example 45 >20, 30, and 40 so I want the following results

2;20

2;30

2;40

I know I could do a union with each range but is there any way to bring back all ranges in one query? I need to bring back the values in one column so having separate columns for each range is not an option.

Thank you in advance for any help

Pam Ozer

Re: Overlapping Ranges- Query Alternative

From

"Marc Mamin"

Date:

12 November 2010, 07:58:08

or:

Select Groups, generate_series

FROM

ranges JOIN generate_series(10,50,10) on ( ColumnA < generate_series)

ORDER by Groups , generate_series

;

regards,

Marc Mamin

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andreas Gaab
Sent: Freitag, 12. November 2010 09:23
To: 'Ozer, Pam'; pgsql-sql@postgresql.org
Subject: Re: [SQL] Overlapping Ranges- Query Alternative

Hi,

the following works:

Create temp table ranges (Groups int, ColumnA int);

Insert into ranges Values(2,45);

Insert into ranges Values(3,15);

Insert into ranges Values(4,25);

Insert into ranges Values(5,35);

Select Groups,

Case when ColumnA between 0 and 19 then 0

when ColumnA >=20 AND ColumnA < 30 then generate_series(20,20,10)

when ColumnA >=30 AND ColumnA < 40 then generate_series(20,30,10)

when ColumnA>=40 AND ColumnA < 50 then generate_series(20,40,10)

when ColumnA>=50 then generate_series(20,50,10) end MinRange

from ranges;

--or even only

Select Groups,

CASE WHEN ColumnA < 20 then 0 ELSE

generate_series(20, (floor(ColumnA / 10.0) * 10)::integer ,10) END MinRange

from ranges;

Best, Andreas

I have the following problem:

Create temp table ranges (Groups int, ColumnA int);

Insert into ranges

Values(2,45);

Select Groups,

Case when ColumnA between 0 and 19 then 0

when ColumnA >=20 then 20

when ColumnA >=30 then 30

when ColumnA>=40 then 40

when ColumnA>=50 then 50 end MinRange

from ranges

Results:

Groups minrange

2;20

What I want Is : One column can fall into multiple ranges. For example 45 >20, 30, and 40 so I want the following results

2;20

2;30

2;40

Thank you in advance for any help

Pam Ozer

Re: Overlapping Ranges- Query Alternative

From

"Ozer, Pam"

Date:

12 November 2010, 15:44:24

Thank you all for your suggestions. I will try each of these and see which one fits my situation best.

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andreas Gaab
Sent: Friday, November 12, 2010 12:23 AM
To: Ozer, Pam; pgsql-sql@postgresql.org
Subject: Re: [SQL] Overlapping Ranges- Query Alternative

Hi,

the following works:

Create temp table ranges (Groups int, ColumnA int);

Insert into ranges Values(2,45);

Insert into ranges Values(3,15);

Insert into ranges Values(4,25);

Insert into ranges Values(5,35);

Select Groups,

Case when ColumnA between 0 and 19 then 0

when ColumnA >=20 AND ColumnA < 30 then generate_series(20,20,10)

when ColumnA >=30 AND ColumnA < 40 then generate_series(20,30,10)

when ColumnA>=40 AND ColumnA < 50 then generate_series(20,40,10)

when ColumnA>=50 then generate_series(20,50,10) end MinRange

from ranges;

--or even only

Select Groups,

CASE WHEN ColumnA < 20 then 0 ELSE

generate_series(20, (floor(ColumnA / 10.0) * 10)::integer ,10) END MinRange

from ranges;

Best, Andreas

I have the following problem:

Create temp table ranges (Groups int, ColumnA int);

Insert into ranges

Values(2,45);

Select Groups,

Case when ColumnA between 0 and 19 then 0

when ColumnA >=20 then 20

when ColumnA >=30 then 30

when ColumnA>=40 then 40

when ColumnA>=50 then 50 end MinRange

from ranges

Results:

Groups minrange

2;20

What I want Is : One column can fall into multiple ranges. For example 45 >20, 30, and 40 so I want the following results

2;20

2;30

2;40

Thank you in advance for any help

Pam Ozer

force view column varchar(32) to varchar(128)

From

Emi Lu

Date:

19 November 2010, 14:17:38

Hello,

Is there a way to force the view column change from varhcar(32) to 
varchar(128)?

Example:
===================
v1 (id varchar(32) ... )

There are more than 1000 other views depend on v1.

Instead of recreating all other 1000 views, is there a way postgresql 
8.3 can do/accept:

create or replace v1 AS

select id::varchar(128), ......

Thanks a lot!
--
Lu Ying

Re: force view column varchar(32) to varchar(128)

From

Andreas Kretschmer

Date:

19 November 2010, 15:55:08

Emi Lu <emilu@encs.concordia.ca> wrote:

> Hello,
>
> Is there a way to force the view column change from varhcar(32) to  
> varchar(128)?

No, you have to recreate the view ...


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."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: force view column varchar(32) to varchar(128)

From

Richard Broersma

Date:

19 November 2010, 16:34:21

On Fri, Nov 19, 2010 at 7:02 AM, Emi Lu <emilu@encs.concordia.ca> wrote:

> Is there a way to force the view column change from varhcar(32) to
> varchar(128)?

> Instead of recreating all other 1000 views, is there a way postgresql 8.3
> can do/accept:

Ouch!  That stinks. I feel your pain (well my pain came from just
changing about 15-20 views.  changing 1000 views would make me
cringe).  Its too bad you haven't or can't use a dimensionless varchar
for your id column (I assume this is your primary key).  This would
eliminate the need to re-dimension your varchar columns.

As I see it, you don't have an option.  PgAdmin can be a great help
here.  Open the create script and make the changes and save both.

The only other Option that I can see would be to dump the table and
view defintions. Modify these definitions with sed.  Apply the new
schema, and lastly, use an ETL software to push your data to the new
schema.

-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug