Skip to Content
author's profile photo Former Member
Former Member

null values

Post Author: Kathleen

CA Forum: Data Connectivity and SQL

The purpose of the attamy is to pull up any client that was served in a time range. So we have been using the Intake "{CLIENT.InDateStart}" and the {CLIENT.InDateEnd} as agency open and close dates. So for instance I want to know this for 1/1/07 - 12/31/07, the open date could be anything and the close could be blank or between those date.

We have worked throught the select formula, and have came to the conclusion that the end date is giving us problems. Certain people are not showing up that should. We looked in the SQL table to see what exactly is storing in the blank end dates, and found clients that were transferred in to casper are showing blank but new clients that I've had to enter since the transfer are storing "NULL". Those clients that are have "NULL" in the end dates are not showing up on the report. So the issue is how do I get blank and "NULL" values to show on my report?

We are having the same issue in a second report where we are tracking medical appointments. We have a report that runs by appointment date. We show the date, person's name, doctor's name and comments that the doctor had during the appointment. If the comments are blank, we know that an appointment was missed. However, in the report, if there are no comments, the entire entry does not show up on the report. This is defeating the purpose of the report entirely. Please help!

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Apr 29, 2008 at 09:21 PM

    Post Author: Marcotte

    CA Forum: Data Connectivity and SQL

    Can you post your SQL statement?

    What you probably want to do is include an OR in the WHERE clause along the lines of ...

    SELECT <fields> FROM <table>

    WHERE <condition1> and ({CLIENT.InDateEnd} = "" OR {CLIENT.InDateEnd} IS NULL)

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.