Archives of the TeradataForum

Message Posted: Tue, 17 Sep 2002 @ 21:00:13 GMT

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

Hey, the color coding has been in 'gate for a while. I downloaded 6.3.3 recently from Calvert's site, and it's there under Tools>>Options>>Edit Controls, and you can set colors for keywords, object names and parens, etc seperately. I use the standard blue for keywords, a nice dark green for tables and columns and a lovely shade of red for |*)', etc. What I use it FOR is when I get code from one of the SQL producing tools (BI/Query, Cognos, whatever) and it has everything strung out, with alias, and looking very messy. If the code sucks the machine dry, I do an explain, but I do it out of 'gate so that I can figure out what's what. I also use my own "prettyprinting" convention to be able to SEE what the heck each section of the SQL is doing. Makes for understanding. And then you can run said explain from the tool itself. The editors are nice (and probably better than Calvert's) but then, 'gate isn't just an editor. You can do multi- statement requests from THIS TYPE OF TOOL (see, I'm not talking about a particular product) (oh yeah, try that in Queryman). So want to see the difference? Use the following macro (you might find it useful), stick it in any colorizing editor-type-thingy and look at the code. Cool, eh? Pretty good for us humans!

CREATE MACRO CompareTbl(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."Title" <> T2."Title"
           THEN TRIM(T1.ColumnName) || ' (' || TRIM(T1."Title") || ') Title <> ' ||
                TRIM(T2.ColumnName) || ' (' || TRIM(T2."Title") || ')'
*/
         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;
);

Oh yeah, as you can see, I'm having some trouble with this little macro, getting SQL to let me use the Title column in DBC.Columns. Seems it conflicts with the reserved word. Anybody figured a way around that?

iv

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