Lazarus

Programming => Databases => Topic started by: mobilevil on May 30, 2018, 11:37:42 am

Title: firebird SQL, is it possible to make a calculated field out of subselct fields?
Post by: mobilevil on May 30, 2018, 11:37:42 am
this query works,

select distinct a.customer_id, c.customer_name,
(select sum(gnd_total) from orders b where b.customer_id=a.customer_id and b.settlement_ID is null) as out_standing,
(select sum(gnd_total) from orders b where b.customer_id=a.customer_id and b.settlement_ID is not null) as paid,
(select sum(gnd_total) from orders b where b.customer_id=a.customer_id) as total
from orders a
left join customers c on a.customer_id = C.customer_id

but is it possible to use "(out_standing+paid) as total" to replace the 3rd subselect? fb complains about column unknown:paid
Title: Re: firebird SQL, is it possible to make a calculated field out of subselct fields?
Post by: Zoran on May 30, 2018, 12:05:02 pm
this query works,

select distinct a.customer_id, c.customer_name,
(select sum(gnd_total) from orders b where b.customer_id=a.customer_id and b.settlement_ID is null) as out_standing,
(select sum(gnd_total) from orders b where b.customer_id=a.customer_id and b.settlement_ID is not null) as paid,
(select sum(gnd_total) from orders b where b.customer_id=a.customer_id) as total
from orders a
left join customers c on a.customer_id = C.customer_id

but is it possible to use "(out_standing+paid) as total" to replace the 3rd subselect? fb complains about column unknown:paid

This should work as well (written from the head, haven't tested, as I don't have the DB with these tables/columns):
Code: SQL  [Select]
  1. SELECT a.customer_id, c.customer_name,
  2.   SUM(CASE WHEN a.settlement_id IS NULL THEN 0 ELSE a.gnd_total END) AS out_standing,
  3.   SUM(CASE WHEN a.settlement_id IS NULL THEN a.gnd_total ELSE 0 END) AS paid,
  4.   SUM(a.gnd_total) AS total
  5. FROM orders a
  6. LEFT JOIN customers c ON c.customer_id = a.customer_id
  7. GROUP BY a.customer_id, c.customer_name
  8.  
Title: Re: firebird SQL, is it possible to make a calculated field out of subselct fields?
Post by: Thaddy on May 30, 2018, 12:09:10 pm
Yes, this is a distinctly distinct programming error.
Zoran gave a query that works.
https://www.w3schools.com/Sql/sql_distinct.asp

Distinct is not easy.
Title: Re: firebird SQL, is it possible to make a calculated field out of subselct fields?
Post by: mobilevil on May 30, 2018, 12:18:55 pm

This should work as well (written from the head, haven't tested, as I don't have the DB with these tables/columns):
Code: SQL  [Select]
  1. SELECT a.customer_id, c.customer_name,
  2.   SUM(CASE WHEN a.settlement_id IS NULL THEN 0 ELSE a.gnd_total END) AS out_standing,
  3.   SUM(CASE WHEN a.settlement_id IS NULL THEN a.gnd_total ELSE 0 END) AS paid,
  4.   SUM(a.gnd_total) AS total
  5. FROM orders a
  6. LEFT JOIN customers c ON c.customer_id = a.customer_id
  7. GROUP BY a.customer_id, c.customer_name
  8.  

in your example you used SUM() to do the calculation but it didn't answer my question about calculating based on the previous sub select result out_standing and paid...

but in your example the case when field is null then 0 is useful for me.
I am guarding null from data entry but yes there can be null in these sub query.
Title: Re: firebird SQL, is it possible to make a calculated field out of subselct fields?
Post by: Zoran on May 30, 2018, 12:31:15 pm
in your example you used SUM() to do the calculation but it didn't answer my question about calculating based on the previous sub select result out_standing and paid...

but in your example the case when field is null then 0 is useful for me.
I am guarding null from data entry but yes there can be null in these sub query.

Now I am not sure that you understand this.
Note that if you have nulls in gnd_total field, my query should return null for the whole sum, just like yours, the query I posted should return exactly the same resultset as the query from your post. Try both and compare the results.
If you want to replace nulls with zeros inside the sums, you should replace a.gnd_total with coalesce(a.gnd_total, 0) in all the places. The same in your original query, as well as in the one I posted.
For example, the first sum will then become:
Code: SQL  [Select]
  1. SUM(CASE WHEN a.settlement_id IS NULL THEN 0 ELSE COALESCE(a.gnd_total, 0) END) AS out_standing,
