Hello to All,
I try to create an SQL statement to extract only service orders out of one year or several monthes. So how can I write a SQL script with such a restriction in the where clause?

select tssoc200.*
from tssoc200
where tssoc200._index1 inrange {:orno.f} and {:orno.t}
and tssoc200.ordt = "2014"
| and tssoc200.ordt = "02.2014" (=Feb. 2014)
selectdo
endselect


So now the question: how can I convert tssoc200.ordt in that SQL to compare it with the number or string 2014 or more difficult with Month and Year.

Best Regards Bernd

It might help you

Strings
string result(80), word(20)

word = "word"

result = sprintf$("This is a '%*s' of 10 positions",10,word)

| result contains "This is a ' word' of 10 positions"

Doubles
string result(80)

result = sprintf$("number: %-*.*f",10,2,1.2)

| result contains "number: 1.20 "

| minimum length of the double value is 10 positions

| number of digits after decimal sign is 2

For %g the style used depends on the value converted. Style e is used only if the exponent resulting from the conversion is

less than -4 or greater than or equal to the precision. Otherwise style f is used.

result = sprintf$("number: %-*.*g",10,3,1234.567)

| result contains "number: 1.23e+03 "

| e style because the exponent (3) is equal to the precision .

result = sprintf$("number: %-*.*g",10,6,1234.567)

| result contains "number: 1234.57 "

| f style because the exponent (3) is greater than -4 and smaller than the precision (6).

Amounts
| Suppose the default currency is USD ($)

| and A001 is defined as "$$ 999T999T999VD99"

string result(80)

result = sprintf$("%A001", 1234.56)

| result contains "$ 000,001,234.56"

result = sprintf$("%-10A001,hfl", 1234.56)

| result contains "fl 000.001.234,56"

result = sprintf$("%-.10A001,hfl", 1234.56)

| result contains "001.234,56"; there is insufficient space

| for currency symbol

Dates
| Suppose date format 002 is: "year/month/day in month"

string result(80)

result = sprintf$("%D002", 727168)

| result contains "1991/12/2"

| Example of substitution symbol %D(format)

string result(80)

result = sprintf$("%D(Date: %02d/%02m/%04Y)", date.num())

| result contains "Date: 12/07/1993"

result = sprintf$("Date: %D(%02d %-20H %04Y)", date.num())

| result contains "Date: 12 June 1993"

UTC dates and times
| Date format 002 is "year/month/day in month"

| Time format 001 is "12 hour format:minutes:seconds AM/PM symbol"

string result(80)

result = sprintf$("%u002 %001", utc.num(), utc.num())

| Result contains "1997/01/01 10:02:53 pm"

string result(80)

result = sprintf$("UTC: %u(%02d/%02m/%04Y) %U(%02h%x%02m%x%025 %a)", utc.num(), utc.num())

| result contains "UTC: 22/07/1997 06:24:53 am"

| provided that for the user's language the time

| separator is ":" and the AM symbol is "am"

| Using a comma after a %u substitution symbol

string result(80)

result = sprintf$("%u001, ,Message text....", utc.now())

| result containts "06-05-15,Message text...."

Hi Bernd

Why dont you write inside the query instead of writing in the query.


For Year,

(Actually you can use both string as well as number for comparison. I have used string in my code)


string hold.year

