Creating a custom query

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 created a custom query,but it did't works.Can you help me

SEL ECT

CatalogCashiers.LastName,
CatalogCashiers.FirstName,

CASE

WHEN CatalogCashiers.Gender = "Female"
THEN "Mrs. ," + SUBSTRING(CatalogCashiers.LastName, 1, 1) + "," +
                       SUBSTRING(CatalogCashiers.FirstName, 1, 1)
WHEN CatalogCashiers.Gender = "Male"
       THEN
              "Mr. ," + SUBSTRING(CatalogCashiers.LastName, 1, 1) + "," +
                       SUBSTRING(CatalogCashiers.FirstName, 1, 1)
       ELSE
      SUBSTRING(CatalogCashiers.LastName, 1, 1) + "," +
                      SUBSTRING(CatalogCashiers.FirstName, 1, 1)
END

FR OM
Catalog.Cashiers AS CatalogCashiers

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

Hello Mesut,

i copied your code and it works absolutely correctly - see screenshots 1 and 2 pls.

By the way, it's not quite the right decision to store gender as a String. It's better to use an enumeration for this. After that the application structure will look like in scr. 3, and the query:

Code
SELECT
   CatalogCashiers.LastName AS LastName,
   CatalogCashiers.FirstName AS FirstName,
   CASE
      WHEN CatalogCashiers.Gender = VALUE(Enum.Gender.Female)
         THEN "Mrs. ," + SUBSTRING(CatalogCashiers.LastName, 1, 1) + "," + SUBSTRING(CatalogCashiers.FirstName, 1, 1)
      WHEN CatalogCashiers.Gender = VALUE(Enum.Gender.Male)
         THEN "Mr. ," + SUBSTRING(CatalogCashiers.LastName, 1, 1) + "," + SUBSTRING(CatalogCashiers.FirstName, 1, 1)
      ELSE SUBSTRING(CatalogCashiers.LastName, 1, 1) + "," + SUBSTRING(CatalogCashiers.FirstName, 1, 1)
   END AS Field1
FROM
   Catalog.Cashiers AS CatalogCashiers



I recommend doing this, it will allow you to avoid various mistakes in the future.

Aleksandr

Download 1.png (9.04 KB)
Download 2.png (11.6 KB)
Download 3.png (31.32 KB)
 
#3
People who like this:0Yes/0No
Interested
Rating: 10
Joined: Sep 30, 2020
Company: Nortek Bilişim

it works Thank you Aleksandr Biryukov!

Edited: Mesut Kahraman - Oct 02, 2020 02:43 PM
 
#4
People who like this:0Yes/0No
Interested
Rating: 10
Joined: Sep 30, 2020
Company: Nortek Bilişim

I have also a simple question.
User enter a number and amountpaid that is greater than the number is showed.
My code below;

SEL ECT
Sales.Number AS Number,
Sales.Date AS Date,
Sales.AmountPaid AS AmountPaid,
Sales.PaymentMethod AS PaymentMethod
FR OM
Document.Sales AS Sales

WHERE AmountPaid > What I need to write here
ORDER BY AmountPaid DESC

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

Mesut,

unfortunately, I didn't quite understand your question :-)

Do you want to select payments above a certain amount?

In this case, the query code will be like this:

Code
WHERE AmountPaid > &Number


where "&Number" is the parameter to be passed to the query.

 
#6
People who like this:0Yes/0No
Active user
1C:Professional
Rating: 6
Joined: May 8, 2013
Company: 1C Company

Mesut,

You need to pass the minimum amount as a parameter as is shown in this episode of the course. Please, watch it once again.

Konstantin

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

Thank you , it works

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

Thank you , it works

 
Subscribe
Users browsing this topic (guests: 1, registered: 0, hidden: 0)
Be the first to know tips & tricks 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.