Archives of the TeradataForum

Message Posted: Fri, 06 Feb 2009 @ 14:47:57 GMT

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

<-- Anonymously Posted: Fri, 6 Feb 2009 14:42 -->

Hi all,

thanks to all for the input - i havent got it to run without it bringing back the row - details below.

what i am actually trying to achieve is to set up a job that runs a simple select against the access log to ascertin what statement texts a particular user ran for the previous month but i want to illiminate statements i expect to see in the output (the statements i am having difficulty with contain '' or ?) so the query looks like this

     select
     LogDate
     ,LogTime
     ,LogonDate
     ,LogonTime
     ,AccessType
     ,DatabaseName
     ,StatementType
     ,StatementText
     from dbc.accesslog
     where StatementText not in (
     ' SELECT InfoData FROM dbc.dbcinfo where InfoKey =''release'' ')
     and logdate > date - 30
     and username = 'dbc' order by 1,2

Thanks to all for your input

results from previous attempts:-

     1)
     SELECT InfoData FROM dbc.dbcinfo where InfoKey ='release'
     this didnt work - i still got this statement in the output

     2)
     SELECT InfoData FROM dbc.dbcinfo where InfoKey Like'release%'' escape '%'
     initally a mismatch in quotes so tried
     SELECT InfoData FROM dbc.dbcinfo where InfoKey Like'release%' 'escape' '%'
       but no joy still got the row
     then tried

     3)
     'SELECT InfoData FROM dbc.dbcinfo where InfoKey =''''||'release' ||'''''
     got a 3706 expected something between the string and the key word

     4)
            'SELECT InfoData FROM dbc.dbcinfo where InfoKey =' || 'release' ||
     ''

     5) where StatementText not in (
            'SELECT InfoData FROM dbc.dbcinfo where InfoKey =' || 'release' ||
     ''

     6) ' SELECT InfoData FROM dbc.dbcinfo where InfoKey =''release'' ')

still got the row from statements 4,5&6

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