formula to combine two fields to one (not concatenate)

[koolskins]

Running CR10 against an informix database. What is the formula that would take two different fields from two different tables like tablea.col1 and tableb.col1 and treat them as one field. I have 3 character and 1 date field that I need to combine in order to run a simple graph. Thanks.

Randy

 

[lbass]

You would need to use "Add Command" as your datasource or manually edit the "show SQL query", using a union statement, as in:

SELECT
table1."col1", table1."col2", table1."col3", table1."date1"
FROM
table1
WHERE
table1."col1" >= 100
UNION ALL
SELECT
table2."col1", table2."col2", table2."col3", table2."date1"
FROM
table2
WHERE
table2."col1" >= 100
ORDER BY
2

If you only want distinct records, use "UNION" instead of "UNION ALL". The fields from each table must be in the same order and have matching datatypes. If you want to order fields, use an Order by statement at the end that references the sequential position of the field in the Select part of the statement. In the above, the report would be ordered by col2.

-LB

 

[koolskins]

Thanks lbass, great advice as usual. Works like a charm.

Randy