Title: Re: firebird SQL, is it possible to make a calculated field out of subselct fields?
Post by: rvk on May 30, 2018, 12:34:36 pm
... but it didn't answer my question about calculating based on the previous sub select result out_standing and paid...
You cannot refer to a column alias defined in the same SELECT list. You can only use it in the WHERE and GROUP BY clause. But not in the SELECT list itself.

You can do a subselect in FROM with out_standing and paid and then use it in the main SELECT but that's just a workaround.
Title: Re: firebird SQL, is it possible to make a calculated field out of subselct fields?
Post by: mobilevil on May 30, 2018, 12:38:58 pm
You cannot refer to a column alias defined in the same SELECT list. You can only use it in the WHERE and GROUP BY clause. But not in the SELECT list itself.

You can do a subselect in FROM with out_standing and paid and then use it in the main SELECT but that's just a workaround.

thanks! but I thought it isn't that complex and it may save some processing by reusing the previous result(calculated field).
I think i will do a client side calculated fields instead, or do it in reporting.
Title: Re: firebird SQL, is it possible to make a calculated field out of subselct fields?
Post by: Zoran on May 30, 2018, 12:42:48 pm
in your example you used SUM() to do the calculation but it didn't answer my question about calculating based on the previous sub select result out_standing and paid...

The answer is no. Not the way you expected.
You can however use subqeries or CTE (https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html#fblangref25-dml-select-cte):

Code: SQL  [Select]
  1. WITH MySubqery AS (
  2.   SELECT a.customer_id, c.customer_name,
  3.     SUM(CASE WHEN a.settlement_id IS NULL THEN 0 ELSE a.gnd_total END) AS out_standing,
  4.     SUM(CASE WHEN a.settlement_id IS NULL THEN a.gnd_total ELSE 0 END) AS paid
  5.   FROM orders a
  6.   LEFT JOIN customers c ON c.customer_id = a.customer_id
  7.   GROUP BY a.customer_id, c.customer_name
  8. )
  9. SELECT customer_id, customer_name, out_standing, paid, out_standing + paid AS total
  10. FROM MySubquery
  11.  

You can replace the inner select with the one from your original post, only without the third sum.
Title: Re: firebird SQL, is it possible to make a calculated field out of subselct fields?
Post by: mobilevil on May 30, 2018, 12:46:22 pm
in your example you used SUM() to do the calculation but it didn't answer my question about calculating based on the previous sub select result out_standing and paid...

The answer is no. Not the way you expected.
You can however use subqeries or CTE (https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html#fblangref25-dml-select-cte):

Code: SQL  [Select]
  1. WITH MySubqery AS (
  2.   SELECT a.customer_id, c.customer_name,
  3.     SUM(CASE WHEN a.settlement_id IS NULL THEN 0 ELSE a.gnd_total END) AS out_standing,
  4.     SUM(CASE WHEN a.settlement_id IS NULL THEN a.gnd_total ELSE 0 END) AS paid
  5.   FROM orders a
  6.   LEFT JOIN customers c ON c.customer_id = a.customer_id
  7.   GROUP BY a.customer_id, c.customer_name
  8. )
  9. SELECT customer_id, customer_name, out_standing, paid, out_standing + paid AS total
  10. FROM MySubquery
  11.  

You can replace the inner select with the one from your original post, only without the third sum.

oh it looks good. Thanks! I have never been a database guy but I am doing this for a friend so...
Title: Re: firebird SQL, is it possible to make a calculated field out of subselct fields?
Post by: Zoran on May 30, 2018, 12:55:32 pm
oh it looks good. Thanks! I have never been a database guy but I am doing this for a friend so...

I still strongly suggest you to avoid unnecessary subqueries which read the same table as you did in the original query you posted. And, if gnd_total is not declared with "not null" constraint, do use coalesce as I told you in one of the posts above.
Title: Re: firebird SQL, is it possible to make a calculated field out of subselct fields?
Post by: mobilevil on May 31, 2018, 02:52:55 am
oh it looks good. Thanks! I have never been a database guy but I am doing this for a friend so...

I still strongly suggest you to avoid unnecessary subqueries which read the same table as you did in the original query you posted. And, if gnd_total is not declared with "not null" constraint, do use coalesce as I told you in one of the posts above.

yes I do enforce not null for those fields. Thanks for your suggestion.