"Select" problems



Common questions about 1C:Query language, Query builder tool and Data composition schema

#1
People who like this:0Yes/0No
Active user
1C:Professional
Rating: 3
Joined: Dec 4, 2015
Company: Smart ID Dynamics

Hello!

I got a problem with a query. I will give you an example of what my query should do : I have 2 stores ( Store 1 and Store 2) and lots of prices. If i have 5 prices on article "A" in Store 1 and 6 prices on same article but in Store 2, I need to get the last price for this article in both stores.

Here is my query .It runs in sql, but not in 1C.
Please help me.

 
#2
People who like this:1Yes/0No
Active user
Rating: 4
Joined: Apr 18, 2012
Company:

Hi, Sebastian Dan.

Try this query

Code
SELECT
   Prices.StoreNo,
   Prices.ArticleCode,
   MAX(Prices.LastDateModified) AS LastDateModified
INTO LastPrices
FROM
   Catalog.Prices AS Prices

GROUP BY
   Prices.StoreNo,
   Prices.ArticleCode
;

////////////////////////////////////////////////////////////////////////////////
SELECT
   Prices.StoreNo,
   Prices.ArticleCode,
   Prices.ExchangeId,
   Prices.FinalPrice,
   Prices.LastDateModified
FROM
   Catalog.Prices AS Prices
      INNER JOIN LastPrices AS LastPrices
      ON Prices.StoreNo = LastPrices.StoreNo
         AND Prices.ArticleCode = LastPrices.ArticleCode
         AND Prices.LastDateModified = LastPrices.LastDateModified


Also I recommend use information register instead catalog. Virtual table "SliceLast" will simplify your query.

Code
SELECT
   PricesSliceLast.Period AS LastDateModified,
   PricesSliceLast.StoreNo,
   PricesSliceLast.ArticleCode,
   PricesSliceLast.ExchangeId,
   PricesSliceLast.FinalPrice
FROM
   InformationRegister.Prices.SliceLast AS PricesSliceLast

 
#3
People who like this:0Yes/0No
Active user
1C:Professional
Rating: 3
Joined: Dec 4, 2015
Company: Smart ID Dynamics

Thank you a lot, Alexei Khatin ! This is what I needed .

 
Subscribe