Re: Display of text fields - Mailing list pgsql-general

* Stephan Szabo <sszabo@megazone.bigpanda.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) ]