PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 02/07/2018 11:03 AM by  thummel1
Formatting of Blank Dates in Smart Notification
 6 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Kay Hamill
Private
Private
Basic Member
(18 points)
Basic Member
Posts:8


Send Message:

--
01/15/2010 10:50 AM

    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
    Private
    Private
    Veteran Member
    (1532 points)
    Veteran Member
    Posts:514


    Send Message:

    --
    01/15/2010 11:53 AM
    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
    Private
    Private
    Basic Member
    (26 points)
    Basic Member
    Posts:10


    Send Message:

    --
    01/18/2010 9:09 AM

    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
    Private
    Private
    Basic Member
    (18 points)
    Basic Member
    Posts:8


    Send Message:

    --
    01/18/2010 9:26 AM
    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
    Private
    Private
    Basic Member
    (18 points)
    Basic Member
    Posts:8


    Send Message:

    --
    01/19/2010 8:01 AM
    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
    Private
    Private
    Veteran Member
    (1532 points)
    Veteran Member
    Posts:514


    Send Message:

    --
    01/19/2010 8:07 AM
    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
    Data Analyst II
    Fairview Health Services
    Veteran Member
    (195 points)
    Veteran Member
    Posts:107


    Send Message:

    --
    02/07/2018 11:03 AM
    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.
    You are not authorized to post a reply.