* * *

Author Topic: firebird SQL, is it possible to make a calculated field out of subselct fields?  (Read 1064 times)

mobilevil

  • Jr. Member
  • **
  • Posts: 54
    • http://www.kachun.com
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

Zoran

  • Hero Member
  • *****
  • Posts: 1244
    • http://wiki.lazarus.freepascal.org/User:Zoran
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.  

Thaddy

  • Hero Member
  • *****
  • Posts: 6527
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.
« Last Edit: May 30, 2018, 12:15:04 pm by Thaddy »
Ada's daddy wrote this:"Fools are my theme, let satire be my song."

mobilevil

  • Jr. Member
  • **
  • Posts: 54
    • http://www.kachun.com

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.

Zoran

  • Hero Member
  • *****
  • Posts: 1244
    • http://wiki.lazarus.freepascal.org/User:Zoran
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,
« Last Edit: May 30, 2018, 12:48:40 pm by Zoran »

rvk

  • Hero Member
  • *****
  • Posts: 3449
... 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.

mobilevil

  • Jr. Member
  • **
  • Posts: 54
    • http://www.kachun.com
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.
« Last Edit: May 30, 2018, 12:45:36 pm by mobilevil »

Zoran

  • Hero Member
  • *****
  • Posts: 1244
    • http://wiki.lazarus.freepascal.org/User:Zoran
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:

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.

mobilevil

  • Jr. Member
  • **
  • Posts: 54
    • http://www.kachun.com
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:

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...

Zoran

  • Hero Member
  • *****
  • Posts: 1244
    • http://wiki.lazarus.freepascal.org/User:Zoran
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.

mobilevil

  • Jr. Member
  • **
  • Posts: 54
    • http://www.kachun.com
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.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus