Hi,
I'm trying to get the year of a date, in a where clause, in a report script.
In MSSQL I can use YEAR(column_name) to get the year, but I've no idea how to do such a thing in 4GL.
Can you help me please?

Thanks

Funny - in 4c4 I never did it in a query that way. I always did something like I want all records for year 2008 - so I would look for any record with a date >= 1/1/2008 and date<=12/31/2008. I would ask for a year (and/or a month) and then create the date using num.to.date and then run the query. I am going to search a bit and see what I can find.

Or in some cases I found where we just put in side the selectdo a little code to get the year and if year not equal to the year looking for then just continue to next record.

You can try the bind option in this http://www.baanboard.com/baanboard/showthread.php?t=68705&highlight=year+query.

Thanks!
I used wherebind, but at compilation time an error occurs.
I'm trying to compare the year obtained from the date.year() function and the anno variable, obtained by the import("tftax225.vyer", anno ) statement.

You will have to wait for someone with more experience than me, I never used wherebind - I always set dates like I said before the query and then did a range within the query.

I have some questions:
1) It seems that the column tftax221.stdt (see query.png) is of type TIMESTAMP. How can I convert a TIMESTAMP to a date?
2) I have only the year, not the date. How can I create a date starting from the year? Based on your answer I can check if the date (based on the year) is between the 1/1/year and 31/12/year
3) The function recalculate.exemption.limit() is called for every row? Because I can't know the exact date based on tftax221.stdt before the query, I suppose.

I'm sorry about these questions but unfortunately I'm new about LN and 4GL and none of my colleagues help me...

Keep in mind I am not on LN - I am 4c4 so I am basing my answers off my experience and from reading the board. So hopefully an experienced LN user can provide more than me. I used something like this:
start.date = date.to.num(year.f, 1,1)
stop.date = date.to.num(year.f,12,31)

Then in the query I would look for table.date>=:start.date and table.date<=:stop.date.

I am not familiar with the type of timestamp, but it sounds like a utc date - you can search this forum for utc date and read lots of threads. Just one such post where Bhushan http://www.baanboard.com/baanboard/showthread.php?t=70565&highlight=utc_date showed how he would convert it inside the selectdo.

Thanks, I tried but something is not working...
Seems that the inner query under the if statement is not performed.

Is it possible to imitate the WITH query_name(cols) AS (...) in 4gl?
Is using an if statement the only way to imitate that behaviour?

Also, the inner query uses the results obtained by the outer query?
So, if I use sum(tftax221.amnt) in the inner query, the column used for the sum() function will be the column obtained by the first query?

You can try this, to repalce your first select.
ps. not tested

domain tcdate start.date | domain for date/time
domain tcdate end.date

start.date = date.to.utc(anno, 1, 1, 0, 0, 0) | this gona create date with time
end.date = date.to.utc(anno, 12, 31, 23, 59, 59)

select tftax221.amnt
from tftax221,
tftax225,
tccom100
where tftax221.bfbp = tftax225.bpid
and tftax221.bfbp = tccom100.bpid
and tftax225.vyer = :anno
and tftax221.stdt inrange :start.date and :end.date | this gona check if tftax221.stdt is in anno/year
and tftax221.ceno = tftax225.ceno
group by tftax225.vyer, tftax225.bpid, tccom100.nama, tftax221.stdt, tftax221.amnt
selectdo
| your secound select

endselect

Not knowing the tables or the fields I took your ms_sql and tried to put it in baan 4gl - so this is just a wild swag and assuming utc dates and times work - I am on 4c4 and never worried about times with dates. So not knowing the tables I did my best to translate it.

start.date = date.to.utc(anno, 1, 1, 0, 0, 0) | this gona create date with time
end.date = date.to.utc(anno, 12, 31, 23, 59, 59)

select tftax225.vyer,
tftax225.bpid,
tccom100.nama,
sum(tftax221.amnt)
from tftax225, tccom100, tftax221
where tftax225.vyer = :anno
and tccom100.bpid = tftax225.bpid
and tftax221.bfbp = tftax225.bpid
and tftax221.ceno = tftax225.ceno
and tftax221.vtyp = 1
and tftax221.rgdt inrange :start.date and :end.date
group by tftax225.vyer,
tftax225.bpid,
tccom100.nama

I am think this would give you records like this:
Year Business Partner Name Tax amt for year
2022 12345 Google 2222.22
2022 12346 Yahoo 3333.33

Thanks!
I used wherebind, but at compilation time an error occurs.
I'm trying to compare the year obtained from the date.year() function and the anno variable, obtained by the import("tftax225.vyer", anno ) statement.The error states that you compare number (long) with integer (domain tfgld.year) Why not output domain tfgld.year in your function?

