|
Glenn and Chuck, The folks here are pretty handy with SQL (which I am not) and they do a lot of Unions to solve a similar problem. The difference is they do it in ONE sql statement. It is kinda ugly to the non-SQL'ers but works like a champ. Here is an example where they join 4 flavors of a data file where the date fields may have zero's and they force in a value at run time. Hope this helps. -- Both g4csdt and g4usd2 w/valid dates select g4csdt, g4usd2, g5doco from QTEMP/f555056e, f5201 where g5doco = g4doco and g4csdt <> 0 and g4usd2 <> 0 union all -- g4csdt w/valid date and g4usd2 w/out valid date select g4csdt, 10001, g5doco from QTEMP/f555056e, f5201 where g5doco = g4doco and g4csdt <> 0 and g4usd2 = 0 union all -- g4csdt w/out valid date and g4usd2 w/valid date select 10001, g4usd2, g5doco from QTEMP/f555056e, f5201 where g5doco = g4doco and g4csdt = 0 and g4usd2 <> 0 union all -- both g4csdt and g4usd2 w/out valid dates select 10001, 10001, g5doco from QTEMP/f555056e, f5201 where g5doco = g4doco and g4csdt = 0 and g4usd2 = 0 order by 1 >>> Glenn Gundermann <ggundermann@rachisholm.com> 05/19 8:41 AM >>> Chuck, I like Alex Moore's solution which I never thought of. What I used to do was split the query into two queries; one selecting records where the denominator = 0 and calculate the result field = whatever and the 2nd query selecting records where the denominator <> 0 and calculate the result field as you have already specified. Glenn Gundermann +--- | This is the Midrange System Mailing List! | To submit a new message, send your mail to MIDRANGE-L@midrange.com. | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com. | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com. | Questions should be directed to the list owner/operator: david@midrange.com +---
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.