Disclaimer: I have never built a Teradata system, so I can't claim this from first-hand experience, but I will explain the reasoning.
I think that Teradata will be able to produce this view efficiently. From what you say, it appears to do little more than join some very small dimension tables against a fact table. The join operations will be relatively efficient. Unless I misunderstand your requirements these columns are allowing your application to select various rollups of data from a multi-grain fact table.
Even though Teradata is a shared-nothing system, I can't see any requirement for the view to push large semi-joins across nodes or anything like that.
Beyond that, all I can suggest is that you suck it and see. If you don't have anywhere to experiment you could download the express version of Teradata off their web site and see if you can prototype this structure to see what the query plan actually is.
Teradata uses Standard SQL join syntax like all other DBMSes.
First of all you should stop using the old syntax FROM tabl1, table, table3, switch to explicit JOIN instead.
I don't know about your data, so I'm not sure if you can simply combine both queries into one, but if it's possible it should be like this:
SELECT
a01.fieldOne Q_One,
a01.fieldTwo Q_Two,
a02.fieldThree Q_Three,
a03.fieldFour Q_Four
FROM db.table_one AS a01
JOIN db.table_two AS a02
ON a01.fieldKey = a02.fieldKey
LEFT JOIN db.table_three AS a03 -- your guess is correct :)
ON a01.fieldKey = a03.fieldKey
and a02.fieldKey = a03.fieldKey;
Check if the number of rows returned matches your 1st query.
Edit:
As you actually join a lot of tables you might try to keep the existing implicit joins and only add a LEFT join for the last table (I'm not sure if this returns the correct result):
db.table23 a125,
db.table24 a126
LEFT JOIN a127
ON ....
And next time start using ON-syntax from the beginning :)
Best Answer
For a SHOW TABLE the parser actually creates the CREATE TABLE based on the current definition.
But for SHOW VIEW, etc. it's retrieving the CREATE from
dbc.TablesV.RequestText. If the source code is larger than 12.5 KBRequestTxtOverFlowis set and the remainder is found in one or more 32 KB chunks indbc.TextTbl.For SHOW PROCEDURE it's different, this returns the internally stored sorce code (unless the SP is created with the
NOSPLoption), there's no way to access this info using a Select.