From: | "Vilson farias" <vilson(dot)farias(at)digitro(dot)com(dot)br> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | UNION syntax different for 7.1.2 |
Date: | 2001-07-19 17:11:50 |
Message-ID: | 000f01c11076$2d056c40$98a0a8c0@dti.digitro.com.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
from 7.0.2 to 7.0.3, the following script works fine, but in 7.1.2 I must remove the rr. from ORDER BY clause. This happens only when I have a UNION between the selects, if I try the order by rr.dtsolicitacao in both selects everything goes ok. I know you guys can suggest me to change this whole script to a single outer join, but I cant because my app still access some old 7.0.3 databases spread over all my country. The question is : Is it some kind of bug or I need to change all my scripts that uses UNION ?
Best regards,
José Vilson de Mello de Farias
Dígitro Tecnologia Ltda - Brazil
SELECT
rr.*, rm.identificacao, rr.tipo_arquivo,
ru.cod_operador, ru.cod_operador as nome,
ru.flg_responsavel, ri.flg_estado as flg_estimp,
ri.msg_erro as msg_erroimp
FROM
rel__relatorio rr, rel__modelo rm,
rel__usuarios ru, rel__impressao ri
WHERE
rr.cod_cad_relatorio = 1 AND
rr.cod_modelo = rm.cod_modelo AND
rr.cod_cad_relatorio = ru.cod_cad_relatorio AND
rr.cod_relatorio = ru.cod_relatorio AND
ru.cod_cad_relatorio = ri.cod_cad_relatorio AND
ru.cod_relatorio = ri.cod_relatorio AND
ru.cod_operador = ri.cod_operador AND
rr.cod_terminal = 'dgtac' AND
ri.dtimpressao IS NULL AND ru.cod_operador = 'SYSADM'
UNION ALL
SELECT
rr.*, rm.identificacao, rr.tipo_arquivo,
ru.cod_operador, ru.cod_operador AS nome,
ru.flg_responsavel, NULL, NULL
FROM
rel__relatorio rr, rel__modelo rm,
rel__usuarios ru
WHERE
rr.cod_cad_relatorio = 1 AND
rr.cod_modelo = rm.cod_modelo AND
rr.cod_cad_relatorio = ru.cod_cad_relatorio AND
rr.cod_relatorio = ru.cod_relatorio AND ru.cod_operador = 'SYSADM' AND
rr.cod_terminal = 'dgtac' AND
NOT EXISTS (SELECT * FROM rel__impressao ri WHERE
ru.cod_cad_relatorio = ri.cod_cad_relatorio AND
ru.cod_relatorio = ri.cod_relatorio AND
ru.cod_operador = ri.cod_operador) AND ru.cod_operador = 'SYSADM'
ORDER BY rr.dtsolicitacao
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-07-19 17:13:02 | Re: VACUUM ANALYZE |
Previous Message | Kenneth Been | 2001-07-19 17:04:30 | many tables in db |