Dynamic Select query possible?

Database
New Member

Dynamic Select query possible?

 

I have this scenario - multiple tables in same db but tables MAY have different schema's.

TABLE A

with fields A,B,C

TABLE B

with fields B,C,D

 

can I write a select query that works for both tables? Something like:

 

case 

if exists field C (select C where date between X and Y)

elseif exists field D (select D where date between X and Y)

else 'unknown'

end as "E"

1 REPLY
Teradata Employee

Re: Dynamic Select query possible?

You could write a stored procedure that generates dynamic SQL using case.  The SP could contain lines like:

 

case TableName
    when 'Table_A' then
        set ColName = 'C';
    when 'Table_B' then
        set ColName = 'D';
    else
        set ColName = '?';
end case;
SET sqlstr = 'select ' || ColName || ' from ' || TableName || ' where date between X and Y';
EXECUTE IMMEDIATE sqlstr;