 |
 |
Archives of the TeradataForum
Message Posted: Thu, 27 Apr 2006 @ 10:00:35 GMT
| Subj: | | Re: Extracting word from LOGONSOURCE |
| |
| From: | | Srikanth Kappagantula |
Hi,
Pls. try this and update me if this can help u.
SELECT trim (trailing ' ' from logonoff.username) (NAMED username)
,logonoff.logdate (NAMED LogDt) ,cast(cast(logonoff.logtime as format
'99:99:99') as char(8)) (NAMED LogTime) ,trim (SUBSTR( trim (SUBSTR(
trim (SUBSTR( trim (SUBSTR(logonoff.logonsource,position (' ' in
logonoff.logonsource),characters(logonoff.logonsource))),position (' '
in trim (SUBSTR(logonoff.logonsource,position (' ' in
logonoff.logonsource),characters(logonoff.logonsource)))),characters(
trim (SUBSTR(logonoff.logonsource,position (' ' in
logonoff.logonsource),characters(logonoff.logonsource)))))),0,characters
( trim (SUBSTR( trim (SUBSTR(logonoff.logonsource,position (' ' in
logonoff.logonsource),characters(logonoff.logonsource))),position (' '
in trim (SUBSTR(logonoff.logonsource,position (' ' in
logonoff.logonsource),characters(logonoff.logonsource)))),characters(
trim (SUBSTR(logonoff.logonsource,position (' ' in
logonoff.logonsource),characters(logonoff.logonsource))))))))),0,positio
n (' ' in trim (SUBSTR( trim (SUBSTR( trim
(SUBSTR(logonoff.logonsource,position (' ' in
logonoff.logonsource),characters(logonoff.logonsource))),position (' '
in trim (SUBSTR(logonoff.logonsource,position (' ' in
logonoff.logonsource),characters(logonoff.logonsource)))),characters(
trim (SUBSTR(logonoff.logonsource,position (' ' in
logonoff.logonsource),characters(logonoff.logonsource)))))),0,characters
( trim (SUBSTR( trim (SUBSTR(logonoff.logonsource,position (' ' in
logonoff.logonsource),characters(logonoff.logonsource))),position (' '
in trim (SUBSTR(logonoff.logonsource,position (' ' in
logonoff.logonsource),characters(logonoff.logonsource)))),characters(
trim (SUBSTR(logonoff.logonsource,position (' ' in
logonoff.logonsource),characters(logonoff.logonsource)))))))))) ))
(NAMED Terminal) ,trim (trailing ' ' from logonoff.event)(NAMED Event)
,date FROM DBC.LOGONOFF,DBC.USERS WHERE EVENT NOT IN
('Logon','Logoff','Logon Failed','Forced Off','') AND LOGONDATE=DATE-1
AND DBC.LOGONOFF.USERNAME=DBC.USERS.USERNAME;
);
Thanks and regards,
Srikanth K.
| |