Thanks, I tried but something is not working...
Seems that the inner query under the if statement is not performed.

Is it possible to imitate the WITH query_name(cols) AS (...) in 4gl?
Is using an if statement the only way to imitate that behaviour?

Also, the inner query uses the results obtained by the outer query?
So, if I use sum(tftax221.amnt) in the inner query, the column used for the sum() function will be the column obtained by the first query?

Hmmm kind of hard to answer, because you do not speak the Baan SQL dialect...
Indeed the values of your "outer" query are not used in your "inner" query, when you need variables from outside your query you will need to refer to them with a colon so they get imported into your SQL (like :tftax221.stdt)

Think of a SQL statement as being executed outside your script (in the database driver) and only the variables "imported into the statement" using the bind (colon : ) mechanism will be taken into or put outside.

Thanks for your answer, but I don't understand.
If I want to make the inner query to use the results of the outer query I must use the colon?
So, in the attached query I must write sum(:tftax221.amnt)?
Because I tried and an error occured (see error2.png)

I must save the outer query result in a variable and then use that variable inside?

Thanks, I tried but the same result is repeated for each line.
I need to save the result of sum(tftax221.amnt) in a field on the report (e.exemption.limit). Is that the right way to do that? (see attached image)

Hello,
seems you have to use the logic in proper reportlayout.

e.g. after.tftax225.bpid

Regards

Thanks!
Now the report shows all the possible results but duplicated.
The query must be split into 2 pieces:
1) Outer query that retrieve the amounts based on various conditions and group by some things
2) Inner query that uses the outer query results to make the sum of the amounts and groups by bpid, name and year
(see image)

It's the only way to get the results without duplicates.
And I have no idea how to do that.

Like bernd said I think the key is finding the right layout in the report script. Without knowing the report before and after it is hard to speculate. There are so many things it could be - the session script sending 2 records when you want 1, sorts not correct or different, printing in the wrong layout (like printing detail layout) when you really want to print in after.field layout, and or the sum can be wrong layout. I would first get the report printing with the right fields like getting bpid, name and year - all looking correct on the report. Because if you get 2 identical records for those fields then you know it is the session script. Then add in the sum in the correct layout. Bout the best I can do.

And running a report in debug mode can show you all this as you step thru it. Watching each layout and piece of code getting hit.

I have no idea how to launch report in debug mode...
The attachment contains both old and new report info

Sorry for bothering you but I've no choice...
First time with LN, 4gl, etc and no one in my company helps me comprehensively...

Hello,
you can compile the report with option "compiler".

While running the report you should be able to trace the variables, fields and their values.

The debugger
The debugger is an interactive statement interpreter that enables you to control and test
the execution of a program. Its principal features include:

simultaneous program execution and display of debug information
source instructions and debug information are displayed in their own windows, separate
from the application source instructions are displayed in a window that you can move
and resize on non-graphical displays, debug information is displayed in screen
columns 81 to 132 the debugger supports variable tracing during program execution
(that is, all changes to the values of specified variables are displayed during program execution)
the debugger is fully symbolic – machine addresses are not used
the debugger is fully integrated with the bshell.
The debugger works only with source programs that have been compiled with
the debug option (-l in the case of the BAAN Compiler). When you start such a program, two
additional windows are displayed. One displays the source code being executed
(the line currently being processed is highlighted).
The other is a command window where you input debugger commands and where
command results and error messages are displayed.

For information about other debug facilities, see the Logic Server section
in the Infor Enterprise Server Technical Manual.


Debugger commands
Debugger commands consist of from one to three words; the words can be separated from each other by one or more spaces. The assignment command is an exception. Here the variable and the value to be assigned are separated by a ':=' sign. Most commands require a line number and/or a source name. If no source name is specified, the default is the current source.

When you start the debugger for an object, the source used by the debugger is first searched for by using the directions in the file fd x.x.<pack.comb.> (where x.x is the bshell version), then it is searched for in the $BSE/tmp directory, and finally in the current directory.

Command Action
b [[source] line_number] Toggle a break point on the current or specified line of the current or specified source. The line is highlighted, when break point is set.

Note that you cannot set break points in included parts in a source. Instead the break point must be set in the included file.

