$system.SQL.TODATE() converts a readable format into a logical %Date value (+$Horolog).

$system.SQL.TODATE() does support converting from Julian date to %Date if you use the "J" format.  However, there is one issue; the value is returned in YYYY-MM-DD format for dates prior to 1841-01-01.  I will make a note to correct this.

 

USER>for Julian=2393465:1:2393475 w !,$SYSTEM.SQL.TODATE(Julian,"J")
1840-12-25
1840-12-26
1840-12-27
1840-12-28
1840-12-29
1840-12-30
0
1
2
3
4

 

"J"ulain format is also supported for $SYSTEM.SQL.TOCHAR():

USER>set string="2017-08-13" write !,$SYSTEM.SQL.TOCHAR(string,"J")   
​2457979

This same issue was reported a week or two ago to the WRC.

Purging cached queries should clear the issue.

It appears to be an issue where the server gets into a state where the cached query class gets an error during creation (that is not reported) and the class is created without any methods.  I believe that customer was on version 2014.1.  Can you please let us know what version you saw this problem on?

Also, if you can reproduce the issue, please contact the WRC with the details.  Thank you.

Also, %SYS.SQLSRV is a routine, not a class, which is why you could not find information on it in documatic.

Is is possible you have a version mismatch here?

There is a new parameter for %Library.Integer called STRICT, but I believe this is only in version 2016.2 and up.

I don't have much knowledge about mirroring, but if your main system was a 2016.2 field test version, and your mirrored system was 2016.1.2, the 2016.1.2 system would not recognize STRICT as being a valid type parameter for %Integer properties.

Perhaps I'm not fully understanding what you are asking for, but why doesn't using a string datatype work for you?

SAMPLES>>create table kev (mynum varchar(10))
1.    create table kev (mynum varchar(10))

0 Rows Affected
statement prepare time(s)/globals/lines/disk: 0.0084s/2003/16386/0ms
          execute time(s)/globals/lines/disk: 0.0838s/46277/360501/0ms
---------------------------------------------------------------------------
SAMPLES>>insert into kev (mynum) values ('300.6000')
2.    insert into kev (mynum) values ('300.6000')

1 Row Affected
statement prepare time(s)/globals/lines/disk: 0.0108s/2772/28131/0ms
          execute time(s)/globals/lines/disk: 0.0002s/13/153/0ms
---------------------------------------------------------------------------
SAMPLES>>insert into kev (mynum) values ('200.100')
3.    insert into kev (mynum) values ('200.100')

1 Row Affected
statement prepare time(s)/globals/lines/disk: 0.0011s/49/2516/0ms
          execute time(s)/globals/lines/disk: 0.0007s/3/153/0ms
---------------------------------------------------------------------------
SAMPLES>>insert into kev (mynum) values ('643.1230')
4.    insert into kev (mynum) values ('643.1230')

1 Row Affected
statement prepare time(s)/globals/lines/disk: 0.0006s/49/2526/0ms
          execute time(s)/globals/lines/disk: 0.0001s/3/153/0ms
---------------------------------------------------------------------------
SAMPLES>>select mynum from kev
5.    select mynum from kev

mynum
300.6000
200.100
643.1230

3 Rows(s) Affected
statement prepare time(s)/globals/lines/disk: 0.0622s/45809/254912/0ms
          execute time(s)/globals/lines/disk: 0.0002s/16/523/0ms
---------------------------------------------------------------------------
SAMPLES>>cos zw ^User.kevD
^User.kevD=3
^User.kevD(1)=$lb("300.6000")
^User.kevD(2)=$lb("200.100")
^User.kevD(3)=$lb("643.1230")

If you are on an older system where INFORMATION_SCHEMA is not available, you can use the %Library.SQLCatalog class queries.  Here is an example:

 

#include %occStatus
    new stmt,sc,rset,handle
    set stmt=##class(%SQL.Statement).%New()
    set sc=stmt.%PrepareClassQuery("%Library.SQLCatalog","SQLTables")
    if $$$ISERR(sc) { write $System.Status.DisplayError(sc) QUIT }
    set rset=stmt.%Execute()
    do rset.%Display()
    QUIT

Hi Andy,

Have the tables used by the query been tuned?  If not, I would suggest tuning the tables.

In the Management Portal, System Explorer -> SQL, switch to the proper namespace and then enter the query text in the Execute Query tab and press the Show Plan button.  Now do the same with the query that selects just a single column and compare the query plans.  Are they different? 

If they are different, post the query plans here so we can have a look.

Possible other issues:

- Are any of the fields in the multi-field select list computed, and could the computation be taking a long time?

- Is the table using default storage, or %CacheSQLStorage?  If %CacheSQLStorage, do any of the fields in the multi-select field list have expensive data retrieval logic?

- Are the tables in the from clause all base tables, or are views involved? 

- Do the fields in the select list come from the same table?

There are a couple of other options for this topic.

If you are simply wanting the ability to define the name of the "ID" field yourself, you can use the class keyword SqlRowidName.  For example:

      SqlRowIdName = PersonId

 

For an Identity property, there is a way to allow a process to explicitly specify values for the identity property/field.  This is typically useful when first populating a table/object from an external source and you want to retain the identity's values. 

The call is:   set oldValue = $SYSTEM.SQL.SetIdentityInsert(1,.sc)

For more information on the $SYSTEM.SQL.SetIdentityInsert call see class documentation for the %SYSTEM.SQL class.

 

Here is an example: