How to query ds for string length

(0) Share

Report

Posted on by RadekM

172

How can I query a form datasource for a certain value length?

I need to query a field that got exactly 10 numeric charaters. Right now I'm able only to query numeric characters.

this.query().dataSourceTable(tablenum(CustInvoiceJour)).addRange(fieldnum(CustInvoiceJour, VATNum)).value(Global::queryRange('0*', '9*'));
this.query().dataSourceTable(tablenum(CustInvoiceJour)).addRange(fieldnum(CustInvoiceJour, VATNum)).value(strLen('%1') == 10);

I have the same question (0)

All responses (14)

Answers (0)

GirishS 27,827 Moderator on at

Like (0)

Report

RE: How to query ds for string length

Hi RadekM,

Try to print the query and see how the query is built for the given range.

You can use toString method of queryBuildDatasource.

Thanks,

Girish S.

Was this reply helpful? Yes No
Martin Dráb 237,658 Most Valuable Professional on at

Like (0)

Report
RE: How to query ds for string length

I would try searching for ten question marks:

QueryBuildRange vatNumRange = this.queryBuildDataSoure().addRange(fieldnum(CustInvoiceJour, VATNum)); vatNumRange.value(strRep('?', 10));

Was this reply helpful? Yes No
RadekM 172 on at

Like (0)

Report

RE: How to query ds for string length

Thanks Martin, it works but only this part of range. So I can have only numeric or only a set length. Using both returns me all Vatnums like I would set no range at all.

Was this reply helpful? Yes No
Martin Dráb 237,658 Most Valuable Professional on at

Like (0)

Report

RE: How to query ds for string length

Please look at what SQL query you've generated by your X++ code. I guess you added two ranges to the same field, as in your code above, which uses "OR" operator, while you seem to want "AND". It would be obvious if you looked at the actual query.

By the way, if you want just values that consist of numbers only, I don't think that your code does it. I believe that queryRange('0*', '9*') checks the first character only, while all other characters may be non-numeric. I would probably use a computed column to do such a check.

Was this reply helpful? Yes No
RadekM 172 on at

Like (0)

Report
RE: How to query ds for string length

As i further investigated both queries were wrong. As you said my query looked just for first character. The length query also passed string that had more than 10 characters.

So I wanted to try to create a view with a computed field but I did fail again... My code below is always on true I did hope I could use regex here the way I did.

The view is created like this:

SELECT VATNUM, DATAAREAID, PARTITION, RECID, CAST(N'' AS NVARCHAR(20)) AS MYVAT
FROM dbo.CUSTINVOICEJOUR AS T1

public class My_CustInvoiceJour extends common { public static server str queryVATNum() { #define.CompView(My_CustInvoiceJour) #define.CompDS('CustinvoiceJour') #define.VATNum('VATNum') DictView dictView; boolean matched; str ret; dictView = new DictView(tableNum(#CompView)); var VATValue = dictView.computedColumnString(#CompDS, #VATNum, FieldNameGenerationMode::FieldList, true); //SysComputedColumn::returnField(tableStr(#CompView), identifierStr(#CompDS), fieldStr(#CompDS, #VATNum)); str VATNumPattern = @"\d\d\d\d\d\d\d\d\d\d"; System.Text.RegularExpressions.Match myMatch; myMatch = System.Text.RegularExpressions.Regex::Match(VATValue, VATNumPattern); matched = myMatch.get_Success(); if(matched == true) { ret = vatvalue; } else { ret = ""; } return ret; } }

Was this reply helpful? Yes No
Martin Dráb 237,658 Most Valuable Professional on at

Like (0)

Report

RE: How to query ds for string length

You misunderstand the purpose of computed columns. A computed column method should generate T-SQL code to be included in a SQL view. Your code does nothing like that - it's return an empty string.

Before you start writing any X++ code, think about what SQL code you want to generate by X++.

Was this reply helpful? Yes No
Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report

RE: How to query ds for string length

After going through the discussion, I did not clearly understand your requirement. Can you explain the requirement again? What exactly you are trying to achieve? Because I hope the issue is resolved for 10 characters string.

Was this reply helpful? Yes No
RadekM 172 on at

Like (0)

Report
RE: How to query ds for string length

I think I finally get it but its a bit messy.

I don't know how to do it without hardcoding the sql statement because it always creates the custinvoicejour table as T1 to it end refering to itself VATNum = VATNum insted of VatNum = t1.Vatnum.

Anything to optimalize this?

public static server str queryVATNum() { SysDictTable CustInvoiceJourDictTable = new SysDictTable(tableNum(CustInvoiceJour)); DictView dictView; str ret; str reg = "'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'"; dictView = new DictView(tableNum(KSeF_CustInvoiceJour)); ret = strFmt('select top(1) %2 from %1 where %2 like %3 and %2 = t1.%2', CustInvoiceJourDictTable.name(DbBackend::Sql), CustInvoiceJourDictTable.fieldName(fieldNum(CustInvoiceJour, VATNum), DbBackend::Sql), reg); return ret; }

Was this reply helpful? Yes No
Martin Dráb 237,658 Most Valuable Professional on at

Like (0)

Report

RE: How to query ds for string length

You don't have to (and shouldn't) hard-code the alias, you just need to use the right methods, such as SysComputedColumn::comparisonField() and SysComputedColumn::returnField(). You could also utilize SysComputedColumn::compareExpressions().

By the way, you can simplify your definition of 'reg' with strRep().

Was this reply helpful? Yes No
RadekM 172 on at

Like (0)

Report
RE: How to query ds for string length

Sorry yet I'm unable to understand this class fully. Tried to use it's methods yet I don't know how to generate a string like "select field from table where" I was only able to create the second part of comparing yet the same problem with the alias occoured.

public static server str queryVATNum() { SysDictTable CustInvoiceJourDictTable = new SysDictTable(tableNum(CustInvoiceJour)); DictView dictView; str ret; str reg = strFmt("'%1'",(strRep('[0-9]', 10))); dictView = new DictView(tableNum(KSeF_CustInvoiceJour)); str exp1 = SysComputedColumn::compareExpressions(CustInvoiceJourDictTable.fieldName(fieldNum(CustInvoiceJour, VATNum), DbBackend::Sql), 'like', reg); str exp2 = SysComputedColumn::compareExpressions(CustInvoiceJourDictTable.fieldName(fieldNum(CustInvoiceJour, VATNum), DbBackend::Sql), '=', CustInvoiceJourDictTable.fieldName(fieldNum(CustInvoiceJour, VATNum), DbBackend::Sql)); ret = strFmt('select top(1) %2 from %1 where %4', CustInvoiceJourDictTable.name(DbBackend::Sql), CustInvoiceJourDictTable.fieldName(fieldNum(CustInvoiceJour, VATNum), DbBackend::Sql), reg, SysComputedColumnBase::and2(exp1, exp2)); return ret; }

Was this reply helpful? Yes No