Re: Display of text fields

* 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;

--------------------
# and finally:

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 :-)))))
# Then I re-read #9.12.1 CASE of pg 7.4 Documentation with a slight
# different syntax that I'm going to try ...
-----------------------------
As I told Richard in a previous message, I was sure the solution ought to
be there: it's a question of being patient and having time to
'experiment' ;-)
Thanks you all for the assistance.
Best regards,
Ennio.

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why to use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (used to say Henry Miller) ]