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

* Richard Huxton <dev@archonet.com> [090904, 14:20]:
> Ennio-Sr wrote:
> > [ ... ]
>
> Ah - looks like I misunderstood what you were trying to do. There is no
> way to have a single query return rows with different numbers of columns
> - each row must be the same.
>
> You'd have to do something like one of the following (substitute
> my_memo_column with whatever your memo field was called).
> [ ... ]

Thanks for your time, Richard. No, may be I was not clear enough ... :-)

I slightly modified your queries and the result gets nearer my goals,
but ...
Here is what I tried:

SELECT  DISTINCT
/* despite the DISTINCT, it shows twice each matching record: once
with the memo fieldd and then without it!. Leaving out the DISTINCT,
each record is shown many times (may be as many as the number of
numbered fields, according to the CASE condition */
        t0.n_prog,
    t0.autore,
    .........,
    .........,
    t0.scheda_ltr,
    CASE
       WHEN t0.scheda_ltr = 'T' AND  t0.n_prog=t1.n_prog THEN
       t1.note
        ELSE 'n/a'
        END AS note
FROM bib_lt t0, bidbt t1 where t0.n_prog<>0 ;
-- i.e. consider already numbered records only as they may have a
-- corresponding 'memo' (alias 'note') in the other table.
[bib_lt has 27 columns, bidbt only has n_rif & note (alias ex-dbf-memo)]
[I'm not sure yet as to whether it is better to keep 'note' in a
separate table or to incorporate it in the main one: much depends on
the possibility to get a reasonable way to read the note 'if and only
when they are there' (i.d. when 'scheda_ltr='T') as not all records have
a filled up 'note' field.]

-----------------
This works allright, apart from doubling the records, as noted above.
[I can get over the problem of superfluous hyphens showing (the '----' of
the memo field) chhosing:
\a  # (unaligned) and
\f '-->:  ' # (as field separator)]
-----------------------
The alternative:

SELECT DISTINCT
    t0.scheda_ltr,
    CASE
       WHEN t0.scheda_ltr = 'T' AND  t0.n_prog=t1.n_prog THEN t0.autore || ' / ' || t0.titolo || ' / ' || editore || '
/' || t0.altre_notizie || ' / ' || t1.note 
        ELSE -- 'n/a'
       t0.autore || ' / ' || t0.titolo || ' / ' || editore || ' / ' || t0.altre_notizie

        END AS note
FROM bib_lt t0, bidbt t1 where t0.autore like '%SERAF%';

formats the records in a different way but does duplicate them as the
other one.

------------

I have no clue as to why records are being shown twice: a new reading of
the PG documentation did not help me much :-(
[BTW, can you suggest any better specific reading (with examples) on this
particular issue?]

Perhaps I'd better explain what I'm trying to do:

I have all my books registered in a *.dbf table and a file .exe
(construed a few years ago with clipper-S87) to access, add, modify
them. When I browse my file (for example, on a Dosemu console), if a
particolar record has the field 'scheda_ltr' set to 'T', I can press F2
and view/modify the relative 'memo' field.
I would like to be able to achieve a similar result with PostgreSQL; I
know I can do that with Pgaccess or like applications, but my goal is
being able to do it from a console!  :-)

As it is only a few weeks since I started my testings seriously on
pgSQL my knowledge of its potentiality is still very limited and I'm
pretty sure there must be a way to that!

I've appreciated your help so far and hope you'll continue to assist me.
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) ]