Why am I only getting whole dollars out of my fex? It doesn’t make any Cents, get it? I have a sql pass-through fex (see below). I’m using sales_d/D12.2 at the output print.

For sales_d, it should be 1.99, but I getting 2.

ENGINE SQLINF SET DEFAULT_CONNECTION tagus
SQL SQLINF PREPARE SQLOUT FOR
select      a12.manufacturer_id,
                a12.department_id,
                a12.category_id,
                a11.store_id,
                sum(a11.sales_d) AS sales_d,
                sum(a11.sales_sngl_u) AS units
from         f_wlsr_data AS a11,
                p_item AS a12
where      a11.item_id = a12.item_id
 and a12.manufacturer_id = 'U0248'
 and a11.store_id = 10015
group by  a12.manufacturer_id,
                a12.department_id,
                a12.category_id,
                a11.store_id;
END

TABLE FILE SQLOUT
PRINT
     manufacturer_id
     department_id
     category_id
     store_id
     sales_d/D12.2
     units/I7
  

This message has been edited. Last edited by: Kerry,

quote:

It doesn’t make any Cents

I think the wooden spoon for that pun

What is the format of sales in the definition of f_wlsr_data? SQL passthru will not change the format unless you specify that in your SQL.

What about your data? Can you just pull out the sales column together with the grouping columns - but do not sum or group - so that you can see the raw data prior to summation and without reformatting? It could be that, with rounding, the summation of the data comes to 1.997 which would be displayed as 2.00 when the format of D12.2 was applied.

T

When you use Direct Pass Thru to an RDBMS you are not using a synonym. But you can set the precison for approximate numeric types with:

ENGINE database SET CONVERSION {FLOAT|REAL} PRECISION nn [mm]

This message has been edited. Last edited by: Clif,

Tony: DataType=Float/22. Even without Grp/Sum, WebFocus rounds to 2.00 In another tool, the same query brings back 1.99.

I thought by changing the master file field from ACTUAL=D8 to D8.2 would work, but it didn't.
FIELDNAME=SALES_D, ALIAS=sales_d, USAGE=D20.2, ACTUAL=D8.2,MISSING=ON

Clif: I tried setting the precision within the fex.... ENGINE SQLINF SET CONVERSION FLOAT PRECISION 12 2 (also tried 22 2).
But setting the precision didn't make any difference.

Thanks to both of you for responding.

quote:

I thought by changing the master file field from ACTUAL=D8 to D8.2 would work, but it didn't.

It would not work anyway, since you are using SQL passthru which gives you "direct" access to the database tables therefore skipping any WebFOCUS metadata defined on them.

Is there any way you can put your query inside of a database view and create a masterfile on the latter instead? That would give you some extra control on how you want to use your fields by tweaking the masterfile.

If you run this:

SQL SQLINF
select      a12.manufacturer_id,
            a12.department_id,
            a12.category_id,
            a11.store_id,
            sum(a11.sales_d) AS sales_d,
            sum(a11.sales_sngl_u) AS units
from        f_wlsr_data AS a11,
            p_item AS a12
where a11.item_id = a12.item_id
  and a12.manufacturer_id = 'U0248'
  and a11.store_id = 10015
group by  a12.manufacturer_id,
          a12.department_id,
          a12.category_id,
          a11.store_id;
TABLE ON TABLE HOLD AS HOLD_DATA
END
? HOLD HOLD_DATA
-EXIT

Can you post the description of the HOLD file as it'll appear in your browser? I'm just interested to see.

Of course, this all raises a question ... why are you using a SQL passthru for such a basic query? Can't you create WF synonyms on both f_wlsr_data and p_item and run your request in "standard" WebFOCUS code?

I would still like to understand why SQL passthru does not seem to be keeping the native data type as defined in your Informix database table.

njsden, Thanks for responding.
To answer your question: "why are you using a SQL passthru for such a basic query?" A: This is a test sample before pulling in more complex queries already written in sql (several). Better not to reinvent the "SQwheeL". Anything new will be written in WF.

HOLD file description:

0 NUMBER OF RECORDS IN TABLE= 33 LINES= 33
0DEFINITION OF HOLD FILE: HOLD_DAT
0FIELDNAME ALIAS FORMAT
manufacturer_id E01 A10V MISSING = ON
department_id E02 I6 MISSING = ON
category_id E03 I6 MISSING = ON
store_id E04 I11 MISSING = ON
sales_d E05 P32 MISSING = ON
units E06 P32 MISSING = ON

As Tony suggests, try changing the numeric format in your SQL code.

sum(a11.sales_d::decimal(22,2)) AS sales_d,
sum(a11.sales_sngl_u::decimal(22,2)) AS units

Dan, Thanks for responding.

