Re: problem with SQL query manager

On 19-Mar-2014 08:19 -0700, Dale Janus wrote:

I was using Query Manager to create a quick inventory cost report
for selected vendors. Our material is stored in feet and inches and a
cost per foot. I divided inches by 12, added to feet and multiplied
by cost per foot. This was a created expression using the prompts in
query manager The result field was 32 positions long. While I could
read the detail data, the total line was too long to display. (maybe
it would have printed, I did not try).

There seems to be nothing here related to RPG.?

So I tried rounding the expression and truncating the expression.
Result was the same.

The ROUND and TRUNC scalars generally do not modify the data typing\sizing of the expression. An exception for rounding: "The data type and length attribute of the result are the same as the data type and length attribute of the first argument, except that precision is increased by one if expression–1 is DECIMAL or NUMERIC and the precision is less than the maximum precision (mp)."

Can anyone tell me how to define the length of an expression in
query manager?

Via the SQL, with casting, not via the reporting; i.e. reporting length [and editing] is merely for presentation.

The data type, precisions, and scale are defined by the SQL query. The latter may be influenced by the Decimal Result Options established for the query; e.g. Maximum Precision, Maximum Scale, and Minimum divide scale. On the RUNSQLSTM command, the means to influence the effect is the Decimal Result (DECRESULT) parameter. I am unaware if there is a means via the QM Query feature to establish those attributes aside from the defaults.

Further comments inline.

Here are some screen shots. <ed: reformatted:>


Define Expressions
...

Name Expression
COST2 ROUND(COSTFT+COSTIN,2)
COST TRUNCATE(LENGTH*INCOST,2)


Edit Column Formatting
...

Column Heading Usage Edit Seq Indent Width
INVEN# VENDOR_NUMBER 1 2 7
INODM OD 2 2 6
INWALL WALL 3 2 5
COST COST 4 2 33
INFT LENGTH_FEET 5 2 6
ININ LENGTH_INCHES 6 2 6
LENGTH LENGTH 7 2 33
INCOST COST/_FOOT 8 2 9


Display Report
...
VENDOR LENGTH LENGTH C
NUMBER OD WALL COST FEET INCHES LENGTH F
------ ------ ----- ---------- ------ ------ ---------- -----
194613 3.500 .438 ?????????? 6 ********** 11
194613 3.500 .438 ?????????? 1 6 ********** 11
194613 3.500 .438 ?????????? 1 7 ********** 11
194613 3.500 .438 ?????????? 22 8 ********** 11
194613 3.500 .438 ?????????? 35 5 ********** 11
194613 3.500 .438 ?????????? 35 6 ********** 11
...

For an explanation of the presentation results:

(Report Data) - Help

If an error condition occurred for any of the numeric fields,
those fields are filled with:

o Asterisks (***), if query management cannot show a field
value because the specified column width is too small for
the report text.

o Question marks (???), when query management cannot show a
field value because it cannot determine the value. This
may happen when:

- Division by zero occurs in calculating the field value.
- Data in a numeric field is not valid.
- Overflow occurs in an intermediate result used to
calculate the final result.

o Greater than symbols (>>>), if a field value overflow occurs.


The likely origin for the issue is default /precision reduction/ rules. Refer to the following docs, the parent topic and siblings, for more information:

<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzdecarithmetic.htm>
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference -> Language elements -> Expressions -> With arithmetic operators
_Decimal arithmetic in SQL_
"The following formulas define the precision and scale of the result of decimal operations in SQL. The symbols p and s denote the precision and scale of the first operand and the symbols p' and s' denote the precision and scale of the second operand.

The symbol mp denotes the maximum precision. The value of mp is 63 if:
..."