Quote used in sql function not working for me.

lm0@logic1.com.au

.Field(new Field("isnull(anyDBField,'')","name")) **always incorrectly returns a null**
.Field(new Field("isnull(anyDBField,'constant')","name")) **always returns an error**

When I have an SQL function per this
and the function includes a quote I can't get it to work.

Even .Field(new Field("fieldname + 'constant' ","name")) does not work for me.

Any assistance would be appreciated.

allan

Hi,

Sorry you are running into a problem with this. We've got a power cut here at the moment, so I can't actually test this myself (main computer is down, working on my phone...), but, could you add .Debug(true) just before the .Process(...) call, and then show me the return from the server when that is run? It should include the SQL that Editor is building and will hopefully indicate where the error is.

Thanks,
Allan

lm0@logic1.com.au

Thank you for your prompt response.

Query when error returned:

SELECT [Id] as 'Id', isnull(SegmentSplit,'constant') as 'isnull(SegmentSplit,'constant')', [PartCode] as 'PartCode', [OriginalString] as 'OriginalString', [SegmentNo] as 'SegmentNo', [SegmentSplit] as 'SegmentSplit', [SegmentData] as 'SegmentData', [count] as 'count', [Guess] as 'Guess', [Classification] as 'Classification', [Models] as 'Models' FROM [PartMeta]

C# code-
.Field(new Field("isnull(SegmentSplit,'constant')","SegmentJoin"))

Query when NULL is incorrectly returned, SQL Management Studio returns an empty string.

SELECT [Id] as 'Id', isnull(SegmentSplit,'') as 'isnull(SegmentSplit,'')', [PartCode] as 'PartCode', [OriginalString] as 'OriginalString', [SegmentNo] as 'SegmentNo', [SegmentSplit] as 'SegmentSplit', [SegmentData] as 'SegmentData', [count] as 'count', [Guess] as 'Guess', [Classification] as 'Classification', [Models] as 'Models' FROM [PartMeta]

C# code-
.Field(new Field("isnull(SegmentSplit,'')","SegmentJoin"))

What I am trying to do
.Field(new Field("SegmentNo + isnull(SegmentSplit,'')", "SegmentJoin"))
Incorrectly returns NULL despite value in SegmentNo.

lm0@logic1.com.au

@Allan,
Did you get a change to look at this?

allan

Hi,

Really sorry for the delay in looking into this. I was tracing it through this morning and it is indeed the quoting of the field - it wasn't escaping the quote characters inside the alias. I've corrected that now. I could send over a rebuilt dll if you like?

However, I don't recommend this approach with Editor. Insert and Update commands will fail with a field like that (since it will attempt to use the function!). I would strongly suggest using a client-side renderer now that I've seen a little more of what you are doing. E.g.:

.Field(new Field("SegmentSplit")
  .SetFormatter(Format.IfEmpty(null))
)

And on the client-side for the column in the DataTable:

{
  data: 'SegmentSplit',
  render: function (data) {
    return data !== null
      ? 'SegmentNo: ' + data
      : 'No data';
  }
}

The other option is to use a GetFormatter on the server-side to do the same logic - however I'm a big fan of having the raw data over the wire so you can do locale specific rendering on the client-side (not so much an issue in this specific case, but useful for dates and numbers).

Allan

lm0@logic1.com.au

Thank you, I have already changed it to a client-side render,
Will the fix be in the next version?

allan

Yes, Editor 2.2.3 or 2.3 (not sure on the name of the next release yet - it depends on other aspects) will carry this fix.

Allan