B [source] Display all set break points in the current or specified source.
c [number] Continue (execute the source) up to the next break point. If you specify a number, the debugger skips that number of break points. The executed source lines are not displayed.
CC [number] Same as 'c', except that the executed source lines are displayed.
cc Change the size of the command window. This is valid only when application and debugger are in the same window.
cs Change the size of the source window. This is valid only when the application and debugger are in the same window.
d [source line_number] Delete the break point on the specified line number in the source. If you do not specify a line number, the debugger provides the opportunity to interactively delete any break point in the current source file.
D [source] Delete all set break points in the specified or current source.
delete all Delete all traces and stop instructions.
delete number Delete a specified trace or stop instruction.
ds Delete the source window.
dd Generate a dump of the data dictionary.
<expression> Execute the specified expression.
f [noarg|noname][number] Display stack trace (present nesting of functions), with the arguments of the functions. If you specify a number, only that number of nestings is displayed.

noarg only the names of functions are displayed

noname arguments are displayed but not their names

fdebug Show the open tables on the command window.
Fdebug Print the open tables to the spooler.
g [source] line_number Continue program execution at the specified line number (goto).
help (or ?) Start the Help Viewer for debugger options.
l Display the full list of program variables.
L Display the list of program variables that have a value, and display their current values. To stop displaying the contents of an array, enter 'n' at the question 'More ?'.
lp Print the output of the 'l' command.
Lp Print the output of the 'L' command.
mem Generate a dump including information about the memory allocated.
p Go to the source line currently being processed (this is useful after the view or seek command). The line is indicated by a greater than (>) sign before the line number.
q Exit the debugger (quit).
return Continue execution until the end of the current function.
s [number] Execute the program step by step, the specified number of instructions at a time (default is 1). During execution, the intermediate source lines are not displayed.
S [number] Similar to 's', except that a function call is executed as a single statement.
slow [number] Reduce the rate of running source code. The maximum number is 100; the minimum (and default) number is 0.
split number Split the command and source window. The specified number is the last line of the command window. This is often used after resizing the Debugger window.
status [trace|stop|number] Display traced variables and/or stop instructions. If you specify a number, only that traced variable or stop instruction is displayed.
stop if <expression> Stop execution when expression is True.
stop in <function> Stop execution when function is entered.
sym Generate a dump of the symbol table.
trap off Disables ‘trap on’. This command is available only when the bshell was started with the -dbgcpu flag.
trap on The debugger stops after execution of every function call. This command is available only when the bshell was started with the -dbgcpu flag.
swin off There is a separate window for stack, array and long string in debugger. The appearence of this second window can be switched off with "swin off".
swin on There is a separate window for stack, array and long string in debugger. The appearence of this second window can be switched on with "swin on".
t variable Trace the specified variable during program execution. The execution of the program stops every time the value of the variable is changed. You cannot trace array variables or common variables.
T variable [value] Same as 't', except that program execution is not stopped when the variable changes. If you specify a value, execution stops when the variable gets that value.
u [variable] Stop tracing the specified variable. If no variable is specified, you can stop tracing each variable interactively.
U Stop tracing all variables currently being traced.
v [source] line_number Display a specified source line in the source window (view). If line_number is greater than the total number of lines, the last line is displayed.
v {+-} number Shift the display to the source line that is number lines before or after the current source line.
/pattern Seek a matching text pattern in the current source and display that line.
[function.]variable [/option] Display the value of a specified variable. If you specify a function, the value of the local variable of that function is displayed. Otherwise, the value of the variable of the current function or the global variable is displayed. If a variable has no value (empty string or zero number), the value is not displayed.

/ option can have the following values:

/ Use to display the value of a variable that has the same name as a Debugger command. For example, ‘b/’ displays the value of variable b.
/d Print the flags of the variable and the dimension for arrays.
/D Display the number of bytes allocated in a long or double variable. Display the number of bytes for one element in a string array.
/g For a string containing the compact string representation of a UUID, display the standard string representation of the UUID. See UUID overview.
The following options are useful for record buffers and binary data. They display the value of a string variable with its full declared length, regardless of NUL characters.
/x NUL characters are displayed as ‘^@’.
/X Characters with values 0 to 31 are displayed as ‘\x<value in hex>’.
/b All characters are displayed as ‘ \x<value in hex>’.
/l Interpret field as UTC field and print in local time and prints current time zone.
/u Interpret field as UTC field and print in utc time.


variable := value Assign a value to a variable. The value can be:

a string constant or expression

a numeric constant or expression

a variable name

vi Activate the vi editor for the current source at the current source line.
Changing the source has no influence on the source displayed by the debugger in the source window.
width value Change the width of command and source window. You can specify any value in the range 80 to 132.
<Ctrl>D/<Ctrl>U Page down or up in source.
<Esc> Toggle between normal and history mode.
<Arrow up>/ <Arrow down> In history mode, these display previously executed commands
on the command line. In normal mode, these move the cursor through the source.

Regards

