Ошибка:
org.postgresql.util.PSQLException: ERROR: column t.nomenclaturecode does not exist Hint: Perhaps you meant to reference the column "t.nomenclatureCode" or the column "t.nomenclatureCode". Position: 3278 at



SQL:

<![CDATA[WITH dates AS (
  SELECT
    $P{start_date_required}::TIMESTAMP - INTERVAL '3 hours' AS "begin", -- convert to MSK TZ
    $P{end_date_required}::TIMESTAMP + INTERVAL '21 hours' AS "end"
),

-- точки продажи, по которым берутся данные
endpoints AS (
  SELECT e.id, e.client
  FROM "Endpoints" e
  JOIN "Clients" p ON p.id = e.client AND p.deleted = FALSE AND p.status = 'active'
  WHERE e.deleted = FALSE
    AND e.status = 'active'
    AND (p.data ->> 'participatesInCampaigns') = 'true'
	AND coalesce(coalesce(e.data, '{}'::JSONB) ->> 'excludedFromCampaigns', 'false') <> 'true'
	AND ($P{campaigns_endpoints_manual} IS NULL OR $X{IN, lower(e.id::VARCHAR), campaigns_endpoints_manual})
),

-- Точки продажи, для которых данные берутся из заказов
-- (не выгружают обороты или есть какие-то проблемы).
manual_order_endpoints AS (
  SELECT e.id
  FROM endpoints e
  WHERE NOT EXISTS(
    SELECT 1
    FROM dates
    JOIN "NomenclatureTurnovers" nt ON nt.endpoint = e.id
	  AND nt."date" >= dates.begin AND nt."date" < dates.end
  )
	  AND ($P{campaigns_endpoints_manual} IS NULL OR $X{IN, lower(e.id::VARCHAR), campaigns_endpoints_manual})
),

-- Точки продажи, для которых данные берутся из оборотов
-- (все точки минус точки по заказам)
turnover_endpoints AS (
  SELECT
    endpoints.*
  FROM endpoints
  WHERE NOT EXISTS(SELECT 1 FROM manual_order_endpoints WHERE manual_order_endpoints.id = endpoints.id)
),

-- Препараты производителя
goods AS (
	SELECT
	turn."nomenclatureCode" AS "nomenclatureCode",
    	mg."EAN",
		max(mg.title) AS title,
		avg(nullif(p.price, 1))::NUMERIC(20, 2) AS price,
		(
			SELECT avg(pp.price)::NUMERIC(20, 2)
			FROM "ResellerGoods" rg
		    JOIN "Clients" c ON
				rg.reseller = c.id AND
				c."countryCode" = 'RU' AND
				c.status = 'active' AND
				c.type = 'reseller'
		    JOIN "Lots" ll ON ll.good = rg.id AND ll.deleted = FALSE
		    JOIN "Prices" pp ON pp.lot = ll.id AND pp.deleted = FALSE
		    WHERE rg."EAN" = mg."EAN"
		) AS "avgPrice"
    FROM "ResellerGoods" mg
    JOIN "Turnovers" turn on turn."EAN"=mg."EAN"
	JOIN "Lots" l ON l.good = mg.id AND l.deleted = FALSE
    JOIN "Prices" p ON p.lot = l.id AND p.deleted = FALSE
    WHERE mg.reseller = $P{manufacturer_single}::UUID
      AND mg."EAN" not like 'PH.%'
	  AND ($P{manufacturers_goods} IS NULL OR $X{IN, mg."EAN", manufacturers_goods})
  	GROUP BY mg."EAN", turn."nomenclatureCode"
),

turnovers_goods AS (
	SELECT
		nt.*,
		goods."nomenclatureCode",
		goods."EAN" AS goods_ean,
		goods.title AS goods_title,
		goods.price AS goods_price,
		goods."avgPrice" AS goods_avg_price
	FROM turnover_endpoints e
	JOIN goods ON TRUE
	JOIN dates ON TRUE
	JOIN "NomenclatureTurnovers" nt ON nt.endpoint = e.id
	  AND nt."EAN" = goods."EAN"
	  AND nt."date" >= dates.begin AND nt."date" < dates.END
	UNION ALL
	SELECT
		nt.*,
		goods."nomenclatureCode",
		goods."EAN" AS goods_ean,
		goods.title AS goods_title,
		goods.price AS goods_price,
		goods."avgPrice" AS goods_avg_price
	FROM turnover_endpoints e
	JOIN goods ON TRUE
	JOIN dates ON TRUE
	JOIN "Nomenclatures" n ON n.client = e.client AND n."clientEAN" = goods."EAN"
	JOIN "NomenclatureTurnovers" nt ON nt.endpoint = e.id
	  AND nt."nomenclatureCode" = n.nomenclature
	  AND nt."EAN" IS NULL
	  AND nt."date" >= dates.begin AND nt."date" < dates.END
),

turnovers AS (
  SELECT
    endpoint,
    "date",
    reseller,
    "resellerCode",
    "EAN",
    "title",
    "incomeQuantity",
    income,
	"nomenclatureCode"
  FROM (
    SELECT
		t.nomenclatureCode,
		t.endpoint,
		t."date",
		r.title AS reseller,
		ntr."resellerCode",
		t.goods_ean AS "EAN",
	  	t.goods_title AS title,
		ntr."quantityDebit" AS "incomeQuantity",
		ntr."quantityDebit" * coalesce(t.goods_price, ntr."priceDebit", t.goods_avg_price) AS income,
		row_number() OVER (
			PARTITION BY
				t.endpoint,
				t."date",
				t."nomenclatureCode",
				t."EAN",
				t."quantityEnd",
				ntr."quantityDebit",
				ntr."resellerCode"
		)
	FROM turnovers_goods t
    JOIN "NomenclatureTurnoversResellers" ntr ON ntr."nomenclatureTurnover" = t.id
    JOIN "NomenclatureTurnoversSettings" nts ON nts.endpoint = t.endpoint AND nts."resellerCode" = ntr."resellerCode"
    JOIN "Clients" r ON r.id = nts.reseller
		WHERE (
			NOT EXISTS(
				SELECT 1
				FROM "ManufacturerResellers" mr
				WHERE mr.manufacturer = $P{manufacturer_single}::UUID
			) OR EXISTS(
				SELECT 1
				FROM "ManufacturerResellers" mr
				WHERE mr.manufacturer = $P{manufacturer_single}::UUID AND mr.reseller = nts.reseller
			)
		)
  ) turnovers_with_duplicates
  WHERE row_number = 1
),

-- Данные по заказам
order_endpoints AS (
  SELECT
    manual_order_endpoints.id AS endpoint,
    r.id AS reseller
  FROM manual_order_endpoints
  JOIN "Contracts" c ON c."buyerEndpoint" = manual_order_endpoints.id AND (c.status = 'approved' OR c.status = 'paused') AND c."resellerType" = 'reseller'
  JOIN "Clients" r ON r.id = c.seller
  GROUP BY manual_order_endpoints.id, r.id
),

orders AS (
  SELECT
    o.id,
    o.endpoint
  FROM dates
  JOIN "Orders" o ON o."dateSent" >= dates."begin" AND o."dateSent" < dates."end" AND o.status = 'sent'
  JOIN order_endpoints ON o.endpoint = order_endpoints.endpoint
  GROUP BY o.id, o.endpoint
),

order_turnovers as (
  SELECT
    goods.nomenclatureCode,
    orders.endpoint,
    oi."date",
    r.title AS reseller,
    goods."EAN",
    goods.title,
    oi.quantity AS "incomeQuantity",
    oi.quantity * coalesce(goods.price, oi.price, goods."avgPrice") AS income,
    t.nomenclatureCode
  FROM "OrderItems" oi
  JOIN "OrderResellers" ors ON oi."orderReseller" = ors.id
  JOIN "Clients" r ON r.id = ors.reseller
  JOIN orders ON orders.id = ors."order"
  JOIN goods ON goods."EAN" = coalesce(oi.offer ->> 'EAN', oi.offer ->> 'ean')
  WHERE oi.type = 'order'
	  AND (
			NOT EXISTS(
				SELECT 1
				FROM "ManufacturerResellers" mr
				WHERE mr.manufacturer = $P{manufacturer_single}::UUID
			) OR EXISTS(
					SELECT 1
					FROM "ManufacturerResellers" mr
					WHERE mr.manufacturer = $P{manufacturer_single}::UUID AND mr.reseller = r.id
			)
		)
),

union_turnovers as (
  SELECT
    turnovers.nomenclatureCode,
    turnovers.endpoint,
    turnovers."date",
    turnovers.reseller,
    turnovers."resellerCode",
    turnovers."title",
    turnovers."EAN",
    turnovers."incomeQuantity",
    turnovers.income
  FROM turnovers
  UNION ALL
  SELECT
    order_turnovers.nomenclatureCode,
    order_turnovers.endpoint,
    order_turnovers."date",
    order_turnovers.reseller,
    'По нашим заказам' AS "resellerCode",
    order_turnovers."title",
    order_turnovers."EAN",
    order_turnovers."incomeQuantity",
    order_turnovers.income
  FROM order_turnovers
)

SELECT
  union_turnovers.nomenclatureCode,
  m.title AS manufacturer,
  union_turnovers."EAN",
  union_turnovers.title,
  p.title AS client,
  e."legalTitle" || ', ' || e.incorporation AS endpoint,
  e.inn,
  e."physicalAddress" AS address,
  coalesce(sum(union_turnovers."incomeQuantity"), 0) AS "incomeQuantity",
  coalesce(sum(union_turnovers.income)::NUMERIC(20, 2), 0) AS income,
  union_turnovers.reseller,
  union_turnovers."resellerCode",
  to_char(union_turnovers."date" + INTERVAL '3 hours', 'MM.YYYY') AS "month"
FROM endpoints
LEFT JOIN union_turnovers ON union_turnovers.endpoint = endpoints.id
JOIN "Endpoints" e ON e.id = endpoints.id
JOIN "Clients" p ON p.id = e.client
JOIN "Clients" m ON m.id = $P{manufacturer_single}::UUID
$P!{show_empty_endpoints_sql}
GROUP BY
  m.id,
  p.id,
  e.id,
  union_turnovers."EAN",
  union_turnovers.title,
  union_turnovers.reseller,
  union_turnovers."resellerCode",
  union_turnovers.nomenclatureCode,
  to_char(union_turnovers."date" + INTERVAL '3 hours', 'MM.YYYY')
ORDER BY title, client, endpoint, address, "month";]]>