Re: Display of text fields

On Fri, 10 Sep 2004, Ennio-Sr wrote:

> * Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> [100904, 07:10]:
> > On Fri, 10 Sep 2004, Ennio-Sr wrote:
> >
> > > I slightly modified your queries and the result gets nearer my goals,
> > > but ...
> > > Here is what I tried:
> > > [ ... ]
> >
> > As an explanation of the duplicate rows:
> >
> > FROM bib_lt t0, bibbt t1 with no WHERE condition that constrains the join
> > [ ... ]
> > If you're not using any other fields from t1, I would wonder if something
> > like:
> >
> > SELECT t0.n_prog, ..., t0.scheda_ltr, coalesce(t1.note, 'n/a') as note
> > FROM bib_lt t0 left outer join t1 on (t0.scheda_ltr='T' and
> > t0.n_prog=t1.n_prog) where t0._nprog<>0;
> >
> > would be closer to what you want from the query. The join should give
> > output with either t0 extended by NULLs or t0 joined by t1 dependant on
> > whether t0.scheda_ltr='T' and if it finds a matching row in t1.
> >
> Thank you Stephen, for your contribution: I'll study it in due course
> ... as I'm interested to learn as much as possible ...
> However, in the meantime, I think I found the solution. What helped me
> was the construction of these two testing tables:
>
> Table "foo"
> Column | Type | Modifiers
> --------+-------------------+-----------
> a | integer |
> b | character varying |
> c | character varying |
> has_d | character(1) |
>
> # which I filled with:
>
> a | b | c | has_d
> ---+------+--------+-------
> 1 | one | number | Y
> 2 | two | number | Y
> 3 | tree | name | Y
> 4 | blue | color | N
> 5 | john | person | N
> (5 rows)
>
> # and:
>
> Table "foo_d"
> Column | Type | Modifiers
> --------+---------+-----------
> a | integer |
> d | text |
>
> # bearing my 'would-be' memo field:
>
> a | d
> ---+----------------------------------
> 1 | is the first natural
> 2 | follows 1 in the seq of natural
> 3 | there are various qualities of -
> (3 rows)
>
> # Then I launched an 'nth' variant of my query:
>
> SELECT DISTINCT
> --- t0.a, t0.b, t0.c, t0.has_d, -- t1.d,
> -- t0.has_d, -- ## ok, mostr prima i due 'N' e poi due volte quelli Y
> -- ## se tolgo 't0.has_d', cambia ordine ma sempre 8
> -- ## sono
> CASE
> WHEN t0.has_d = 'Y' AND t0.a=t1.a
> THEN t0.a || ' - ' || t0.b || ' - ' || t0.c || ' - ' || t1.d
> ELSE
> CASE
> WHEN t0.has_d = 'N'
> THEN t0.a || ' / ' || t0.b || ' / ' || t0.c -- || ' / ' || t0.has_d
> END
> END AS "The result is: "
> FROM foo t0, foo_d t1;

Note however, that this may very well perform poorly compared to other
solutions because as foo and foo_d get large, you're going to be
evaluating the case clause alot. In addition, this gives an extra NULL
row AFAICS (see below where you get a "blank" row and the rowcount is 1
higher than the meaningful number of rows.

> The result is:
> ----------------------------------------------------
> 1 - one - number - is the first natural
> 2 - two - number - follows 1 in the seq of natural
> 3 - tree - name - there are various qualities of -
> 4 / blue / color
> 5 / john / person
>
> (6 rows)
>
> # which is exaclty what I was looking for :-)))))