Archives of the TeradataForum

Message Posted: Mon, 24 Feb 2004 @ 00:12:22 GMT

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

Are you running V2R4? I remember having problems like what you describe in V2R4. I had to break up my case statement in V2R4 but not in V2R5. I searched around and found an example:

sel 'revoke ' ||
    (case when a.accessright < 'E' then
      (case a.accessright
           when 'AS' then 'ABORTSESSION '
           when 'CD' then 'CREATE DATABASE '
           when 'CG' then 'CREATE TRIGGER '
           when 'CM' then 'CREATE MACRO '
           when 'CP' then 'CHECKPOINT '
           when 'CT' then 'CREATE TABLE '
           when 'CU' then 'CREATE USER '
           when 'CV' then 'CREATE VIEW '
           when 'D'  then 'DELETE '
           when 'DD' then 'DROP DATABASE '
           when 'DG' then 'DROP TRIGGER '
           when 'DM' then 'DROP MACRO '
           when 'DP' then 'DUMP '
           when 'DT' then 'DROP TABLE '
           when 'DU' then 'DROP USER '
           when 'DV' then 'DROP VIEW '
           else '*** '
       end)
     else
      (case a.accessright
           when 'E'  then 'EXECUTE '
           when 'I'  then 'INSERT '
           when 'MR' then 'MONRESOURCE '
           when 'MS' then 'MONSESSION '
           when 'IX' then 'INDEX '
           when 'R'  then 'SELECT '
           when 'RF' then 'REFERENCES '
           when 'RS' then 'RESTORE '
           when 'SR' then 'SETRESRATE '
           when 'SS' then 'SETSESSRATE '
           when 'U'  then 'UPDATE '
           else '*** '
      end)
    end) ||
    'on ' ||
    trim(dd.databasename) ||
    (case t.tvmname
         when 'All' then ' '
         else '.' || trim(t.tvmname) || ' '
    end) ||
    'from ' ||
    trim(du.databasename) ||
    ';' (title'')
from dbc.accessrights a
    ,dbc.dbase        du
    ,dbc.dbase        dd
    ,dbc.tvm          t
where a.userid = du.databaseid
 and  a.databaseid = dd.databaseid
 and  a.tvmid = t.tvmid
 and  du.rowtype = 'D'
 and  a.allnessflag = 'N'
 and  a.withgrant = 'N'
 and  a.accessright NOT IN ('PC','PD','PE','RO')
order by 1;

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