I think it is important to remember that Row Level Security typically results in worse performance.  It is better to create a VIEW and give your users access to the VIEW instead of going through this exercise.  

Wellll technically NULL is a valid value for all datatypes.  I think what you want to do is to interrogate the metadata information which you can do like so:

SAMPLES>s stmt=##class(%SQL.Statement).%New()

SAMPLES>w stmt.%Prepare("SELECT ID,Name FROM Sample.Person")
1
SAMPLES>w stmt.%Metadata.%Display()
(You'll get lots of output)

Note that you can do this even without running the query!  You can look at %SQL.StatementMetadata in the Class Reference for more methods and properties that can help you.  

Note 2 - Sean's method is also perfectly valid.  The only advantage to mine is that you don't have to run the query.

I agree with Mark.  If you do not understand his response and/or if you still have questions, I invite you to open an issue at wrc.intersystems.com and the Support Team will help you get to the bottom of this.

While I'm not entirely sure what you are trying to do here as MLLP is a bit foreign to me, the CSP Gateway is just a module added onto a web server.  If you can get the setup you want with a webserver, you can use the CSP Gateway to talk to Caché/Ensemble/HealthShare .  What exactly are you trying to accomplish here?  What's your goal?

Are you using a trigger generator or writing your own code?  Can you send the whole trigger definition for review?

These answers are all sufficient.  However, perhaps a more natural solution is to make the field of type %Date.  Then you can use the SelectMode to determine how the date is represented in a query.  For instance, look at the DOB field in Sample.Person (in the SAMPLES) namespace.  The storage looks like this:

^Sample.PersonD(1)=$lb(","Waterman,Brenda Z.","437-96-2023",36088,...)

Here we see the ID (1) the Name ("Waterman,Brenda Z.") the SSN (437-96-2023), and the DOB (36088).  If you want to see the data you can do the following:

--------------------------------------------------------------

SELECT Name,DOB FROM Sample.Person WHERE ID=1

Name                                     DOB

Waterman,Brenda Z.      36088

--------------------------------------------------------------

If we wanted to see that in a readable format, we can change the selectmode and get:

--------------------------------------------------------------

Name                                       DOB

Waterman,Brenda Z.   1939-10-22

--------------------------------------------------------------

Moreover, you can use this in the WHERE clause very naturally:

--------------------------------------------------------------

SELECT Name,DOB FROM Sample.Person WHERE DOB='1939-10-22'

Name                                           DOB

Waterman,Brenda Z.     1939-10-22

Underman,Jane V.          1939-10-22

Bush,Susan E.                   1939-10-22

--------------------------------------------------------------

The way to change the selectmode is going to depend on how you are running your query.  All queries that come through xDBC are in ODBC mode, so that's easy.  If you are running a dynamic SQL statement you can do:

s stmt.%SelectMode=1  

Possible values are:

• 0 for LOGICAL mode.
• 1 for ODBC mode.
• 2 for DISPLAY mode.

If you are using embedded SQL, you can use the precompiler directive #SQLCompile select=<value> as outlined here:

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY…

Eduard is right, you should use GREATEST in this case.  CASE statements are kind of sloppy, and while they are fine to use, I like to avoid them in order to keep the query as readable as possible.

More to your other points, you can find a list of valid SQL functions here:

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY…

Note that all $-functions are not SQL Standard and will only work with Caché (most likely), so if you are interested in database agnosticism then try to use one of the more standard functions.  Good luck! 

-Kyle

Yup!

DELETE FROM Sample.Person where (ID # 2) = 0   --Deletes all even numbered rows (so you know I'm not cheating and using ID numbers)

select * from (select id,name,ssn from sample.person) where %vid between 11 and 20 

%VID is a special variable in Cache SQL (VIEW ID, I think?) which allows you limit your rows without too much trouble.  And alternative is the following:

select TOP 10 ID,Name,SSN from Sample.Person where ID NOT IN (SELECT TOP 10 ID FROM Sample.Person)

The first TOP gives you your page size, and the subquery's TOP gives you where to start (in this case, we start at 11, because we're ignoring the first 10).  You can dynamically put in the sizes for TOP.  

I'm pretty sure you need to return an oref to get this working.  Here's one that I was able to get working:

Class Sample.SerialGet Extends %Persistent {
Property Address as Sample.Address [calculated];

Method AddressGet() as Sample.Address {  
    set a=##class(Sample.Address).%New()  
    set a.City="Cambridge"  
    set a.State="MA"  
    set a.Zip="02142"  
    set a.Street="1 Memorial Drive"  
    quit a
}
}

Terminal:

SAMPLES>s reader=##class(%XML.Reader).%New()

SAMPLES>w reader.OpenFile("/users/kbaxter/downloads/testopensinglequote.xml")

1

SAMPLES>s x=##class(Sample.SerialGet).%New()

SAMPLES>w x.Address

5@Sample.Address

SAMPLES>w x.Address.Street

1 Memorial Drive

I actually baked a cake to commemorate this occasion, and totally not because I'm fat and wanted cake.

Well, if you want good performance in SQL Queries, you need indexes.  What do your tables look like?  We can definitely get fast performance on a 50M row table, it might just take some work (which we're happy to help with).