Hello to All,

where can I get a full list of valid SQL commands and functions for Infor LN.

Especially I'm searching for an equivalent of Oracle's lpad/rpad (left / right-pad) for strings filled with numbers.

Best Regards
Bernd

OK, maybe the question for a SQL manual is to generell, but maybe someone can help me with the more special problem I've mentioned too.

In a new table I have branch groups with related branch codes. These branch codes consists out of range of numbers.
Example: BRGR01 = 21 to 213456
For this example the branch code of 31 is part of the branch group BRGR01, when the codes will interpreted as numbers. But it shouldn't.
Now the branch codes are strings with the length of 10. To evaluate inside a SQL query, if a branch code is in that range, I'd like to add "0" from the right side to the numbers. It shouldn't matters how long the original value for the branch code is, the result should have a length of 10.
In my example: 21 => 2100000000 and 213456 => 2134560000
Then 31 becomes 3100000000. And that number it's obviously not inside this branch group.
So, now my question is: how can I add (right padding) "0" (Zeros) to the string numbers to fill them up to a length of 10 within a SQL-Statement.

Best Regards Bernd

Hi,

You can use string.set$() function to pad the digits - >

string grp(100)
string padded.group(100)

grp= "31"
padded.group= trim$(grp) & string.set$("0", 10 - len(trim$(grp)))

This will add 8 digits to 31 and hence will make it "3100000000". You can then use it in your SQL query selection.

For example, branch.code is the string .


long char.to.be.filled

store.branch = str$(branch.code)

if len(store.branch) < 10 then

char.to.be.filled = 10 - len(store.branch)

for i = 1 to char.to.be.filled
store.branch = store.branch & "0"
endfor

endif

function main()
{

string src(1000),trg(100), url.to.process(150)
long ret,exitcode,tst,sql_id





| in my data .. i have position 10 and 800 ==> converted to 10000 and 80000


src = " select xyz.pos:2 " &
" from ( " &
" SELECT case " &
" when tdsls401.pono < 10 then tdsls401.pono * 10000 " &
" when tdsls401.pono < 100 and tdsls401.pono >= 10 then tdsls401.pono * 1000 " &
" when tdsls401.pono < 1000 and tdsls401.pono >= 100 then tdsls401.pono * 100 " &
" when tdsls401.pono < 10000 and tdsls401.pono >= 1000 then tdsls401.pono * 10 " &
" when tdsls401.pono >= 10000 then tdsls401.pono " &
" end as pos " &
" from tdsls401 " &
" where tdsls401.orno = '671100030' " &
") as xyz" |
& " where xyz.pos < 50000"

sql_id = sql.parse( src )
ret = sql.select.bind(sql_id,2,tst)
ret = sql.exec(sql_id)

while sql.fetch(sql_id) = 0
message("%d",tst) | only postion 10000 being printed since there is condtion
endwhile


}

Hello Bhushan, Ajesh and Vamsji,

thanks a lot for your help. It seems all of your solutions will fit. But I still not able to test is, cause I've first have to transform my select-statement into a selectbind-statement.
First I'd like to handle this problem in SQL but Infor-SQL is not that flexible as Microsoft- or Oracle-SQL.

Best Regards Bernd

Hello to All,

thanks for help. I've solved the problem with Bhushans code. It seems to fit best to my problem and now it works well within my solution.

Best Regards Bernd

where can I get a full list of valid SQL commands and functions for Infor LN.

You can download the help file, its similar to the Tools Programmers guide.

Title Infor ES Connectivity SQL Language Guide
Version 10.4.2_sql (10.4.2_sql)
Publication date 2015/11/23 11:24 AM CET
Copyright © 2015 Infor

Hello NPRao,

thanks for that advice. I've found a hint in the https://docs.infor.com (Pic1)
But there it's also mentioned that this document is only available as HTML (online-help?). And I can't access the web from my developing computer. So I would prefer a PDF-Document. But that is not accessible.

Maybe you can send me the file name (like: ln_10.4_devstudioag__en-us.pdf). So I can find it directly in the document listing (Pic2)

Best Regard Bernd

Maybe you can send me the file name (like: ln_10.4_devstudioag__en-us.pdf). So I can find it directly in the document listing (Pic2)
We are not allowed to send the Infor Copyright documents on the forum here.

Refer to the following KB -
https://www.inforxtreme.com/espublic/EN/AnswerLinkDotNet/SoHo/Solutions/SoHoViewSolution.aspx?SolutionID=22924522

KB 22924522 The programmers manual for ERP LN
Description:
This solution will contain the latest published version of the Infor ES programmers guide, the Infor ES Connectivity SQL Language Guide, and the Design Principles Guide.

Hello NPRao,

thanks for that link! I've found the files. But I was not able to download them cause of security reasons from Infor.
So I've opened a support case on "Infor Xtreme" and they have send me the *.chm help files.

Thanks a lot for help.

Best Regards
Bernd