Thread: monster query, how to make it smaller
Hello everybody I need some help on a monster query. Please see the attached file for the query itself. The only difference is Z_durch_soll and the offset, which is currently 0.25. The query will run in a loop where I increment this offset until I find enough records. Thanks in advance ... jr (See attached file: monsterQuery.txt) ============================================ PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland phone: +4141 790 4040 fax: +4141 790 2545 mobile: +4179 211 0315 ============================================
Attachment
----- Original Message -----
From: <juerg.rietmann@pup.ch>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, January 23, 2001 2:42 PM
Subject: [SQL] monster query, how to make it smaller
> Hello everybody
>
> I need some help on a monster query. Please see the attached file for the
> query itself. The only difference is Z_durch_soll and the offset, which is
> currently 0.25. The query will run in a loop where I increment this offset
> until I find enough records.
I'm not entirely clear on what you are trying to do, but perhaps something
like
... AND Z_durch_soll in ('286.35', '286.30', '286.25')
instead of all the UNIONs? This is the same as
... AND (Z_durch_sol1='286.35' OR Z_durch_sol1='286.30' ...)
HTH
- Richard Huxton
Can't you skip the UNIONing and do this with something like
where (Z_A_nr is NULL
AND Z_umfang = '900' AND Z_blaenge = '2340' AND
Z_durch_soll IN ('286.1', '286.15', '286.20', ...)
AND Z_status = 'zcu'
AND (((Z_durch_soll+0.25)-Z_durch_ist) / 2) >= 0.085
AND (((Z_durch_soll+0.25)-Z_durch_ist) / 2) > 0.12)
?
I'm not sure whether the difference in the last output expression
(Z_durch_soll+0.25 in the first UNION'd select, Z_durch_soll+0.2
in the rest) is intentional or a typo. If it's intentional, you
could write it as something like
CASE WHEN Z_durch_soll = '286.45' THEN Z_durch_soll+0.25
ELSE Z_durch_soll+0.2 END
regards, tom lane