Prev
Next
Forums Performance Management Smart Notification

Formatting of Blank Dates in Smart Notification

 6 Replies
 1 Subscribed to this topic
 14 Subscribed to this forum
Sort:
Author
Messages
Kay Hamill
Basic Member
Posts: 8
Basic Member
1/15/2010 3:50 PM

    I am new to Smart Notification and am writing a query that returns a date field where some of the dates are not populated.  I am using a JDBC connection against an Oracle 10 database.

    The records with the blank dates are returned to the infoset as 1/1/1700.  I know when I run the query directly in SQLPlus, that tool returns the date as 1/1/2000, but I know that in Lawson the date is unpopulated.

    When I write the Notification, I see the 1/1/1700 populating there as well.  Is there a way that I can have these dates show up as blank in the Smart Notification?  

    Thanks!

    Matthew Nye
    Veteran Member
    Posts: 514
    Veteran Member
    1/15/2010 4:53 PM
      Kay,

      you could use raw SQL to do this. With oracle you can try using nvl function.

      SELECT nvl(date_field,null) FROM table


      If that doesnt work Id just use a simple CASE statement

      CASE WHEN datefield = TO_DATE(1700,01,01, 'yyyy-mm-dd) THEN null ELSE datefield'
      If any of my answers were helpful an endorsement on LinkedIn would be much appriciated! www.linkedin.com/pub/matthew-nye/1a/886/760/
      Jesse
      Basic Member
      Posts: 10
      Basic Member
      1/18/2010 2:09 PM

        I agree with Matt—the best way to do this would be with SQL. However, you can write your select using a decode statement to make it a little more efficient:

        e.g. using EMPLOYEE table and term_date:
        decode(emp.term_date,'01-JAN-1700',null,emp.term_date) as term_date

        Keep in mind that Lawson always stores null dates in Oracle as Jan. 1, 1700.

        Kay Hamill
        Basic Member
        Posts: 8
        Basic Member
        1/18/2010 2:26 PM
          Thanks for the information. I tried the Matt's suggestion, and unfortunately they did not work for me. I am will try the decode statement and let you know if that works.

          Kay Hamill
          Basic Member
          Posts: 8
          Basic Member
          1/19/2010 1:01 PM
            I used the decode, and with some modifications was able to get it to work. It now shows up as a string and not a date in SN, but I can live with that.

            Thanks for all the help.

            Kay
            Matthew Nye
            Veteran Member
            Posts: 514
            Veteran Member
            1/19/2010 1:07 PM
              Yeah, I was going to say, SmartNotes doesnt like blank dates. And since you repository is SQL Server, if you were to try to store Oracles version of a null date (1700-01-01) youd get errors since the smallest date that SQL Serve rcan handle is 1753-01-01. This also means you wont be able to perform any conditions on the that date field so be sure to do all that in your SQL code.
              If any of my answers were helpful an endorsement on LinkedIn would be much appriciated! www.linkedin.com/pub/matthew-nye/1a/886/760/
              thummel1
              Veteran Member
              Posts: 121
              Veteran Member
              2/7/2018 4:03 PM
                I know this is a very old post, but I have a solution that worked for us with graduation completion dates:
                CASE WHEN EMPCODES.RENEW_DATE = TO_DATE('01-01-1700 00:00:00', 'DD-MM-YYYY HH24:MI:SS') THEN null ELSE EMPCODES.RENEW_DATE END as "Test",

                This returns NULL in the SQL and appears blank in the smart notification.