I have this scenario - multiple tables in same db but tables MAY have different schema's.
with fields A,B,C
with fields B,C,D
can I write a select query that works for both tables? Something like:
if exists field C (select C where date between X and Y)
elseif exists field D (select D where date between X and Y)
end as "E"
You could write a stored procedure that generates dynamic SQL using case. The SP could contain lines like:
when 'Table_A' then
set ColName = 'C';
when 'Table_B' then
set ColName = 'D';
set ColName = '?';
SET sqlstr = 'select ' || ColName || ' from ' || TableName || ' where date between X and Y';
EXECUTE IMMEDIATE sqlstr;