How can I substitute a literal for a specific column value? 1

[RCPD700]

I'm new to Oracle and SQL Plus etc. having migrated from Datacom/DB, so I'm basically teaching myself.

I wrote a query and debugged it in SQL Plus to assist our Help Desk determine what terminal a user is logged on at. The result was put into a read-only APEX (application express) app by our database guys for security reasons and it turned out pretty nice (snippet link attached).

Our DB guys are way too busy to bug with my learning questions, so here's the controlling info and my question with the existing query...

Info
When a user is not logged on, the date and time (VARCHAR2 columns - 12 and 9 respectively) both contain zeros. If their session was terminated without signing off, or they otherwise became disconnected, we could see both terminal and date info even though they are not actually logged on; therefore, date and time are the controlling factor for the condition test.

How can I...
When a user is logged on, display the date and time they logged on; otherwise, display "Logged Off" instead, so it's obvious to Help Desk staff that the user is not logged on. I've tried a few different things, but keep getting various, mabiguous, syntactical errors.

Original query...
prompt
accept user_id
prompt 'Enter user ID - e.g., zzabc1: '
prompt
SELECT
a.EMPLOYEE_TS_ID AS UserID,
TRIM(a.NAME_LAST)||', '||TRIM(a.NAME_FIRST)||' '||SUBSTR(a.NAME_MIDDLE,1,1) AS User_Name,
b.TERM_ID_SIGN_ON AS Term_ID,
b.DATE_SIGN_ON_MO||'/'||b.DATE_SIGN_ON_DY||'/'||b.DATE_SIGN_ON_CN||b.DATE_SIGN_ON_YR||'-'||b.TIME_SIGN_ON_HR||':'||TIME_SIGN_ON_MN AS Signed_On
FROM PCJIS.EMPLOYEE_S_LV a, PCJIS.EMPLOYEE_USER_S_LV b
WHERE a.EMPLOYEE_TS_ID like upper ('%&user_id%') AND b.SSN = a.SSN
ORDER BY Signed_On DESC, UserID ASC, Terminal_ID ASC ;

Date/Time descending is the primary sort here because so logged on users appear at the top of the list.

Any suggestions would be greatly appreciated. There also has to be a better way of formatting the entire date and time columns DATE_SIGN_ON and TIME_SIGN_ON) than what I did above.


Thanks in advance

 

[SantaMufasa]

RCPD700,