I did some test...
If I put something into the detail section, the report will show mixed unique and duplicate values. If I put only into the before.tftax225.vtyp section, the report will show the correct amounts for each month, unique values (BP) but the third column shows me the same value for each row...

No idea how to handle this...
Tried to compile with debugger but I don't understand much about it...
The only thing that happened is the creation of the attached file

That file looks like what I would normally step thru in debug mode. I do not know how to get LN to launch a report in debug mode where you step thru 1 line at a time. I know in 4c4 we had to give people permission to see the code in debug mode - not sure if that is the same in LN or not.

When compiling a report in debug mode, two things happen.
For a report
1) the "standard program" of the report runs in debug
2) the "report script" runs in debug

And although very interesting indeed, this standard program is rather confusing because it allows you to step through (most generated) code that you cannot change anyway. And actually it is of no use because that is not the code you influence.

So just after compiling the report look carefully to the output of the debugger.
You will notice there are actually two steps involved.
1) repgen This is the report generator that merges the report script with the standard logic into one compilable (temporary) file. This is the file you showed. That file is used as input for the
2) bic the actual compiler (with option -lo, where l indicates debug mode).

When you delete the generated source file from step 1 before you start debugging, you will only debug your own created report script. It will issue a warning "Source file not found" but since you do not want to see that, it is ok.

Then you can step through the debugger.
Helpful commands are
? - show help
f - show function stack (how did I come here with what arguments)
t (or T) - for trace a variable (capital will display value change but not stop debugging)
b - for toggle breakpoints
s - for stepping through (and S for over functions)
c (or C) - for running the code (capital will not display the source code you are running)
you can enter a variable name (or in graphical mode click on a variable) to learn it's current value.

Apart from this crash course, search the forum on debugging.

I've just fixed (a little) the query and now it works better (into before.tftax225.bpid), but many values are incorrect. That's because I can't make a query based on the first one.
So, please, there's a way to tell ln/baan/4gl: "take the results of the first query, and (using those results) sum all the amounts and group by these fields (bpid, vyer, nama)"?

Thanks

Hello,
avoid screenshots, please

Use codetags for your source snippets.

Regards

This is the query:

select sum(tftax221.amnt):e.exemption.limit
from tftax225, tccom100, tftax221
where tftax225.vyer = :anno
and tftax221.vyer = :anno
and tccom100.bpid = :tftax225.bpid
and tftax221.bfbp = :tftax225.bpid
and tftax221.ceno = :tftax225.ceno
and tftax221.vtyp = :type
group by tftax225.vyer, tftax225.bpid, tccom100.nama, tftax221.amnt
selectdo
endselect


Thanks

...
select sum(tftax221.amnt):e.exemption.limit
...
group by tftax225.vyer, tftax225.bpid, tccom100.nama, tftax221.amnt
...
Why would you only need a sum of all amounts with the same value?
And is :anno enough as a delimiter on tftax225?

I guess so.
This is the original in MS-SQL:

WITH qry(yer, bp, nama, amnt)
AS (
SELECT
t1.t_vyer,
t1.t_bpid,
t_nama,
t3.t_amnt
FROM ttftax225100 t1, ttccom100100 t2, ttftax221100 t3
WHERE t1.t_vyer = 2022 AND
t3.t_vyer = 2022 AND
t3.t_bfbp = t2.t_bpid AND
t3.t_bfbp = t1.t_bpid AND
t3.t_vtyp = 2 AND -- hard coded
t1.t_ceno = t3.t_ceno
GROUP BY t1.t_vyer, t1.t_bpid, t_nama, t3.t_amnt
)
SELECT bp, nama, SUM(amnt)
FROM qry
GROUP BY yer, bp, nama
ORDER BY bp

So I tried to imitate as much as possible this query

I would use something like this:
(It's uncompiled and untested, but I think you can get the idea.)
|"It don't come easy" (Song by Ringo Star)

|First step: grouping
|We don't need grouping by bpid and nama, because it's a 1-1 relation
select tftax225.vyer, tftac225.bpid
from tftax225, tftac225
where tfrtax225.vyer = 2022 | use: tftax225.vyer = :anno , when ammo is a variable
group by tftax225.vyer, tftac225.bpid
selectdo
|Step 2: select the data for output
select sum(tftax221.amnt):sum.amt
from tftax225, tftax221
where tftax225.vyer = :tftax225.vyer
and tftax225.bpid = :tftax225.bpid
and tftax221.ceno = tftax225.ceno
and tftax221.vtyp = 2 |tfgld.vtyp.out
selectdo
|Just in case you do need tccom100.nama in the report:
|(This can be merged with step 2)
select tccom100.nama
from tccom100
where tccom100.bpid = :tftax225.bpid
selectdo
endselect
endselect
endselect