Clarity

I try to make it possible to filter the results of a query using decimals, but it seems to accept only integers.

I tried using: @WHERE:PARAM:USER_DEF:float:cb_index@  but it doesn't seem to help.

Is it possible?

Hi ANTHONY,

I think that float data_type is not possible to use as NSQL parameters, and integer and string are valid data_type for parameters. 

I checked "User-Defined NSQL Constructs" section in CA PPM Studio Development manual and it says below.

https://docops.ca.com/ca-ppm/15-4/en/reference/ca-ppm-studio-development/ca-ppm-studio-nsql-queries#CAPPMStudioNSQLQueri… 

User-Defined NSQL Constructs

All parts of the SELECT clause must use special NSQL syntax and be specified with an NSQL @SELECT@ construct.

Data Types

The following data types are supported in NSQL. They are valid only as part of the Dimension, Properties, and Metrics columns, and cannot be used as parameters.

• IMPLIED indicates there is no need to qualify a data type; the database uses what it contains. This data type is allowed only in @SELECT…@ constructs because the NSQL engine can retrieve information about the data type from this location only.
• MONEY (<currency column>) specifies that the value is a monetary amount. The column alias in parentheses specifies the currency. The currency must also be part of the SELECT statement.
• STRING specifies a basic string that cannot be manipulated.
• INTEGER represents a whole number that has no fraction or decimal component. The minimum and maximum limits depend on the capabilities of your database. As an example, use a unique integer data type for an employee ID field.
• FLOAT represents a decimal or floating-point number (for example, 2.375). The minimum and maximum limits, and the precision of values, depend on the capabilities of your database.
• DATE represents a date and time value. The default data format and supported conversion functions depend on the capabilities of your database. Typically, the default date-time format is YYYY-MM-DD HH:MM:SS.

I have searched document which describes about valid data_type for NSQL parameters, but I have not got it yet.

Regards,

Shoichi

Is there any reason why the NSQL has to have NSQL parameter, in your case, prpctcomplete?

Why can't you just push over the attribute required to be filtered into the Portlet Filter.  For example, using prpctcomplete on prtask in a custom query, I was able to filter on 0.2 which returned tasks which are at '20%'.  The only issue I experienced was that the filter automatically changed the display to be 0  in the filter, but it returned the required tasks which were at 20%.

Hi Anthony,

In accordance with the above provided points, you may refer to the following thread -> NSQL: how to declare variables? 

Some discussion has been carried out in similar topics

Regards

Samik