Welcome to the Oracle 10g forum. (You've been a member of Tek-Tips for 10 years, but this is your first time in the Oracle 10g forum...I hope we can be helpful to you.)

I have a couple of questions to help me give you the most helpful response:[ul][li]If the tables you are using (PCJIS.EMPLOYEE_S_LV and PCJIS.EMPLOYEE_USER_S_LV) are Oracle tables, then why are the date columns not of data type DATE? (In Oracle, there is never a good reason to store a date value in a data type that is not DATE.)[/li][li]What would we see in your tables if a user is not logged on?[/li][li]Can you you please post an excerpt of the results you are getting with this query presently (including a row or two of users that are not logged on)?[/li][/ul]Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[RCPD700]

I see you noticed an issue... that has been one of my pet peeves for the 15 years I've been working on this system.

It was originally designed to run on an IBM mainframe under CICS using CA's Datacom/DB database - which of course is not really relational, though it tries to be.

In 2009 we finished a conversion and migration of the system to IBM pSeries servers running under AIX. The application system runs under Clerity Solutions' Unikix with an Oracle 10gR2 back end.

As part of the migration from Datacom to Oracle, we had to deal with converting several Datacom datatypes that are not supported in Oracle. Because of the way dates were [mis]used in Datacom, it made it necessary to define them as varchar in Oracle.

I had numerous heated discussions about the poor choice of initial data typing in a number of areas, but at that point (1996) the horse was long gone from the barn.

Oops... looks like my screenshot of output didn't get attached as I had thought. You can find it here:

http://www.mediafire.com/i/?2lk29d12k1fm3a9

When a user logs on, their terminal ID along with the date and time of sign-on are saved in a table to prevent multiple sign-ons by an individual. There are additional internal security reasons which I won't bore you with. When the user successfully signs off, the terminal ID and logon date/time are cleared.

You'll notice in the screenshot that there are several users having no terminal id and the date/time are all zeroes. These people are not signed on. Currently signed on users will have a terminal ID and the current date with the time they signed on displayed. If we see a user entry with a terminal ID, but the date is a prior date, it indicates they shutdown their session without logging off, or their system crashed, or some other type of disconnect occurred and they have not signed on since.

Hopefully this answers your questions.

 

[Turkbear]

Hi,
Santa would a decode or CASE statement provide what RCPD700 needs? Maybe a view that uses a CASE statement to handle the Date/time conversion?



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Yes, Turkbear, either a DECODE or a CASE statement would work fine (with a variety of condition-checking options):

SELECT a.EMPLOYEE_TS_ID AS UserID
      ,TRIM(a.NAME_LAST)||', '||TRIM(a.NAME_FIRST)||' '||SUBSTR(a.NAME_MIDDLE,1,1) AS User_Name
      ,b.TERM_ID_SIGN_ON AS Term_ID
      ,CASE WHEN TO_NUMBER(b.DATE_SIGN_ON_MO||
                           b.DATE_SIGN_ON_DY||
                           b.DATE_SIGN_ON_CN||
                           b.DATE_SIGN_ON_YR) = 0
            THEN 'Logged off'
       ELSE
            b.DATE_SIGN_ON_MO||'/'||
            b.DATE_SIGN_ON_DY||'/'||
            b.DATE_SIGN_ON_CN||b.DATE_SIGN_ON_YR||'-'||
            b.TIME_SIGN_ON_HR||':'||TIME_SIGN_ON_MN
       END AS Signed_On
  FROM EMPLOYEE_S_LV a, EMPLOYEE_USER_S_LV b
 WHERE b.SSN = a.SSN
 ORDER BY Signed_On DESC, UserID ASC, Term_ID ASC ;

USERID USER_NAME            TERM_ID SIGNED_ON
------ -------------------- ------- --------------
DAMAS1 SINGLETON, MIFF              Logged off
MCCSJ1 JOSE, CESAR F                Logged off
SHJXB1 BYRNES, JAMES                Logged off
SHKL01 OWEN, KENNETH L              Logged off
SHSCK1 KIRKPATRICK, SCOTT C         Logged off
DASFG1 GALLAGHER, SEAN F    NM22    10/24/2011-9:7
MCABC2 COUNTER, TWO         UD03    10/24/2011-8:1

7 rows selected.

RCPD, I reformatted your code (for looks only), but there was a minor expression-labelling ambiguity in your original code ("TERM_ID" versus "TERMINAL_ID"), which I resolved by making the TERMINAL_ID appear as TERM_ID. Also, since it appeared that you wanted 7 rows of output, I removed the "ACCEPT...PROMPT" SQL*Plus code.

Let us know if this resolves your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[RCPD700]

Santa,

That looks just like what I'm after. No surprise about the "ambiguity" given all the "normal" stuff I'm trying to accomplish while working a pretty nasty problem. But I digress...

I put the prompt in my query for our Help Desk folks, so they could enter a single user's initials, or their entire user id. This way they can display either a single line, or all matches on the user's initials , since a number of users have multiple sign-ons. No matter, a little tweak and that's good. When my query got turned into the APEX app, it changed behavior a bit, so that when the app opens, all users are listed and the Help Desk person can use a box to enter a specific user id to locate and those results generally return a single record.

It looks like I was barking up the right tree. I was thinking that a CASE might be just what I needed to do, but the example I found was confusing. Trying to work 3 or 4 things at once just added to the confusion.

As soon as I get a chance, I'll run your code against my data and see what comes back.

Thank you very much for your assistance. I truly appreciate it!
You must have one heck of a star collection

 

[SkipVought]

In Oracle, there is never a good reason to store a date value in a data type that is not DATE.

I agree. Yet in the aircraft manufacturing company I work for, there are "date" fields that are CHAR of the form yyyymmdd, where it seems that decades ago, they designed the tables to mirror IMS Inquiry screen transactions and store date data as text. Fortunately, joining to a ControlDate table on a DATE8 field, solves the conversion issue.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SantaMufasa]

Interesting, Skip.

Here is an equally interesting trivia question for you (and others) that want to "race" for the answer:

How many bytes do each of the following two data occupy in Oracle:[ul][li]a "date" field that is CHAR of the form "yyyymmdd".[/li][li]an Oracle DATE field that contains:[/li][ul][li]a B.C./A.D. indicator[/li][li]a two-digit century[/li][li]a two-digit year[/li][li]a two-digit month[/li][li]a two-digit day[/li][li]a two-digit hour[/li][li]a two-digit minute[/li][li]a two-digit second[/li][/ul][/ul]???


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[RCPD700]

Not knowing a blasted thing about Oracle, except for its location (down the road), I'll take a WAG and say 8 bytes. I suspect they have a trick or two up their sleeve for handling the BC/AD and more. It probably also may have something to do with whether its unicode or not. Like I say, purely an uneducated guess - basically because I really need a short break.

 

[Dagon]

SQL> select vsize(sysdate), vsize('20111102') from dual

VSIZE(SYSDATE) VSIZE('20111102')
-------------- -----------------
             7                 8

 

[SantaMufasa]

Ding, ding, ding, ding, ding...Dagon wins the race.

We see that even when storing all of the Date/Time components, above, Oracle takes less space than a character storage of just Date.

So, even with the legacy-system-compatibility example that Skip offered, above, I cannot identify a justification for using anything besides a DATE expression in Oracle to deal with Date/Time information.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.