Blankety blank, blankety blank...

To make a cell blank when zero in Excel you could do so via conditional formatting, setting the font colour to be the same as the background colour when it equals zero.

This technique is a bit limited however, and hits problems if you need to use other conditional formatting or when the background colour varies from cell to cell.

Another more flexible method is to use the following formatting string for your cell.
Just go to Format > Cells > Custom and plonk it in to the Type box.

0;-0;;@

The above format doesn't show a thousands delimiter. To get that in use: -

#,##0;-0;;@

Why Microsoft made this expression so bloody complex I'll never know. But hey, Microsoft are Microsoft, they'd make the complete works of Shakespeare out of a crisis.

Have a play with varients of this for other effects.

Enjoy!

Peering into the depths of the PeopleSoft Process Scheduler Queue

On occaision you need to be able to see what's been running in the PeopleSoft Batch queue at a given moment in time. At such times the following SQL comes in quite handy.

-- --------------------------
-- Dump Process Monitor Table
-- --------------------------
select PRCS.OPRID,
       PRCS.PRCSJOBNAME,
       PRCS.PRCSNAME,
       PRCS.RUNCNTLID,
       XLAT.XLATLONGNAME,
       to_char(PRCS.BEGINDTTM,'yyyy-mm-dd-hh24.mi.ss') as Began,
       to_char(PRCS.ENDDTTM,'yyyy-mm-dd-hh24.mi.ss')   as Ended
       (((TO_NUMBER(TO_CHAR(PRCS.ENDDTTM, 'J')) - TO_NUMBER(TO_CHAR(PRCS.BEGINDTTM, 'J'))) * 86400)+(TO_NUMBER(TO_CHAR(PRCS.ENDDTTM, 'SSSSS')) - TO_NUMBER(TO_CHAR(PRCS.BEGINDTTM, 'SSSSS')))) as Dur_Sec
  from PSPRCSRQST   PRCS,
       XLATTABLE_VW XLAT
 where PRCS.BEGINDTTM   > to_date('2009-03-02-01.00.00','YYYY-MM-DD-HH24.MI.SS')
   and PRCS.RUNSTATUS    = XLAT.FIELDVALUE
   and XLAT.FIELDNAME    = 'RUNSTATUS'
   and XLAT.EFFDT = (select max(XLAT1.EFFDT)
                       from XLATTABLE_VW XLAT1                    
                      where XLAT1.FIELDNAME  = XLAT.FIELDNAME
                        and XLAT1.FIELDVALUE = XLAT.FIELDVALUE
                        and XLAT1.EFFDT <= SYSDATE)
   and XLAT.EFF_STATUS = 'A'
 order by PRCS.BEGINDTTM Desc

Be careful when you stare into the void however, the void might stare back at you.

One Step Beyond

One of Time Admin's subprograms, TL_TA000800, calls TL_TA_RULES to apply rules in the order specified on the rule program for each batch. The sections in TL_TA_RULES correspond to each of the active time and labor rules.

When a rule is built the application engine definition tables underlying TL_TA_RULES are updated to reflect any changes made to the rule.

Unfortunately, for a particular rule, the section name and step of the app engine don't correspond to the rule name and step number of the rule itself. Which can make it a little tricky when debugging an abend. The following SQL solves this problem by giving you the rule ID and step for the app. engine section and step that the abend log will give you.

select *
  from ( select TL_RULE_ID,
                TL_RULE_STEP,
                rownum as AE_STEP,
                SQL_ID,
                DESCR100   
           from PS_TL_RULE_STEPS
          where TL_RULE_ID = (select TL_RULE_ID
                                from PS_TL_RULE_DEFN
                               where AE_SECTION = :AE_SECTION)
       )
 where AE_STEP = :STEP

Fun with Time and Labor Dates

There's a useful little table in PeopleSoft Time and Labor called PS_TL_DATES_TBL. This holds a variety of date information, such as day number, julian date , day of week and month, that can save a bit of coding to derive these values yourself. The following example uses this table to get the start and end dates of the previous, current and next years, months and weeks.

