Forum

Hello, I have writed a custom query in a dynamic list.I want to round up UnitPrice.As I hear,I must use CAST keyword.How can I do this?

The question is in 5.Module 4.Task.


SELECT
SalesProducts.Product AS Product,
SUM(SalesProducts.Quantity) AS Quantity,
AVG(SalesProducts.UnitPrice) AS UnitPrice,
SUM(SalesProducts.LineTotal) AS LineTotal
FR OM
Document.Sales.Products AS SalesProducts
WHERE
SalesProducts.LineTotal > ¶meter

GROUP BY
SalesProducts.Product

ORDER BY
LineTotal ASC

Hi Mesut,

yes, of course, you can use the CAST keyword for this. In your case it will look like this:

CAST(SalesProducts.UnitPrice AS NUMBER(15, 2))

or

CAST(SalesProducts.UnitPrice AS NUMBER(15, 0))

I have tried but it gives an error message.

Please show me the full text of your query.

A, I see,

you should write like this:

SELECT
SalesProducts.Product AS Product,
SUM(SalesProducts.Quantity) AS Quantity,

AVG(CAST(SalesProducts.UnitPrice AS NUMBER(15, 2))) AS UnitPrice,

SUM(SalesProducts.LineTotal) AS LineTotal
FR OM
Document.Sales.Products AS SalesProducts
WHERE
SalesProducts.LineTotal > ¶meter

GROUP BY
SalesProducts.Product

ORDER BY
LineTotal ASC

Yes it works but there is a little thing;

TrueUnitPrice and MyUnitPrice below;

Mesut,

please upload dt-file here and also put the full text of your query.

SEL ECT
SalesProducts.Product AS Product,
SUM(SalesProducts.Quantity) AS Quantity,
AVG(CAST(SalesProducts.UnitPrice as number(15,2))) AS UnitPrice,
SUM(SalesProducts.LineTotal) AS LineTotal
FR OM
Document.Sales.Products AS SalesProducts
WHERE
SalesProducts.LineTotal > ¶meter

GROUP BY
SalesProducts.Product

ORDER BY
LineTotal ASC


CommonModule->TASK4

Mesut,

in your case, the request text will be as follows:

SELECT
SalesProducts.Product AS Product,
SUM(SalesProducts.Quantity) AS Quantity,
CAST(AVG(SalesProducts.UnitPrice) as number(12,2)) AS UnitPrice,
SUM(SalesProducts.LineTotal) AS LineTotal
FROM
Document.Sales.Products AS SalesProducts

GROUP BY
SalesProducts.Product

ORDER BY
LineTotal ASC

Yes it works,thank you for ypur help.

What is the reason (12,2)?

Okey thank you!