How to get year in where clause - 4GL SQL

[fgiannacchi]

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.

[mark_h]

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.

[fgiannacchi]

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...

[bdittmar]

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

[fgiannacchi]

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

[mark_h]

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.

[OmeLuuk]

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.

[fgiannacchi]

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

[bdittmar]

Hello,
avoid screenshots, please

Use codetags for your source snippets.

Regards

[fgiannacchi]

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

[OmeLuuk]

Originally Posted by fgiannacchi

...
    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?

[fgiannacchi]

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