The following error was caused by changing the format syntax to: sum(a11.sales_d::decimal(22,2)) AS sales_d

(FOC1400) SQLCODE IS -201 (HEX: FFFFFF37) XOPEN: 42000
: A syntax error has occurred.
L (FOC1405) SQL PREPARE ERROR.
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND

I will have to get the correct informix format syntax from my DBA and try forcing it again.

Sorry I'm not an Informix SQL expert. With Informix, supposedly built-in numeric format conversions eliminate the need for the CAST function. But maybe the language does not permit use of in-line format onversion in conjunction with the SUM verb. Hopefully your DBA can give you the correct approach/syntax for converting your FLOATs to fixed DECIMAL.

Likewise, I am not conversant with Informix but in MS SQL or Oracle SQL, even if you cannot use the equivalent of CAST with SUM in the same line, you should be able to -

  select S1.manufacturer_id
       , S1.department_id
       , S1.catgory_id
       , S1.store_id
       , SUM(S1.sales)
    from (select a12.maunfacturer_id
               , a12.department_id
               , a12.category_id
               , a11.store_id
               , CAST(a11.sales_d as data_type) AS sales
            from f_wlsr_data           a11
               , p_item                a12
           where a11.item_id         = a12.item_id
             and a12_manufacturer_id = 'U2048'
             and a11.store_id        = 10015) S1
group by S1.manufacturer_id
       , S1.department_id
       , S1.catgory_id
       , S1.store_id
;

Should you not?

T

Tony,

Thanks for your suggestion, however I received the following error.
(FOC1400) SQLCODE IS -201 (HEX: FFFFFF37) XOPEN: 42000
: A syntax error has occurred.
L (FOC1405) SQL PREPARE ERROR.
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND

---------------------------------------------
But my DBA helped me with a work-around to my ssue. By forcing the precision with a round(xxxx,2), the result is now 1.99, not 2.00. But I'm still looking for a permanent solution.

SQL SQLINF
select a12.manufacturer_id,
a12.department_id,
a12.category_id,
a11.store_id,
round(sum(a11.sales_d),2) AS sales_d,
sum(a11.sales_sngl_u) AS units
from f_wlsr_data AS a11,
p_item AS a12
where a11.item_id = a12.item_id
and a12.manufacturer_id = 'U0248'
and a11.store_id = 10015
group by a12.manufacturer_id,
a12.department_id,
a12.category_id,
a11.store_id;
END

TABLE FILE SQLOUT
PRINT *
---------------------------------------------

As mentioned previously, I changed the master file field from ACTUAL=D8 to D8.2 would work, but it didn't.
FIELDNAME=SALES_D, ALIAS=sales_d, USAGE=D20.2, ACTUAL=D8.2,MISSING=ON

I also tried setting the precision within the fex.... ENGINE SQLINF SET CONVERSION FLOAT PRECISION 12 2 (also tried 22 2).
But setting the precision didn't make any difference.

The table structure for the decimal field in Informix is Float. The W/F master file is USAGE=D20.2, ACTUAL=D8.2, should either of these be set to D16.2 (*see Infx-Def below)?

*Informix Definition:
FLOAT Stores double-precision floating-point numbers with up to 16 significant digits. The float-precision parameter is accepted in datatype declarations for compliance with the ANSI/ISO standard for SQL, but this parameter has no effect on the actual precision of values that the database server stores.

This message has been edited. Last edited by: AFS-Skier,

With Dnn.n format the underlying values will remain in the precision that they come through at and changing the data on the fly (sales_d/D12.2) is only changing the usage.

You could try specifying an actual of P12.2 and a usage of P12.2 to see what difference that makes?

As for your syntax error then you need to ratify the SQL that you used against what Informix will accept and then correct the error. But you knew that.

T

As has previously been pointed out, since you're setting the engine to Informix, SQL passthru is used in the request - no WebFOCUS meta-data (MAS, ACX) is used.

Is it possible that the default numeric precision is overridden in the code or some profile?

quote:

How to Override Default Precision and Scale
ENGINE [SQLINF] SET CONVERSION RESET
ENGINE [SQLINF] SET CONVERSION format RESET
ENGINE [SQLINF] SET CONVERSION format [PRECISION precision [scale]]
ENGINE [SQLINF] SET CONVERSION format [PRECISION MAX]

Try adding

ENGINE SQLINF SET CONVERSION RESET

before your SQL statement to reset any overrides.

Tony, Changing the precision of the master file's actual and usage to P12.2, did make any difference.

Francis, The command "ENGINE SQLINF SET CONVERSION RESET", did make any difference.

The only work around I have so far is; Round(xxxx,2). But again this not a permanent solution to resolve future fex issues w/informix sql pass-thrus.

Thank you to both of you for responding,
Kevin