CODEWITHSUNDEEP
sqlpostgresql
Eugen Konkov
Eugen Konkov

Reputation: 25133

Can I write SQL query without subquery duplicate?

I have query:

SELECT
  acc.usage AND EXISTS (
      SELECT * FROM order_bt prev_order
      WHERE sys_period @> sys_time() AND
        prev_order.id = o.id  AND prev_order.app_period && acc.usage_range
  ) as usage,
  acc.usage_range,
  acc.invoice,
  acc.invoice_range,
  o.*
FROM "order" o
left join period prd on prd.id = period_id
LEFT JOIN accounting_ready() acc ON TRUE
WHERE
  ( acc.usage AND EXISTS (
      SELECT * FROM order_bt prev_order
      WHERE sys_period @> sys_time() AND
        prev_order.id = o.id  AND prev_order.app_period && acc.usage_range
    ) OR acc.invoice )

In this query next part is copy/paste:

  acc.usage AND EXISTS (
      SELECT * FROM order_bt prev_order
      WHERE sys_period @> sys_time() AND
        prev_order.id = o.id  AND prev_order.app_period && acc.usage_range
  ) as usage,

Is there a way to write query without this copy/paste?

Upvotes: 0

Views: 28

Answers (1)

user330315
user330315

Reputation:

You could put your query into a derived table:

SELECT *
FROM (
  SELECT acc.usage AND EXISTS (
              SELECT * FROM order_bt prev_order
              WHERE sys_period @> sys_time() AND
                prev_order.id = o.id  AND prev_order.app_period && acc.usage_range
          ) as usage,
          acc.usage_range,
          acc.invoice,
          acc.invoice_range,
          o.*
  FROM "order" o
  left join period prd on prd.id = period_id
  LEFT JOIN accounting_ready() acc ON TRUE
) t 
WHERE usage OR acc.invoice

Upvotes: 1

Related Questions