select DAT.THE_DATE,
       ADD_MONTHS(TO_DATE('01-JAN-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY'))),-12) as START_OF_PREVIOUS_YEAR,
       ADD_MONTHS(TO_DATE('31-DEC-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY'))),-12) as END_OF_PREVIOUS_YEAR,
       TO_DATE('01-JAN-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY')))                 as START_OF_CURRENT_YEAR,
       TO_DATE('31-DEC-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY')))                 as END_OF_CURRENT_YEAR,
       ADD_MONTHS(TO_DATE('01-JAN-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY'))),12)  as START_OF_NEXT_YEAR,
       ADD_MONTHS(TO_DATE('31-DEC-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY'))),12)  as END_OF_NEXT_YEAR,
       ADD_MONTHS((TRUNC(DAT.THE_DATE - DAT.DAYOFMONTH) + 1),-1)                    as START_OF_PREVIOUS_MONTH,
       DAT.THE_DATE - DAT.DAYOFMONTH                                                as END_OF_PREVIOUS_MONTH,
       TRUNC(DAT.THE_DATE - DAT.DAYOFMONTH) + 1                                     as START_OF_CURRENT_MONTH,
       ADD_MONTHS((DAT.THE_DATE - DAT.DAYOFMONTH),1)                                as END_OF_CURRENT_MONTH,
       ADD_MONTHS(TRUNC((DAT.THE_DATE - DAT.DAYOFMONTH) + 1),1)                     as START_OF_NEXT_MONTH,
       ADD_MONTHS((DAT.THE_DATE - DAT.DAYOFMONTH),2)                                as END_OF_NEXT_MONTH,
       DAT.THE_DATE  - case
                         when DAT.DAYOFWEEK = 1 then 13
                         when DAT.DAYOFWEEK = 2 then 7
                         when DAT.DAYOFWEEK = 3 then 8
                         when DAT.DAYOFWEEK = 4 then 9
                         when DAT.DAYOFWEEK = 5 then 10
                         when DAT.DAYOFWEEK = 6 then 11
                         when DAT.DAYOFWEEK = 7 then 12
                       end                                                          as START_OF_PREVIOUS_WEEK,
       DAT.THE_DATE  - case
                         when DAT.DAYOFWEEK = 1 then 7
                         when DAT.DAYOFWEEK = 2 then 1
                         when DAT.DAYOFWEEK = 3 then 2
                         when DAT.DAYOFWEEK = 4 then 3
                         when DAT.DAYOFWEEK = 5 then 4
                         when DAT.DAYOFWEEK = 6 then 5
                         when DAT.DAYOFWEEK = 7 then 6
                       end                                                          as END_OF_PREVIOUS_WEEK,
       DAT.THE_DATE  - case
                         when DAT.DAYOFWEEK = 1 then 6
                         when DAT.DAYOFWEEK = 2 then 0
                         when DAT.DAYOFWEEK = 3 then 1
                         when DAT.DAYOFWEEK = 4 then 2
                         when DAT.DAYOFWEEK = 5 then 3
                         when DAT.DAYOFWEEK = 6 then 4
                         when DAT.DAYOFWEEK = 7 then 5
                       end                                                          as START_OF_CURRENT_WEEK,
       DAT.THE_DATE  + case
                         when DAT.DAYOFWEEK = 1 then 0
                         when DAT.DAYOFWEEK = 2 then 6
                         when DAT.DAYOFWEEK = 3 then 5
                         when DAT.DAYOFWEEK = 4 then 4
                         when DAT.DAYOFWEEK = 5 then 3
                         when DAT.DAYOFWEEK = 6 then 2
                         when DAT.DAYOFWEEK = 7 then 1
                       end                                                          as END_OF_CURRENT_WEEK,
       DAT.THE_DATE  + case
                         when DAT.DAYOFWEEK = 1 then 1
                         when DAT.DAYOFWEEK = 2 then 7
                         when DAT.DAYOFWEEK = 3 then 6
                         when DAT.DAYOFWEEK = 4 then 5
                         when DAT.DAYOFWEEK = 5 then 4
                         when DAT.DAYOFWEEK = 6 then 3
                         when DAT.DAYOFWEEK = 7 then 2
                       end                                                          as START_OF_NEXT_WEEK,
       DAT.THE_DATE  + case
                         when DAT.DAYOFWEEK = 1 then 7
                         when DAT.DAYOFWEEK = 2 then 13
                         when DAT.DAYOFWEEK = 3 then 12
                         when DAT.DAYOFWEEK = 4 then 11
                         when DAT.DAYOFWEEK = 5 then 10
                         when DAT.DAYOFWEEK = 6 then 9
                         when DAT.DAYOFWEEK = 7 then 8
                       end                                                          as END_OF_NEXT_WEEK
  from PS_TL_DATES_TBL DAT
 where DAT.THE_DATE = TRUNC(SYSDATE)
 order by DAT.THE_DATE desc

What day is it son?

If you need to know what day of the week it is you could get the day number then go on the principle that 1 is a sunday, 2 is a Monday etc, to work it out. The problem with this however, is that this number can vary depending on how the Oracle environment is configured, which makes this unreliable. A better way of finding out the day is to use the 'DAY' parameter of TO_CHAR. This returns the day as MONDAY, TUESDAY etc. The only thing to watch here is that the returned value has trailing spaces, so if you're comparing it then remember to trim it first. For example: -

select DAT.THE_DATE, to_char( DAT.THE_DATE, 'DAY' )
  from PS_TL_DATES_TBL DAT
where trim(to_char( DAT.THE_DATE, 'DAY' )) in ('SATURDAY','SUNDAY')
  and DAT.THE_DATE between '01-DEC-2011' and '31-DEC-2011'
order by DAT.THE_DATE Asc

Enjoy.

Finding a table name

select *
from dba_tables
where table_name like '%<table_name>';

Creating a Database Link

I always forget the syntax for this one...

CREATE DATABASE LINK 
CONNECT TO 
IDENTIFIED BY 
USING '';

For example: -

CREATE DATABASE LINK HR89TST
CONNECT TO FBLOGGS
IDENTIFIED BY FR3DD13
USING 'HR89TST';

Note that if you have special characters in your password, you should enclose it in double quotes or you may get an error message.

To list existing database links, use: -

select * from dba_db_links;
or

Select * from ALL_DB_LINKS;

To drop a database link use: -

DROP DATABASE LINK linkname;

Pump Up The Volume

Recently I had a problem with the sound level on my EEE PC, which for no apparent reason went rather quiet. A trawl through the forumsfound the solution. What you need to do to remedy this grievous state of affairs is to take the following steps.

• Enter <ctrl><alt>T to open a command console window.
  

• Type alsamixer to bring up the sound card control panel.

• Use the up arrow to increase the PCM level, which is the first bar displayed.

• Celebrate by jacking in some power speakers and playing some Motorhead really loud.

An alternative solution would be to boost your hearing with the aid of some bionics, though you'll need to find Rudy Wells first. We have the technology.....