Re: VIEW on lookup table

> Yes. If you create a table with all of the values, 1 to 100,000+, and then
> join that with lookup_data, using a "left outer join", and then use a
> case statement for the value -- when NULL, 'Unknown', then it should
> work.

This would still require constructing a large table, which is what I
want to avoid.

> I would look at bending the requirements a bit before I do this. Why do
> you want the string "Unknown" and not NULL? What is this table going to
> be used for? Also, just because you can't write a function in the
> database to do this doesn't mean you can't write a function in perl or
> python outside of the database to do it.

The technology used to access the database does not cope very well
with NULL/missing rows/colunns :/

As it turns out, the lookup table has been ditched.

> Also, seriously consider upgrading to 7.4.1. 7.2 is ancient and really
> shouldn't be used anymore.

Mutch as I would like to, this is not an option.

Thanks for your help,
JJ

On Fri, Mar 05, 2004 at 08:39:12AM -0800, Jonathan M. Gardner wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Friday 27 February 2004 2:18 pm, JJ Gabor wrote:
> > Hello all,
> >
> > I have a lookup table consisting of 100,000+ rows.
> >
> > 99% of the lookup values resolve to 'Unknown'.
> >
> > Building the lookup table takes a long time.
> >
> > I would like to remove the 'Unknown' entries from the
> > table and provide a VIEW to emulate them.
> >
> > The VIEW would need to provide all 100,000+ rows by
> > using the reduced lookup data and generating the
> > remaining values on the fly.
> >
> > The lookup table structure:
> >
> > CREATE TABLE lookup_data (
> >
> > id1 INTEGER,
> > id2 INTEGER,
> > name TEXT,
> >
> > PRIMARY KEY (id1, id2)
> > );
> >
> > id1 is an INTEGER; from 0 through to 50,000+
> > id2 is an INTEGER; either 9 or 16.
> >
> > Example data:
> >
> > INSERT INTO lookup_data (id1, id2, name) VALUES (1, 9, 'a');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (1, 16, 'b');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (2, 9, 'c');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (2, 16, 'd');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (3, 9, 'e');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (3, 16, 'f');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (4, 9, 'g');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (4, 16, 'h');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (8, 9, 'i');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (8, 16, 'j');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (10, 9, 'k');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (10, 16, 'l');
> > ..
> >
> > In the example data, entries where id1 is 5,6,7,9 are 'Unknown';
> >
> > The VIEW would return:
> >
> > id1, id2, name
> > 1, 9, 'a'
> > 1, 16, 'b'
> > 2, 9, 'c'
> > 2, 16, 'd'
> > 3, 9, 'e'
> > 3, 16, 'f'
> > 4, 9, 'g'
> > 4, 16, 'h'
> > 5, 9, 'Unknown'
> > 5, 16, 'Unknown'
> > 6, 9, 'Unknown'
> > 6, 16, 'Unknown'
> > 7, 9, 'Unknown'
> > 7, 16, 'Unknown'
> > 8, 9, 'i'
> > 8, 16, 'j'
> > 9, 9, 'Unknown'
> > 9, 16, 'Unknown'
> > 10, 9, 'k'
> > 10, 16, 'l'
> >
> > I am using Postgres 7.2.1, which prevents me using a
> > function to return a result set.
> >
> > Can I achieve this in pure SQL?
>
> Yes. If you create a table with all of the values, 1 to 100,000+, and then
> join that with lookup_data, using a "left outer join", and then use a
> case statement for the value -- when NULL, 'Unknown', then it should
> work.
>
> I would look at bending the requirements a bit before I do this. Why do
> you want the string "Unknown" and not NULL? What is this table going to
> be used for? Also, just because you can't write a function in the
> database to do this doesn't mean you can't write a function in perl or
> python outside of the database to do it.
>
> Also, seriously consider upgrading to 7.4.1. 7.2 is ancient and really
> shouldn't be used anymore.
>
> - --
> Jonathan Gardner
> jgardner(at)jonathangardner(dot)net
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.3 (GNU/Linux)
>
> iD8DBQFASK0wqp6r/MVGlwwRAub2AKCUcqvFvkD1KjXLEeg8osybgw5kqwCgiq8W
> YiJY3ZYsAXNfjjBTCF0vGKE=
> =5EIl
> -----END PGP SIGNATURE-----