Custom Query - Round operation

For students who study 1C Junior course.

#1
People who like this:0Yes/0No
Interested
Rating: 10
Joined: Sep 30, 2020
Company: Nortek Bilişim

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

Edited: Mesut Kahraman - Oct 08, 2020 09:34 AM
 
#2
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

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

 
#3
People who like this:0Yes/0No
Interested
Rating: 10
Joined: Sep 30, 2020
Company: Nortek Bilişim

I have tried but it gives an error message.

 
#4
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

Please show me the full text of your query.

 
#5
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

A, I see,

you should write like this:

Code
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

 
#6
People who like this:0Yes/0No
Interested
Rating: 10
Joined: Sep 30, 2020
Company: Nortek Bilişim

Yes it works but there is a little thing;

TrueUnitPrice and MyUnitPrice below;

 
#7
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

Mesut,

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

 
#8
People who like this:0Yes/0No
Interested
Rating: 10
Joined: Sep 30, 2020
Company: Nortek Bilişim

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

Download DTFILE.dt (511.04 KB)
Edited: Mesut Kahraman - Oct 08, 2020 03:33 PM
 
#9
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

Mesut,

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

Code
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




that is, we first get the average, and then round it up.

 
#10
People who like this:0Yes/0No
Interested
Rating: 10
Joined: Sep 30, 2020
Company: Nortek Bilişim

Yes it works,thank you for ypur help.

What is the reason (12,2)?

 
#11
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

Quote
Mesut Kahraman wrote:
What is the reason (12,2)?

This is my example, you can do it like this: NUMBER(15,2)

The first digit is the length of the integer part of the number after rounding, the second digit is the length of the fractional part.

 
#12
People who like this:0Yes/0No
Interested
Rating: 10
Joined: Sep 30, 2020
Company: Nortek Bilişim

Okey thank you!

 
Subscribe
Be the first to know tips & trick on business application development!

A confirmation e-mail has been sent to the e-mail address you provided .

Click the link in the e-mail to confirm and activate the subscription.