an OR-Query with CR8 via ODBC to an Informix-DB 1

[OliverKulbach]

Hi everybody,
I have a sql-query which always kills CR. Doe's anybody know if it is possible to submit OR within the query. It works without OR but doesn't get the correct result. Please find the query below.
Thanks for any idea
Oliver
SELECT
adr_t.name_1, adr_t.ort, adr_t.login_verantw,
s800.send_nr_jj, s800.send_nr_mm, s800.send_nr_kr, s800.send_nr, s800.erf_dat, s800.abs_adr_1, s800.emp_1, s800.tot_wht, s800.tot_charg_wht,
s702.abt_kz,
spktoko_t.saldo_dm
FROM
lfsdb:cargo.adr_t adr_t,
lfsdb:cargo.s800 s800,
lfsdb:cargo.s702 s702,
lfsdb:cargo.spktoko_t spktoko_t
WHERE
adr_t.mandant=s800.mandant AND
(adr_t.adr_art=s800.stat_abs_adr_art AND
adr_t.adr_nr=s800.stat_abs_kds_nr) OR
(adr_t.adr_art=s800.stat_emp_adr_art AND
adr_t.adr_nr=s800.stat_emp_kds_nr) AND
s800.mandant=s702.mandant AND
s800.send_nr_kr=s702.nr AND
s800.mandant=spktoko_t.mandant AND
s800.send_nr_jj=spktoko_t.send_nr_jj AND
s800.send_nr_mm=spktoko_t.send_nr_mm AND
s800.send_nr_kr=spktoko_t.send_nr_kr AND
s800.send_nr=spktoko_t.send_nr AND
adr_t.login_verantw <> ' '
ORDER BY
s800.send_nr_jj ASC,
adr_t.login_verantw ASC,
adr_t.name_1 ASC,
s702.abt_kz ASC,
s800.send_nr_mm ASC

 

[FowlerConsulting]

You have your )'s in the wrond place

Try this where clause:

WHERE
(adr_t.mandant=s800.mandant
AND adr_t.adr_art=s800.stat_abs_adr_art
AND adr_t.adr_nr=s800.stat_abs_kds_nr)
OR
(adr_t.adr_art=s800.stat_emp_adr_art
AND adr_t.adr_nr=s800.stat_emp_kds_nr
AND s800.mandant=s702.mandant
AND s800.send_nr_kr=s702.nr
AND s800.mandant=spktoko_t.mandant
AND s800.send_nr_jj=spktoko_t.send_nr_jj
AND s800.send_nr_mm=spktoko_t.send_nr_mm
AND s800.send_nr_kr=spktoko_t.send_nr_kr
AND s800.send_nr=spktoko_t.send_nr
AND adr_t.login_verantw <> ' ')



Steven Fowler
info@fowlerconsulting.com

http://www.fowlerconsulting.com

 

[FowlerConsulting]

OR you could have ment this:
WHERE
(adr_t.mandant=s800.mandant
AND adr_t.adr_art=s800.stat_abs_adr_art
AND adr_t.adr_nr=s800.stat_abs_kds_nr)
OR
((adr_t.adr_art=s800.stat_emp_adr_art
AND adr_t.adr_nr=s800.stat_emp_kds_nr)
AND s800.mandant=s702.mandant
AND s800.send_nr_kr=s702.nr
AND s800.mandant=spktoko_t.mandant
AND s800.send_nr_jj=spktoko_t.send_nr_jj
AND s800.send_nr_mm=spktoko_t.send_nr_mm
AND s800.send_nr_kr=spktoko_t.send_nr_kr
AND s800.send_nr=spktoko_t.send_nr
AND adr_t.login_verantw <> ' '))

Steven Fowler
info@fowlerconsulting.com

http://www.fowlerconsulting.com

 

[FowlerConsulting]

But is this THIS is what you need!!!

WHERE
((adr_t.adr_art=s800.stat_abs_adr_art
AND adr_t.adr_nr=s800.stat_abs_kds_nr)
AND adr_t.mandant=s800.mandant)
OR
((adr_t.adr_art=s800.stat_emp_adr_art
AND adr_t.adr_nr=s800.stat_emp_kds_nr)
AND s800.mandant=s702.mandant
AND s800.send_nr_kr=s702.nr
AND s800.mandant=spktoko_t.mandant
AND s800.send_nr_jj=spktoko_t.send_nr_jj
AND s800.send_nr_mm=spktoko_t.send_nr_mm
AND s800.send_nr_kr=spktoko_t.send_nr_kr
AND s800.send_nr=spktoko_t.send_nr
AND adr_t.login_verantw <> ' ')

PS the last one has one two many ) on the end. There should be one ) not two ))

Steven Fowler
info@fowlerconsulting.com

http://www.fowlerconsulting.com

 

[OliverKulbach]

Thanks a lot for your help, but either way doesn't work. Is there a chance that CR can't work with OR?
Rgds
Oliver

 

[OliverKulbach]

Now it works, with

WHERE
( (adr_t.adr_nr=s800.stat_abs_kds_nr AND adr_t.adr_art=s800.stat_abs_adr_art) OR (adr_t.adr_nr=s800.stat_emp_kds_nr AND adr_t.adr_art=s800.stat_emp_adr_art)) AND adr_t.mandant=s800.mandant AND s800.mandant=s702.mandant AND s800.send_nr_kr=s702.nr AND s800.mandant=spktoko_t.mandant AND s800.send_nr_jj=spktoko_t.send_nr_jj AND s800.send_nr_mm=spktoko_t.send_nr_mm AND s800.send_nr_kr=spktoko_t.send_nr_kr AND s800.send_nr=spktoko_t.send_nr AND adr_t.login_verantw <> ' '

your comments helped a lot.
Thanks
Oliver