Archives of the TeradataForum

Message Posted: Mon, 13 Jan 2003 @ 21:47:31 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Cave Canum (Beware of Dog)!

Now, the only problem with this approach is if you have restrictions in two views, once of which is on the outside of an outer join. Because there are WHERE clause restrictions (in this case, for a certain class or user) on the outside table, the optimizer will turn your very well designed outer join into an inner join. Poof, you loose the capability you wanted to include. The solution to this is to either build an outer join view (it works, but blech! it's not very future oriented) or use the view on the outside of the outer join in a subquery and do an outer join to that subquery. Using the quickest example I have on hand, my little compare columns macro, which does a FULL outer join:

DATABASE z010506;

CREATE MACRO CompareColumns(TableInQuestion CHAR(30),
                            DatabaseName1   CHAR(30),
                            DatabaseName2   CHAR(30))
          AS (
SELECT :TableInQuestion, :DatabaseName1, :DatabaseName2,
       CASE
         WHEN T1.DatabaseName IS NULL
           THEN TRIM(T2.ColumnName) || ' missing from ' ||
                TRIM(T2.TableName) || ' in ' || :DatabaseName1
         WHEN T2.DatabaseName IS NULL
           THEN TRIM(T1.ColumnName) || ' missing from ' ||
                TRIM(T1.TableName) || ' in ' || :DatabaseName2
         WHEN T1.ColumnType <> T2.ColumnType     OR
              T1.ColumnLength <> T2.ColumnLength
           THEN TRIM(T1.ColumnName) || ' [' ||
                (CASE T1.ColumnType
                      WHEN 'CF' THEN 'CHAR(' || TRIM(T1.ColumnLength) || ')'
                      WHEN 'CV' THEN 'VARCHAR(' || TRIM(T1.ColumnLength) || ')'
                      WHEN 'D'  THEN 'DECIMAL(' || TRIM(T1.DecimalTotalDigits) ||
                                     ',' || TRIM(T1.DecimalFractionalDigits) || ')'
                      WHEN 'I'  THEN 'INTEGER'
                      WHEN 'I1' THEN 'BYTEINT'
                      WHEN 'I2' THEN 'SMALLINT'
                      WHEN 'DA' THEN 'DATE'
                      ELSE T1.ColumnType
                 END) || '] <> ' ||
                TRIM(T2.ColumnName) || ' [' ||
                (CASE T2.ColumnType
                      WHEN 'CF' THEN 'CHAR(' || TRIM(T2.ColumnLength) || ')'
                      WHEN 'CV' THEN 'VARCHAR(' || TRIM(T2.ColumnLength) || ')'
                      WHEN 'D'  THEN 'DECIMAL(' || TRIM(T2.DecimalTotalDigits) ||
                                     ',' || TRIM(T2.DecimalFractionalDigits) || ')'
                      WHEN 'I'  THEN 'INTEGER'
                      WHEN 'I1' THEN 'BYTEINT'
                      WHEN 'I2' THEN 'SMALLINT'
                      WHEN 'DA' THEN 'DATE'
                      ELSE T2.ColumnType
                 END) || ']'
         WHEN T1.ColumnFormat <> T2.ColumnFormat
           THEN TRIM(T1.ColumnName) || ' (' || TRIM(T1.ColumnFormat) || ') Format <> ' ||
                TRIM(T2.ColumnName) || ' (' || TRIM(T2.ColumnFormat) || ')'
         WHEN T1.Nullable <> T2.Nullable
           THEN TRIM(T1.ColumnName) || ' (' || TRIM(T1.Nullable) || ') Nullable <> ' ||
                TRIM(T2.ColumnName) || ' (' || TRIM(T2.Nullable) || ')'
         WHEN T1.Compressible <> T2.Compressible
           THEN TRIM(T1.ColumnName) || ' (' || TRIM(T1.Compressible) || ') Compress <> ' ||
                TRIM(T2.ColumnName) || ' (' || TRIM(T2.Compressible) || ')'
         WHEN T1.CompressValue <> T2.CompressValue
           THEN TRIM(T1.ColumnName) || ' (' || TRIM(T1.CompressValue) || ') Value to Compress <> ' ||
                TRIM(T2.ColumnName) || ' (' || TRIM(T2.CompressValue) || ')'
         WHEN T1.DefaultValue <> T2.DefaultValue
           THEN TRIM(T1.ColumnName) || ' (' || TRIM(T1.DefaultValue) || ') Default Value <> ' ||
                TRIM(T2.ColumnName) || ' (' || TRIM(T2.DefaultValue) || ')'
         WHEN T1.ColumnTitle <> T2.ColumnTitle
           THEN TRIM(T1.ColumnName) || ' (' || TRIM(T1.ColumnTitle) || ') Title <> ' ||
                TRIM(T2.ColumnName) || ' (' || TRIM(T2.ColumnTitle) || ')'
         ELSE   'Match on ' || TRIM(T1.ColumnName)
       END
  FROM (SELECT *
          FROM DBC.Columns
         WHERE TableName = :TableInQuestion AND
               DatabaseName = :DatabaseName1) AS T1
  FULL OUTER JOIN (SELECT *
                     FROM DBC.Columns
                    WHERE TableName = :TableInQuestion AND
                          DatabaseName = :DatabaseName2) AS T2
    ON T1.TableName = T2.TableName AND
       T1.ColumnName = T2.ColumnName;
);

In my first attempt, I tried to put the where clause on one side or the other, and always turned my full outer join into a left or right outer join (depending on whether I was feeling left or right handed that day). The same could happen if you had two views, both of which used some sort of horizontal (row) access restrictions and you wanted an outer join of some sort between them. Anyway, enjoy!

iv

	<Prev	Next>		<<First	<Prev	Next>	Last>>