Am 16.02.2017 um 09:24 schrieb Sergi Almacellas Abellana:
>
> Well, that will be also possible if you use a UNION query to join data
> from productions and from shipments. Indeed all the data is in the stock
> move table but it have to be joined in a different way (one using the
> shipment field and the other using the production fields). Something like:
>
> select output.id, input.lot as from_lot, output.lot as to_lot
> FROM stock_move input
> INNER JOIN stock_move output ON input.shipment = output.shipment and
> input.product = output.product and input.to_location =
> output.to_location and input.lot <> output.lot
> UNION
> select output.id, input.lot as from_lot, output.lot as to_lot
> FROM stock_move input
> INNER JOIN stock_move output ON input.production_input =
> output.production_output and input.product = output.product and
> input.to_location = output.to_location and input.lot <> output.lot
>
> Does this work for you?
>
Yes, thanks. I first had to build a field for the supplier lot into
stock.move and had to populate it with test data. Now my query is even
simpler than your sugestion:
SELECT "a"."create_uid" AS "create_uid", "a"."create_date" AS
"create_date", "a"."write_uid" AS "write_uid", "a"."write_date"
AS "write_date", "a"."id" AS "id", "a"."uom" AS "uom",
"a"."quantity" AS "quantity", "a"."lot" AS "from_lot",
"b"."lot" AS "to_lot"
FROM "stock_move" AS "a" INNER JOIN "stock_move" AS "b"
ON ("a"."production_input" = "b"."production_output")
WHERE (("a"."lot" > %s) AND ("b"."lot" > %s))
UNION
SELECT "a"."create_uid" AS "create_uid", "a"."create_date" AS
"create_date", "a"."write_uid" AS "write_uid", "a"."write_date"
AS "write_date", "a"."id" AS "id", "a"."uom" AS "uom",
"a"."quantity" AS "quantity", "a"."supplier_lot" AS "from_lot",
"a"."lot" AS "to_lot"
FROM "stock_move" AS "a"
WHERE (("a"."lot" > %s) AND ("a"."supplier_lot" > %s))
but i have to take care, that there is not more than one output with a
lot in every production or i will get double ids again.
Do you know how to generate new ids in a query?
Thanks...
--
You received this message because you are subscribed to the Google Groups
"tryton" group.
To view this discussion on the web visit
https://groups.google.com/d/msgid/tryton/f491a52f-19a2-322e-11ce-54a68a1d8d92%40gmx.de.
