Re: IN vs EXIIST

Quoting Jean-Christian Imbeault <jc@mega-bucks.co.jp>:
>
> Jochem van Dieten wrote:
>  >
>  > SELECT     key1, Min(CASE WHEN x THEN 1 ELSE 0 END) AS isTrue
>  > FROM       table
>  > GROUP BY   key1
>  > HAVING     isTrue = 1
>
> I tried this but got an error:
> psql TMP -c "select invoice_id, min(case when received then 1 else 0
> end) as ok from invoice_li group by invoice_id having ok = 1"
> ERROR:  Attribute 'ok' not found

Sorry, not behind the console at the moment. Try either:

SELECT     invoice_id
FROM       (
  SELECT    invoice_id, Min(CASE WHEN received THEN 1 ELSE 0 END) AS ok
  FROM      invoice_li
  GROUP BY  invoice_id
           ) agg_invoices
WHERE      ok = 1

or:

SELECT    invoice_id, Min(CASE WHEN received THEN 1 ELSE 0 END) AS ok
FROM      invoice_li
GROUP BY  invoice_id
HAVING    Min(CASE WHEN received THEN 1 ELSE 0 END) = 1

Make sure you have an index on invoice_id. I am not sure if it is
faster because of the case that needs to be done on each row, but I
think it is worth a shot.

Jochem