select tssoc200.*
from tssoc200
where tssoc200._index1 inrange {:orno.f} and {:orno.t}
selectdo
hold.year = str$(sprintf$("%D(%04Y),tssoc200.ordt)
if hold.year = "2014" then
| Your set of statements
endif
endselect





For month and Year


string hold.year,hold.month

select tssoc200.*
from tssoc200
where tssoc200._index1 inrange {:orno.f} and {:orno.t}
selectdo
hold.year = str$(sprintf$("%D(%04Y),tssoc200.ordt)
hold.month = str$(sprintf$("%D(%02m),tssoc200.ordt)
if hold.year = "2014" and hold.month = "12" then
| Your set of statements
endif
endselect

U can try this...

select tssoc200.*
from tssoc200
where tssoc200._index1 inrange {: orno.f} and {: orno.t}
and :1 = "2014"
wherebind(1,date.year(tssoc200.ordt))
selectdo
endselect

|***********function section***************
function extern long date.year(domain tcdate i.date)
{
long year,month,day
num.to.date(i.date,year,month,day)
return(year)
}

pre calculate the date.f and date.t

and use it in query.

Hi to All,
thanks a lot for the helpful hints. I will check it soon.
Best Regards Bernd

Hello to All,
I need your help again, cause date conversion seems to be more difficult as assumed.

string date_ordt

select tssoc200.*
from tssoc200
selectdo
date_ordt = str$(sprintf$("%u(%02d.%02m.%04Y),tssoc200.ordt))
endselect

|Result in Reports: Nothing => due to the string declaration as I assume.


extern domain tcstr.12 date_ordt, date_ordt.2

select tssoc200.*
from tssoc200
selectdo
date_ordt = str$(sprintf$("%u(%02d.%02m.%04Y),tssoc200.ordt))
date_ordt.2 = sprintf$("%u(%02d.%02m.%04Y),tssoc200.ordt)
endselect

| Result for both variants in the Report: actual date stamp but not the value out of tssoc200.ordt

Can anyone explain my the reason why

Best Regards
Bernd

Hi Bernd

There is no need to put str$ to the output of sprintf$, sprintf$ already converts the given input into the string.

Regards
Ajesh

Hi Ajesh,
yes I've also recognized that, therefore I've added the second line with date_ordt.2 . But nevertheless: instead of the converted real tssoc200.ordt value I will get the actual date (today: 16.03.2016). That is the really confusing item.
Remark: tssoc200.ordt is off type utc-date.
Best Regards Bernd

Just try adding tssoc200.ordt in the Report as an Input field.Also if you can debug try to see the value of tssoc200.ordt inside that query. What is this? A Report script?

tssoc200.ordt is declared as an Input Field in the report. I also let show the values of this field in the report to compare it with the converted fields. There I've different date values out of the past. But in the converted fields I only get the actual date.
Yes the script where I convert the date field tssoc200.ordt is a report script.
Also inside the script the converted value is wrong. I tested it with the message function.

Best Regards
Bernd

Is there maybe a library (dllxxxxx) in Infor available which solves the following problem:
extracting the yearno (YYYY as string or integer/long value) out of the utc-date field of a infor table (here: tssoc200.ordt) within a select statement (or in it's selectdo area).

Best Regards
Bernd

Hello to All,
I need your help again, cause date conversion seems to be more difficult as assumed.

string date_ordt

select tssoc200.*
from tssoc200
selectdo
date_ordt = str$(sprintf$("%u(%02d.%02m.%04Y),tssoc200.ordt))
endselect

|Result in Reports: Nothing => due to the string declaration as I assume.


extern domain tcstr.12 date_ordt, date_ordt.2

select tssoc200.*
from tssoc200
selectdo
date_ordt = str$(sprintf$("%u(%02d.%02m.%04Y),tssoc200.ordt))
date_ordt.2 = sprintf$("%u(%02d.%02m.%04Y),tssoc200.ordt)
endselect

| Result for both variants in the Report: actual date stamp but not the value out of tssoc200.ordt

Can anyone explain my the reason why

Best Regards
Bernd

Hello,

try:

date_ordt.2 = sprintf$("%u(%02d.%02m.%04Y)",tssoc200.ordt)

Regards

Hi Bernd

Maybe you have posted the script wrongly but i have seen a glaring error in your script.


extern domain tcstr.12 date_ordt, date_ordt.2

select tssoc200.*
from tssoc200
selectdo
date_ordt = str$(sprintf$("%u(%02d.%02m.%04Y),tssoc200.ordt))
date_ordt.2 = sprintf$("%u(%02d.%02m.%04Y),tssoc200.ordt)
endselect



You have not selected any record using where clause, it should be something like this


extern domain tcstr.12 date_ordt, date_ordt.2

select tssoc200.*
from tssoc200
where tssoc200._index1 = {:hold.orno}
selectdo
date_ordt.2 = sprintf$("%u(%02d.%02m.%04Y),tssoc200.ordt)
endselect



Hope i make a point here.

Hi Ajesh,
thanks for help a lot. But where-clause was not the solution.
But don't hit me :eek: the solution had another reason:
The error was I've executed the select in the before.program: section.
After moving the date-conversion without select into the detail.x: section the result was displayed in correct way.
Beg your pardon for the trouble.
Best Regards
Bernd