Re: Display of text fields

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

As an explanation of the duplicate rows:

FROM bib_lt t0, bibbt t1 with no WHERE condition that constrains the join
is going to give alot of rows with basically every combination (1st row of
t0 with 1st row of t1, 1st row of t0 with 2nd row of t1, etc...). Some of
these rows will have t0.n_prog=t1.n_prog but most will not. You then
project the select list for each of those rows. The ones with 'T' are
going to get (assuming no duplicates in t0.n_prog or t1.n_prog) one row
with the note as the final field, and a bunch more with 'n/a' as it.
When you DISTINCT those, it sees that the note and 'n/a' are distinct
(well, usually) and outputs both.

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.